Friday, August 31, 2012

Executing dynamic code in PL/SQL

This is a sample of how to execute a dynamic code to create a partition:

  PROCEDURE cre_part_tab
    (ptable IN VARCHAR2,
     ptag   IN VARCHAR2,
     plim   IN VARCHAR2)

   *  Procedure to facilitate creating of new Partitions
   *  Author  Strovek
   *  Date    Aug 15, 2001

    v_Cursor         INTEGER;
    v_creString      VARCHAR2(100);
    v_Cursor := DBMS_SQL.OPEN_CURSOR;
    v_creString := 'ALTER TABLE ' || ptable || ' ADD PARTITION '
      || ptable || '_' || ptag || ' VALUES LESS THAN (''' || plim || ''')';
    DBMS_SQL.PARSE(v_Cursor, v_creString,DBMS_SQL.V7);
      IF DBMS_SQL.IS_OPEN(v_Cursor) then
      End IF;
  END cre_part_stage;

Note that it is necessary to close the cursor should an error happen (to avoid leaving open cursors.  

pTable is the table name.
pTag is the tag for the partition.   Can be a running number.
pLim is the limit for the partition, that depends on how you partition your table, if by date then you need to supply a date.

Thursday, August 30, 2012

Using a procedure within a Oracle Trigger

This is a sample how you can declare a procedure in the Declare section of the PL/SQL block.   This will reduce the number of redundant code in your program.

In the sample below, I want to log changes to value of fields within an employee master database.   I will make the assumption that the tables are already there.

Create Or Replace Trigger trEmp_AttribUpd
Before Update Of title, srv_years, status, remarks on empmaster
For Each Row
 Create a history of the changes in the employee attributes
 Author    Strovek
 Date    Sep 26, 2005
    Procedure ins_attribhist(pParam IN Varchar2, pOldVal IN Varchar2, pNewVal IN Varchar2)
        insert into test_attrib_hist
            (emp_num, step_num, date_time, modifier_id, modifier_name, param_name,
            from_content, to_content, ver) values
            (:new.emp_num, :new.step_num, sysdate, :new.modifier_id, :new.modifier_name, pParam,
            pOldVal, pNewVal, :new.ver);

    End ins_attribhist;


    If NVL(:new.ver, -1) > 0 Then
        If :old.title <> :new.title Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('title', :old.title, :new.title);
        End If;
        If :old.srv_years <> :new.srv_years Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('srv_years', :old.srv_years, :new.srv_years);
        End If;
        If :old.remarks <> :new.remarks Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('REMARKS', :old.remarks, :new.remarks);
        End If;
        If :old.status <> :new.status Then
            :new.step_num := nvl(:old.step_num, 0) + 1;
            ins_attribhist('STATUS', :old.status, :new.status);
        End If;
    End If;


Wednesday, August 29, 2012

SQL command to find the location of the trace file in Oracle

select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid 
|| nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump 
cross join V$PARAMETER instance 
cross join V$PROCESS 
join V$SESSION on v$process.addr = V$SESSION.paddr 
where = 'user_dump_dest' 
and = 'instance_name' 
and V$SESSION.audsid=sys_context('

Wednesday, August 1, 2012

Postgres Memory Architecture vs Oracle Memory Architecture

The following is the memory architecture for Postgres 9.0.   The image is obtained from another blog which has the full explanation.

Oracle Memory Architecture is as follow.  The information is obtained from the Oracle document.