Home » SQL & PL/SQL » SQL & PL/SQL » Procedure changes (oracle 10g)
Procedure changes [message #573740] |
Wed, 02 January 2013 22:56 |
|
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 #573753 is a reply to message #573740] |
Thu, 03 January 2013 00:25 |
|
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 |
|
Littlefoot
Messages: 21807 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) performinginsert 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
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 04:45:59 CDT 2024
|