Home » SQL & PL/SQL » SQL & PL/SQL » How to generate a insert script as it is what ever we running the insert script? (insert into dept(deptno,dname,loc) values (70,'PERSONNEL','DALLAS'))
How to generate a insert script as it is what ever we running the insert script? [message #657856] Thu, 24 November 2016 09:16 Go to next message
narendraenamala
Messages: 5
Registered: November 2016
Junior Member
Here I am attaching the code
-- PACKAGE FOR INSERT SCRIPT 
CREATE OR REPLACE PACKAGE DatabaseUtilities AS
    function TriggerReferenceToRowType(prefix VARCHAR2,tableName VARCHAR2) RETURN VARCHAR2;
END DatabaseUtilities;
/

CREATE OR REPLACE PACKAGE BODY DatabaseUtilities 
AS 
    FUNCTION table2rowtype ( prefix IN VARCHAR2, tableName  IN VARCHAR2) RETURN VARCHAR2
    IS
        CURSOR v_cursor IS
        SELECT column_name FROM user_tab_columns 
        WHERE TABLE_NAME = UPPER(tableName) 
        ORDER BY column_id;
        
        v_sql  varchar2(2000);
        vv_sql  varchar2(2000);
    BEGIN
        FOR v_rec IN v_cursor
        LOOP
            IF v_sql iS NOT NULL 
            THEN
                v_sql := v_sql || ',';
                vv_sql:= vv_sql || ',';
            END IF;
            v_sql := v_sql || prefix || v_rec.column_name;
            vv_sql := vv_sql || v_rec.column_name;
       end  LOOP; 
       
   --  v_sql := 'SELECT ' || v_sql ; --|| ' FROM DUAL';
     v_sql := v_sql ; 
  v_sql := 'insert into  '||tableName ||' ('||vv_sql  ||') values (' || v_sql || ')';
        return v_sql;
    END;
    
    /* ----------------------------------------------------------------------------*/
    
   FUNCTION TriggerReferenceToRowType (prefix VARCHAR2,tableName IN VARCHAR2)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN table2rowtype(prefix, tableName);
    END;
    
END DatabaseUtilities;
/
-----------------TRIGEGR 
CREATE OR REPLACE TRIGGER TableToArchive_ArchiveTrigger
BEFORE INSERT or UPDATE or DELETE ON DEPT
REFERENCING OLD as old NEW as new
FOR EACH ROW
DECLARE
    archiveRec  DEPT%ROWTYPE ;
    v varchar2(2000);
BEGIN

    IF inserting OR updating or Deleting THEN
    
      --  EXECUTE IMMEDIATE DatabaseUtilities.TriggerReferenceToRowType(':new.','DEPT') INTO archiveRec;

select :new.deptno,:new.dname,:new.loc into archiveRec from dual;

 v:=  DatabaseUtilities.TriggerReferenceToRowType(':new.','DEPT') ;
 -- dbms_output.put_line(v||' '||archiveRec.deptno||','||archiveRec.dname||','||archiveRec.loc||');');
--  dbms_output.put_line(v);
-- v:= v||' into '||archiveRec||' from dual';
 --v:= v||' into archiveRec from dual';
  dbms_output.put_line(v);
-- execute immediate 'select '|| v || 'into '|| archiveRec ||' from dual';
--  'select '||v || into archiveRec from dual;
--   execute immediate  v;
      -- dbms_output.put_line('after '||archiveRec.deptno);
     

    ELSE
         v:=  DatabaseUtilities.TriggerReferenceToRowType(':old.','DEPT') ;
             
      --  EXECUTE IMMEDIATE DatabaseUtilities.TriggerReferenceToRowType(':old.','DEPT') INTO archiveRec;

    END IF;

    -- call to package/procedure to process archiving:  archive(archiveRec) ;

END ;
/

insert into dept(deptno,dname,loc) values (70,'PERSONNEL','DALLAS')


[Exit MC: add code tags]

[Updated on: Thu, 24 November 2016 09:59] by Moderator

Report message to a moderator

Re: How to generate a insert script as it is what ever we running the insert script? [message #657857 is a reply to message #657856] Thu, 24 November 2016 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 25849
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

EXECUTE IMMEDIATE does NOT scale.
while at one level it sounds elegant to have a generic INSERT procedure, it is a bad idea to actually put it into Production.
Tables have different data types for columns which is a challenge for generic INSERT.
EXECUTE IMMEDIATE can only do row by row, which is slow by slow, INSERT.

I strongly suggest that you abandon this "solution" to a problem that does not exist.
Re: How to generate a insert script as it is what ever we running the insert script? [message #657858 is a reply to message #657856] Thu, 24 November 2016 09:30 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

What are you asking?
Re: How to generate a insert script as it is what ever we running the insert script? [message #657859 is a reply to message #657856] Thu, 24 November 2016 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I agree with John, where/what is the question?
Or do you expect we reverse engineer your incorrect code to guess what it should do?

Re: How to generate a insert script as it is what ever we running the insert script? [message #657898 is a reply to message #657859] Fri, 25 November 2016 22:08 Go to previous messageGo to next message
narendraenamala
Messages: 5
Registered: November 2016
Junior Member
My Intention is that when user executing insert into dept(deptno,dname,loc) values (70,'PERSONNEL','DALLAS');

I need to read that insert script from DB and save same script in a test document.
Re: How to generate a insert script as it is what ever we running the insert script? [message #657906 is a reply to message #657898] Sat, 26 November 2016 01:27 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
Are you saying that you want to capture all the INSERT statements? If so, just use audit: you can read them from the SQL_TEXT column of UNIFIED_AUDIT_TRAIL.
Re: How to generate a insert script as it is what ever we running the insert script? [message #657907 is a reply to message #657906] Sat, 26 November 2016 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or USER/DBA_AUDIT_TRAIL is you are in a version before 12c.

Re: How to generate a insert script as it is what ever we running the insert script? [message #657909 is a reply to message #657906] Sat, 26 November 2016 04:29 Go to previous messageGo to next message
narendraenamala
Messages: 5
Registered: November 2016
Junior Member
Hi John/Michel,
Thanq for your response. I need to capture insert statement when an insert event occurred on a dept table(dept is just an example). I need to save that script for a security reasons.

Thanks in advance
Re: How to generate a insert script as it is what ever we running the insert script? [message #657910 is a reply to message #657909] Sat, 26 November 2016 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As John said you already have it in the audit table... if you activate the audit, so you have nothing to do and it is far more efficient than a trigger and,as you want, it is done when the statement fires (more it can also record when the statement fails) and it is more secure than recording it in a custom table as only DBA can query it and can't modify it unless using SYSDBA which is automatically audited...

Re: How to generate a insert script as it is what ever we running the insert script? [message #657911 is a reply to message #657910] Sat, 26 November 2016 04:40 Go to previous message
Michel Cadot
Messages: 65380
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

to state it more precisely, the only things you have to do it:
* set audit_trail parameter to "db, extended".
* execute "audit insert on dept;", or if you want only successful statements: "audit insert on dept whenever successful;".

Previous Topic: using rownum
Next Topic: Function returns Record - How to select
Goto Forum:
  


Current Time: Sun Feb 18 21:41:28 CST 2018

Total time taken to generate the page: 0.02443 seconds