Home » SQL & PL/SQL » SQL & PL/SQL » Procedure changes (oracle 10g)
Procedure changes [message #573740] Wed, 02 January 2013 22:56 Go to next message
rajmighty972
Messages: 11
Registered: December 2012
Junior Member
Hi I am using the below procedure to move data from staging to target.

but i need to use the bukl collect using the loop to insert into the target table and move errorneous data into the log table using exception. with the current procedure i am not able to commit succesful and move errored out into log table.

Please advise.

CREATE OR REPLACE PROCEDURE PRC_ETL_PROC(P_T_NAME1 VARCHAR2,P_T_NAME2 VARCHAR2) IS
   COLUMN_LIST_V   VARCHAR2(32000);
   SQLV            VARCHAR2(32000);
   V_TNAME1        VARCHAR2(30);
   V_TNAME2        VARCHAR2(30);
   V_STATUS 	   NUMBER;
   CURSOR C_GET_TABLE(C_TNAME VARCHAR2) IS SELECT TABLE_NAME FROM USER_TABLES 
WHERE TABLE_NAME = UPPER(C_TNAME);

BEGIN
V_STATUS:=P_STATUS;
   -- VERIFY TABLE EXISTANCE
   	OPEN C_GET_TABLE(P_T_NAME1) ;
   		FETCH C_GET_TABLE INTO  V_TNAME1;
   	CLOSE C_GET_TABLE;

   	OPEN C_GET_TABLE(P_T_NAME2);
   		FETCH C_GET_TABLE INTO  V_TNAME2;
   	CLOSE C_GET_TABLE;

   -- GATHER COMMON COLUMNS
   FOR R3 IN (SELECT COLUMN_NAME
                   , DATA_TYPE
                   , DATA_LENGTH
                   , DATA_PRECISION
                   , DATA_SCALE
              FROM   USER_TAB_COLUMNS
              WHERE  TABLE_NAME = V_TNAME1
              AND    DATA_TYPE IN
                        ('VARCHAR', 'VARCHAR2', 'CHAR', 'NUMBER', 'INTEGER'
                       , 'FLOAT', 'DATE')
              INTERSECT
              SELECT COLUMN_NAME
                   , DATA_TYPE
                   , DATA_LENGTH
                   , DATA_PRECISION
                   , DATA_SCALE
              FROM   USER_TAB_COLUMNS
              WHERE  TABLE_NAME = V_TNAME2
              AND    DATA_TYPE IN
                        ('VARCHAR', 'VARCHAR2', 'CHAR', 'NUMBER', 'INTEGER'
                       , 'FLOAT', 'DATE'))
   	LOOP
      		COLUMN_LIST_V    := COLUMN_LIST_V || ',' || R3.COLUMN_NAME;
   	END LOOP;

   		COLUMN_LIST_V    := SUBSTR(COLUMN_LIST_V, 2);
   		DBMS_OUTPUT.PUT_LINE('COLUMN_LIST_V = ' || COLUMN_LIST_V);

	-- CHECK WHETHER THERE ARE SHARED COLUMNS:
   	IF COLUMN_LIST_V IS NOT NULL THEN
		-- CONCATENATE THE INSERT STATEMENT    
      		SQLV    := 'INSERT INTO ' || V_TNAME2 || ' (' || COLUMN_LIST_V ||
 ') SELECT ' || COLUMN_LIST_V || ' FROM ' || V_TNAME1 ||' '|| ';
                             
      		DBMS_OUTPUT.PUT_LINE('SQLV = ' || SQLV);
	-- EXECUTE THE INSERT STATEMENT
      		EXECUTE IMMEDIATE (SQLV);
   	END IF;
 COMMIT;

	
EXCEPTION
     WHEN NO_DATA_FOUND THEN
                  DBMS_OUTPUT.PUT_LINE('NO DATA AVAILABLE IN THE TABLE');
          				NULL;
     WHEN OTHERS THEN
       PRC_INS_ERROR_LOG(SQLCODE,SQLERRM,'','');
      
END;
/

[Updated on: Thu, 03 January 2013 00:46] by Moderator

Report message to a moderator

Re: Procedure changes [message #573743 is a reply to message #573740] Wed, 02 January 2013 23:15 Go to previous messageGo to next message
BlackSwan
Messages: 22927
Registered: January 2009
Senior Member
>with the current procedure i am not able to commit succesful and move errored out into log table.

Realize that we don't have your table or data, so we can't run posted code.

You report a problem exists, but choose not to post actual error.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


You have a mystery & we have no clues provided by you.
Re: Procedure changes [message #573753 is a reply to message #573740] Thu, 03 January 2013 00:25 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Quote:
i need to use the bukl collect using the loop to insert into the target table


Why u want to use bulk collect to insert data? I think Bulk collect not required....

Quote:
move erroneous data into the log table using exception


What exactly is erroneous data ?

May be below code would help

Ahhh!!
u would need a log table to log error details if applies


create table log_tab(msg varchar2(2000),dt date);

SQL> desc log_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------

 MSG                                                VARCHAR2(2000)
 DT                                                 DATE



CREATE OR REPLACE PROCEDURE P1(P_T_NAME1 VARCHAR2,P_T_NAME2 VARCHAR2) IS
   COLUMN_LIST_V   VARCHAR2(32000);
   SQLV            VARCHAR2(32000);
   V_TNAME1        VARCHAR2(30);
   V_TNAME2        VARCHAR2(30);
   V_STATUS 	   NUMBER;
	 o_errormsg	 varchar2(1000):=NULL;
   CURSOR C_GET_TABLE(C_TNAME VARCHAR2) IS SELECT TABLE_NAME FROM USER_TABLES 
WHERE TABLE_NAME = UPPER(C_TNAME);

BEGIN
--V_STATUS:=P_STATUS;-------NOT SURE WHAT IS THIS SO COMMENTED OUT
   -- VERIFY TABLE EXISTANCE
   	OPEN C_GET_TABLE(P_T_NAME1) ;
   		FETCH C_GET_TABLE INTO  V_TNAME1;
   	CLOSE C_GET_TABLE;

   	OPEN C_GET_TABLE(P_T_NAME2);
   		FETCH C_GET_TABLE INTO  V_TNAME2;
   	CLOSE C_GET_TABLE;

   -- GATHER COMMON COLUMNS
   FOR R3 IN (SELECT COLUMN_NAME
                   , DATA_TYPE
                   , DATA_LENGTH
                   , DATA_PRECISION
                   , DATA_SCALE
              FROM   USER_TAB_COLUMNS
              WHERE  TABLE_NAME = V_TNAME1
              AND    DATA_TYPE IN
                        ('VARCHAR', 'VARCHAR2', 'CHAR', 'NUMBER', 'INTEGER'
                       , 'FLOAT', 'DATE')
              INTERSECT
              SELECT COLUMN_NAME
                   , DATA_TYPE
                   , DATA_LENGTH
                   , DATA_PRECISION
                   , DATA_SCALE
              FROM   USER_TAB_COLUMNS
              WHERE  TABLE_NAME = V_TNAME2
              AND    DATA_TYPE IN
                        ('VARCHAR', 'VARCHAR2', 'CHAR', 'NUMBER', 'INTEGER'
                       , 'FLOAT', 'DATE'))
   	LOOP
      		COLUMN_LIST_V    := COLUMN_LIST_V || ',' || R3.COLUMN_NAME;
   	END LOOP;

   		COLUMN_LIST_V    := SUBSTR(COLUMN_LIST_V, 2);
   		DBMS_OUTPUT.PUT_LINE('COLUMN_LIST_V = ' || COLUMN_LIST_V);

	-- CHECK WHETHER THERE ARE SHARED COLUMNS:
   	IF COLUMN_LIST_V IS NOT NULL THEN
		-- CONCATENATE THE INSERT STATEMENT    
      		SQLV    := 'INSERT INTO ' || V_TNAME2 || ' (' || COLUMN_LIST_V ||
 ') SELECT ' || COLUMN_LIST_V || ' FROM ' || V_TNAME1 ||' '|| '';
                             
      		DBMS_OUTPUT.PUT_LINE('SQLV = ' || SQLV);
	-- EXECUTE THE INSERT STATEMENT
	begin
      		EXECUTE IMMEDIATE SQLV;
      		EXCEPTION
     WHEN NO_DATA_FOUND THEN
                  DBMS_OUTPUT.PUT_LINE('NO DATA AVAILABLE IN THE TABLE');
          				NULL;
     WHEN OTHERS THEN
	 o_errormsg	:= SUBSTR(SQLERRM,1,100);
         insert into log_tab(msg,dt) values (o_errormsg,sysdate);
          				
  end;
   	END IF;
 COMMIT;

	
EXCEPTION
     WHEN NO_DATA_FOUND THEN
                  DBMS_OUTPUT.PUT_LINE('NO DATA AVAILABLE IN THE TABLE');
          				NULL;
     WHEN OTHERS THEN
     NULL;
       --PRC_INS_ERROR_LOG(SQLCODE,SQLERRM,'','');-------NOT SURE WHAT IS THIS SO COMMENTED OUT
      
END;
/

[Updated on: Thu, 03 January 2013 00:46] by Moderator

Report message to a moderator

Re: Procedure changes [message #573754 is a reply to message #573743] Thu, 03 January 2013 00:34 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that use of cursor (C_GET_TABLE) is pretty much useless here. Mind you: procedure takes table name as a parameter; you pass that parameter to a cursor and select exactly the same name from USER_TABLES, so - what's the point in doing that (apart from the fact that you are putting table name into uppercase, and - to do that - you certainly don't need a cursor).

So, you are (more or less) performing
insert into table_2 select * from table_1
(yes, I know, "*" is not really "*", it is a column list). For some reason (which ones? Can you tell? Just being curious) some records fail to be inserted and - as I understood it - you want to insert valid records and put invalid ones aside. Would Inserting Data with DML Error Logging do any good in your case?

Finally, it appears that you might want to remove WHEN OTHERS exception handler as, obviously, you don't do anything "smart" with WHEN NO_DATA_FOUND either so why wouldn't you let Oracle raise that "other" exception?


[EDIT] While I was typing, @rishwinger posted his message - didn't see it.

Anyway: his logging isn't exactly what you are after, I think. INSERT won't raise NO_DATA_FOUND (it will just insert 0 records, i.e. do nothing), and OTHERS might be recorded as the fact that something happened, but won't insert anything).

[Updated on: Thu, 03 January 2013 00:44]

Report message to a moderator

Re: Procedure changes [message #573775 is a reply to message #573740] Thu, 03 January 2013 02:09 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
INSERT statement itself is able to log invalid rows to its own log table.
For 10gR2, you may find an example e.g. in Oracle documentation here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ
Seems to me it is more than you have now - you will see entire row content with error code and message. You should try to use it.
Re: Procedure changes [message #573784 is a reply to message #573775] Thu, 03 January 2013 02:54 Go to previous message
rajmighty972
Messages: 11
Registered: December 2012
Junior Member
Thanks..
Previous Topic: how to insert data multiple bases using the multiple database links?
Next Topic: DBMS_PARALLEL_EXECUTE help needed.
Goto Forum:
  


Current Time: Fri Oct 31 10:55:37 CDT 2014

Total time taken to generate the page: 0.78343 seconds