Oracle PL/SQL – Create Temp Table (T0101) Complete Script

 

Business Need:

Have a basic local table in Oracle. Can be used for any logging or tracking of program execution by inserting data. The indexes and timesstamps are pre-populated by database triggers.

 

SQL Script (Execute one by one, not as a single script):

Create Table:

C1 – Index, populated by trigger

C2 – Main content, add from program

C3 – Timestamp, populated by trigger

C4 – SYSTIMESTAMP, required only for microsecond time (may be required in SQL Performance Optimization). Otherwise remove or do not use)

CREATE TABLE T0101 (
            C1 NUMBER,
            C2 VARCHAR2(4000),
            C3 DATE DEFAULT SYSDATE,
            C4 TIMESTAMP DEFAULT SYSTIMESTAMP);
 
 
CREATE PUBLIC SYNONYM T0101 FOR APPS.T0101;
 
COMMIT;

Create Sequence for Primary Key:

 
CREATE SEQUENCE T0101SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1;
 
 
COMMIT;

 

Create DB Trigger:

 
CREATE OR REPLACE TRIGGER T0101B4I
  BEFORE INSERT
ON T0101   FOR EACH ROW
DECLARE
    V_C1   NUMBER;
BEGIN
    SELECT T0101SEQ.NEXTVAL INTO V_C1 FROM DUAL;
 :NEW.C1 := V_C1;
EXCEPTION
    WHEN OTHERS THEN
      RAISE;
END T0101B4I;
/
 
 
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  T0101 TO PUBLIC WITH GRANT OPTION;
 
 
COMMIT;
 

Example Insert/Select Scripts:

 
INSERT INTO T0101 (C2) VALUES ('TEST2');
COMMIT;
 
 
SELECT C1, C2, C3, C4 FROM T0101
WHERE C3 > SYSDATE-1
ORDER BY C1 DESC;
 
 
COMMIT;
 

Insert script from program:

 
INSERT INTO T0101 (C2) select 'SID:'||sys_context('userenv','sessionid')||';SoldTo:'||p_sold_to_org_id from dual;
 
 
CREATE OR REPLACE PROCEDURE t0101_log(V_STRING VARCHAR2) IS
   PRAGMA autonomous_transaction;
BEGIN
       INSERT INTO T0101 (C2) VALUES ('x'||V_STRING);
       COMMIT;
 
end t0101_log;
/
 
COMMIT;
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

XML, PL/SQL, Database, 19c, 12.1.0.0, SQL Plus, Parser, APEX JSON, Patch