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
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
|
] 
Blog
Search
Help
Members
Register
Login
Home
