Oracle DB programming

Managed logging in PL/SQL

Posted on

1. Call log function inside the procedure: procedure my_procedure is begin ….. IF $$debug_code THEN pkg_log.write_log (‘Log message’); END IF; …. end;     2. Switch logging on with alter package pkg_name compile body plsql_ccflags = ‘debug_code:true’ reuse settings;

Oracle DB programming

Natural vrs surrogate primary key

Posted on

Tom Kyte: “If you have a natural key, by all means – use it. The natural key should be immutable and sensible – they are sometimes “rare”, but if I had a document table, document version table – I would definitely use the document_id (might be a surrogate) as the primary key of one and […]

Oracle DB programming

NVL and COALESCE

Posted on

http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL. The differences are: NVL accepts only 2 arguments whereas COALESCE can take multiple arguments NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value. NVL does a […]

Oracle DB programming

Plan management

Posted on

Look for sql_id based on a fragment of sql text: select s.CHILD_NUMBER, s.PLAN_HASH_VALUE, s.IS_BIND_SENSITIVE, s.IS_BIND_AWARE, s.* from v$sql s where sql_text like ‘%O.ORDER_NO%’   Explain plan for sql_id: SELECT * FROM table ( DBMS_XPLAN.DISPLAY_CURSOR(sql_id => ‘gd92hchnmrbwc’, cursor_child_no => 0)); select * from TABLE(dbms_xplan.display_awr(‘5q5x7zcnt3c5d’)); Purge bad plan from SGA: select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID […]

Oracle DB programming

Multi table inserts

Posted on

Unconditional multi table insert: INSERT ALL INTO table1 (col1, col2, col3) values (source_table.col1, source_table.col2, source_table.col3) INTO table2 (col1, col2, col4) values (source_table.col1, source_table.col2, source_table.col4) INTO table3(col1, col5, col6) values (source_table.col1, source_table.col5, source_table.col6) SELECT * FROM source_table;   Conditional multi table insert: INSERT ALL WHEN (sal >= 3000) THEN INTO top_earners (emp_id, emp_name, emp_sal) VALUES (empno, […]

Oracle DB programming

PL/SQL standard named exceptions

Posted on

Oracle has a standard set of exceptions already named as follows: Oracle Exception Name Oracle Error Explanation DUP_VAL_ON_INDEX ORA-00001 You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. TIMEOUT_ON_RESOURCE ORA-00051 You were waiting for a resource and you timed out. TRANSACTION_BACKED_OUT […]

Oracle DB administering

Sqlplus formatting

Posted on

set echo off set verify off set heading off set feedback off set pagesize 0 set linesize 600 set trimpool on col aaa a 10 accept myparameter prompt ‘Bla bla enter parameter:’   spool myfiole.lst select ‘&myparameter’ aaa from dual   spool off col aaa clear

Oracle DB administering

ASH masters

Posted on

ASH Masters is the brain child of  John Beresniewicz,  Graham Wood and Kyle Hailey. We wanted to provide a web site where people could learn and exchange new ideas, experiences and tools to harness the power of ASH.   see http://ashmasters.com for information on Oracle Active Session History (ASH) also see Tanel Poder’s ash scripts at http://blog.tanelpoder.com/files/scripts/ash/ […]

Oracle DB programming

A trick with rownum

Posted on

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32812348052#2030256639041 When we have a query with subqueries like this: select t1.a from (select a from table1 where b between 1 and 10) t1, (select a from table2 where b between 1 and 10)  t2 where t1.a = t2.a and a is indexed, oracle with high probability will perform nested loops for the tables. But […]