Peoplecode Scroll, Rowset, Row, Fields

* OriginalValue vs PriorValue

Display table column followed by data

 declare

   sql_stmt varchar2(200);

   BU varchar2(5);

   col_name varchar2(30);

   col_val  varchar2(4000);

begin

 ------cursor

   for x in (select COLUMN_NAME from user_tab_columns where TABLE_NAME=upper('ps_voucher') order by COLUMN_ID ) loop

     cnt:=cnt+1;

     sql_stmt:='select '''||x.COLUMN_NAME||''', to_char('|| x.COLUMN_NAME|| ') from ps_voucher where business_unit=:1 and voucher_id = :2'; 

   --DBMS_OUTPUT.PUT_LINE('sql='||sql_stmt);

     EXECUTE IMMEDIATE sql_stmt into col_name, col_val  USING 'BU', 'VI'

     DBMS_OUTPUT.PUT_LINE(col_name||'='||col_val);

    end loop;

 exception

    WHEN OTHERS then

     declare

        errcd  NUMBER := SQLCODE;

        errmsg VARCHAR2(300) := SQLERRM;

     begin

        DBMS_OUTPUT.PUT_LINE('Error: rec#'|| cnt || ' rc='|| errcd ||','||errmsg);

     end;

 end;

/

AE Write Log

 Local File &Logfile;

&Logfile = GetFile(“Filename”,”W”,%FilePath_Absolute);

&Logfile.WriteLine(“Your messages”);

&Logfile.Close();

AE Trace Quick Look at Tables Modified

 

UPDATE

--- get all UPDATEs

awk '/^UPDATE /,/^\//' AE_AP_MATCH_999_0302143109.AET > xxx 

--- combine multiple lines into 1 line

sed -n 'H;:a /^\/$/{x;s/\n//g;p;n;h;b a}' xxx > yyy             

--- ignore temp tables

egrep -v '^UPDATE PS_.*_(T.*[0-9]|TAO) SET ' yyy                        

 

INSERT

grep ^INSERT AE_AP_MATCH_999_0302143109.AET| cut -d' ' -f3| egrep -v 'PS_.*_(T.*[0-9]|TAO)'

Open DB in RO

Read only oracle database tips

CREATE OR REPLACE TRIGGER
   manage_service
after startup on database
DECLARE
   role VARCHAR(30);
BEGIN
   SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
   IF role = 'PRIMARY' THEN
      DBMS_SERVICE.START_SERVICE('sales_rw');
   ELSE
      DBMS_SERVICE.START_SERVICE('sales_ro');
END IF;
END;

CONNECT BY Explained

 

 Source 

Result of each iteration is used as PRIOR in next iteration. 

Calculate Field Offset in a Record

Useful for generate load file:

select table_name, COLUMN_ID, COLUMN_NAME,DATA_TYPE, DATA_LENGTH, sum(DATA_LENGTH) 

over(PARTITION BY  table_name order by COLUMN_ID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Running Length"

from all_tab_columns where table_name ='PS_REQ_HDR'