Skip to main content

Posts

Showing posts from November, 2011

:: CSV files in Oracle ::

How to make a csv/flat file in oracle through UTL_FILE
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

CSV(comma separated values) are mostly useful in data transformation. I believe these files are mostly useful in DWH.

We can do the same through sqlplus features.

SQL> set heading off
SQL> set feedback off
SQL> spool /home/oracle/test/csv.txt
SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from emp;

7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY…

:: How Commit Works in PL SQL Functions/Triggers ::

COMMIT in Functions/Triggers.
+++++++++++++++++++

It Triggers/functions may be led to bulk associated transactions. This may lead to transaction atomicity failure.

Please find the below test case from Oracle.

Step 1:
---------

SQL> CREATE TABLE COMMIT_TRIG_TEST_TABLE1(T INT);

Table created.

SQL> CREATE TABLE COMMIT_TRIG_TEST_TABLE2(T INT,CHECK (T NOT IN(0)));

Table created.

Step 2:
---------
CREATE OR REPLACE FUNCTION INSERT_DATA(NUM IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO COMMIT_TRIG_TEST_TABLE1 VALUES('||NUM||')';
RETURN 'INSERT OK';
END;

Step 3:
---------
CREATE OR REPLACE TRIGGER COMMIT_TRIG
BEFORE INSERT ON COMMIT_TRIG_TEST_TABLE1
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO COMMIT_TRIG_TEST_TABLE2 VALUES(:NEW.T);
END;

Step 4:
--------
However, we know that if the function having dml operations, then we can't make that function as a part of SQL statement. So, please use the below method for inserting

the data.

SQL> VAR…

:: Fun With Oracle ::

Hi,

If you run this, you will find one funny frame with the given string ...

SELECT SUBSTR('&&STR',ROWNUM,1)
||DECODE(ROWNUM,1,SUBSTR('&&STR',2,LENGTH('&&STR')),LENGTH('&&STR'),' ',LPAD(' ',LENGTH('&STR')-2,' '))
||DECODE(ROWNUM,LENGTH('&&STR'),SUBSTR(REVERSE('&&STR'),2,LENGTH('&&STR')-2),' ')
||DECODE(ROWNUM,1,' ',SUBSTR('&&STR',-(ROWNUM),1))
FROM EMP,EMP
WHERE ROWNUM<=LENGTH('&&STR');

::ksdwrt in Oracle and workarround in PostgreSQL::

dbms_system.ksdwrt
================
In Oracle we do have ksdwrt for writing the customized alters into alert_log file as well trace files.
These customized log information is very usefull to DBA while monitoringthe servers.

Step 1:-
======
exec dbms_system.ksdwrt(2,CURRENT_DATE||'::NOTIFY:: '||' ****** Disk Space IS Very Less ... 90% Full
******');

PL/SQL procedure successfully completed.

Step 2:-
=======
SQL> select name,value from v$parameter where name='background_dump_dest';

NAME
--------------------------------------------------
VALUE
--------------------------------------------------
background_dump_dest
D:\ORACLE\PRODUCT\10.2.0\ADMIN\DINESH\BDUMP


Step 3:-
=======
From alertdinesh_log

Fri Nov 11 02:33:07 2011
11-NOV-11::NOTIFY:: ****** Disk Space IS Very Less ... 90% Full ******



In PostgreSQL, we don't have that feature. So,please find the below work arround for this in PostgreSQL.
=================================================================…

::Novalidate in Oracle, Not Valid in PostgreSQL::

Child entry in Oracle without Refference
=========================================

Yes, this is possible in Oracle as well PostgreSQL.

Adv:-
------
1. Doesn't Lock the table.So, no outage.
2. It doesn't validate the child entries with parent.So, no performance issues.
3. This option is very useful, when you are handling very big tables.

Step 1:
----------
CREATE TABLE PARENT(T INT PRIMARY KEY);

Step 2:
----------
Insert sample data

SQL> SELECT * FROM PARENT;

T
----------
1
2
3

Step 3:
----------
CREATE TABLE CHILD(T INT);

SQL> SELECT * FROM CHILD;

T
----------
1
2
3
4
5

Step 4:
----------
SQL> ALTER TABLE CHILD ADD FOREIGN KEY(T) REFERENCES PARENT(T) ENABLE NOVALIDATE;

Step 5:
----------
SQL> SELECT * FROM CHILD;

T
----------
1
2
3
4
5

SQL> INSERT INTO CHILD VALUES(10);
INSERT INTO CHILD VALUES(10)
*
ERROR at line 1:
OR…

::Pipelined in Oracle as well in PostgreSQL::

Pipelined Table Functions:- [ORACLE]
===========================

If you want to return multiple rows to the calling environment, then piplined table functions is prefred. It will increase the dbperformance as well.

Ex:-

Step 1:
-----------
CREATE TABLE EMP(EMPNO INT,ENAME VARCHAR2(10),SAL INT);

Step 2:
-----------
Insert sample data.

Step 3:
-----------
Create an object for the row type casting.
CREATE OR REPLACE TYPE emp_row
AS OBJECT
(
empno INT,
ename VARCHAR2(20),
SAL INT
);

Step 4:
-----------
Create a Return Type for the pipelined function.
CREATE OR REPLACE TYPE emp_table_type
AS TABLE OF emp_row;

Step 5:
-----------
CREATE OR REPLACE FUNCTION emp_pipe_function RETURN emp_table_type PIPELINED IS
BEGIN
FOR rec in (select * from emp)
LOOP
PIPE ROW (emp_row(rec.empno,rec.ename,rec.sal));
END LOOP;
RETURN;
END;


Step 6:
----------
SQL> select * from table(emp_pipe_function);

EMPNO ENAME SAL
---------- -------------------- ----------
1 Dinesh …

::Pg_dump With Load Balance::

Hi ,

Find the below steps to do pg_dump with load balance in PostgreSQL.

REQUIRED TABLES ON REMOTE:- [pg_dump is doing remotely]
==============================
CREATE TABLE LOAD_AVG(AVG REAL);
INSERT INTO LOAD_AVG VALUES(0.0);

REQUIRED SHELL SCRIPT FOR EVERY ONE MINUTE ON REMOTE:-
=======================================================
PGBIN="/opt/PostgreSQL/9.0/bin"
PORT=5432
USER="postgres"
PASSWORD="postgres"
DATABASE="postgres"
LOAD=$(cat /proc/loadavg|awk -F ' ' '{print $1}')
$PGBIN/psql -p $PORT -U $USER -d $DATABASE -c "UPDATE LOAD_AVG SET AVG='$LOAD'";


REQUIRED C PROGRAM ON LOCAL:-
================================
#include
#include
#include
#include
#include
#include
#include "/opt/PostgreSQL/9.0/include/libpq-fe.h"
int main(int argc,char *argv[])
{
FILE *fp,*remote_machine,*log;
char pid[10],cmd[30],con_string[110],host[40],port[4],dbname[20],user[20],password[20],pg_dump_cmd[1000];