Hi All,
Requirement : We need to pass table name as a parameter into the Procedure and also further I need to concatenate that value with a string to make a correct table name which will be available in database.
For ex :As per my code , if am giving the argument as
Execute PROCEDURE (10,'DE','CON','INC');
So by this I am trying to make a table in the Procedure as given below :
CON_SAP_TEMP_DE_CON_INC (Select table in Cursor)
CON_SAP_UNLOAD_DE_CON_IN (Insert Table during Insertion)
Please see my code Now :
create or replace PROCEDURE UNLOAD_CON_SAP_RESP_LOG
(PROC_ARRAY_SIZE IN PLS_INTEGER DEFAULT 10000,OPCO_UPCASE IN VARCHAR2,V_SEGMENT IN VARCHAR2,V_EVENT_NAME IN VARCHAR2)
AS
V_TABLE_NAME varchar2(100) := OPCO_UPCASE||'_'||V_SEGMENT||'_'||V_EVENT_NAME;
V_TABLE_NAME_TEST varchar2(100) := 'CON_SAP_UNLOAD'||'_'||V_TABLE_NAME;
V_TABLE_NAME_TEST_TEMP varchar2(100) := 'CON_SAP_TEMP'||'_'||V_TABLE_NAME;
sql_stmt_t_select VARCHAR2(32767);
sql_stmt_t_insert VARCHAR2(32767);
type rc is ref cursor;
CON_SAP_CUR rc;
TYPE CON_SAP_RESP IS TABLE OF CONTRACT_SAP_RESPONSE_LOG%ROWTYPE;
V_CON_SAP_RESP CON_SAP_RESP;
BEGIN
sql_stmt_t_select := 'SELECT * FROM CON_SAP_TEMP WHERE CONTRACT_ID IN
(SELECT CONTRACT_ID FROM '||V_TABLE_NAME_TEST_TEMP||')';
sql_stmt_t_insert := 'INSERT INTO '||V_TABLE_NAME_TEST||' VALUES V_CON_SAP_RESP(I)';
DBMS_OUTPUT.PUT_LINE(sql_stmt_t_select);
DBMS_OUTPUT.PUT_LINE(sql_stmt_t_insert);
DBMS_OUTPUT.PUT_LINE('PROCEDURE STARTS' || ' ' || TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'));
OPEN CON_SAP_CUR for sql_stmt_t_select;
LOOP
FETCH CON_SAP_CUR BULK COLLECT INTO V_CON_SAP_RESP LIMIT PROC_ARRAY_SIZE;
FORALL I IN 1..V_CON_SAP_RESP.COUNT
sql_stmt_t_insert;
--INSERT INTO con_sap_unload_de_con_inc VALUES V_CON_SAP_RESP(I);
EXIT WHEN CON_SAP_CUR%NOTFOUND;
END LOOP;
CLOSE CON_SAP_CUR;
DBMS_OUTPUT.PUT_LINE('PROCEDURE ENDS' || ' ' || TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
--Consider logging the error and then re-raise
RAISE;
END UNLOAD_CON_SAP_RESP_LOG;
------------------------------------------ If you focus on insert , I have commented out one line "--INSERT INTO con_sap_unload_de_con_inc VALUES V_CON_SAP_RESP(I);"
, if I am passing this line , procedure is getting compiled and am not facing any issue because we are passing a hard coded table in this statement, but my requirement is to have table which I am resolving from V_TABLE in beginning.
Could you please help me in the Insert statement ?
What will be the correct way to write that Insert.
You're not specifying what the value you're inserting is! You need to bind to I.
But what you should do instead is ditch the loop. Make this a straight insert from select:
sql_stmt_t_select := 'INSERT INTO '||V_TABLE_NAME_TEST||' (cols)
SELECT cols FROM CON_SAP_TEMP WHERE CONTRACT_ID IN
(SELECT CONTRACT_ID FROM '||V_TABLE_NAME_TEST_TEMP||')'; Also: do you really need dynamic SQL? Static SQL is far safer.
If you really must use dynamic SQL, then ensure you sanitize user input with dbms_assert:
https://oracle-base.com/articles/10g/dbms_assert_10gR2 Otherwise you leave yourself exposed to SQL injection attacks!