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)
  IS

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

    v_Cursor         INTEGER;
    v_creString      VARCHAR2(100);
  BEGIN
    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);
  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_SQL.IS_OPEN(v_Cursor) then
        DBMS_SQL.CLOSE_CURSOR(v_Cursor);
      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.

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
Declare
/*
 Create a history of the changes in the employee attributes
 Author    Strovek
 Date    Sep 26, 2005
 Revised
*/
    Procedure ins_attribhist(pParam IN Varchar2, pOldVal IN Varchar2, pNewVal IN Varchar2)
        IS
    Begin
        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;

Begin

    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;

End;

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 u_dump.name = 'user_dump_dest' 
and instance.name = 'instance_name' 
and V$SESSION.audsid=sys_context('

userenv','sessionid');

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.