DevAdmin

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,AD...

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 ...

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

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. =================================...

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(...

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 ---------- ----...

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...