CODEWITHSUNDEEP
oracle-databaseplsqlcursorprocedure
t v
t v

Reputation: 208

how to catch the error of execute immediate (open for)?

I have this procedure:

PROCEDURE proc_with_cursor(  P_PARAM1_IN NUMBER, P_PARAM2_IN NUMBER, P_CURSOR_OUT OUT SYS_REFCURSOR)
    IS
    v_sql   VARCHAR2 (32767);
    v_script   VARCHAR2 (32767);
    v_bind_vars VARCHAR2 (32767);
BEGIN
     v_sql := 'BEGIN OPEN :1 FOR :2 ';
     v_bind_vars := P_PARAM1_IN||', '||P_PARAM2_IN;
     v_sql := v_sql||' USING '||v_bind_vars||'; ';
    --tried also following  but also couldn't catch the error!
    --v_sql := v_sql ||'EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(''error: ''||SQLCODE||'' - ''||SQLERRM); END;';
    v_sql := v_sql||' END;';

    v_script := 'select sysdate from dual where 1= :bind_first and 2 = :bindsecond';

    EXECUTE IMMEDIATE v_sql using P_CURSOR_OUT, v_script;     

    EXCEPTION 
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('error: '||SQLCODE||' - '||SQLERRM); -- this part is not catching the error!!!!!   
END;

Sometimes, the script will throw an error like:

ORA-12847: retry parsing due to concurrent DDL operation

[Error] Execution (1: 1): ORA-12847: retry parsing due to concurrent DDL operation

My question is: how to catch that error?

Upvotes: 0

Views: 3801

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

I don't understand your problem. If you have this procedure

CREATE OR REPLACE PROCEDURE proc_with_cursor(  p_param1_in NUMBER, p_param2_in NUMBER, p_cursor_out OUT SYS_REFCURSOR)
IS
    v_script   VARCHAR2 (32767);
BEGIN
    v_script := 'select sysdate fromM dual where 1= :bind_first and 2 = :bindsecond';
    OPEN p_cursor_out FOR v_script USING p_param1_in, p_param2_in;
END;

Then you get this:

DECLARE
    cur SYS_REFCURSOR;
BEGIN
    proc_with_cursor(1, 2, cur);    
end;
/

Error at line 1
ORA-00923: FROM keyword not found where expected
ORA-06512: at "xxx.PROC_WITH_CURSOR", line 6
ORA-06512: at line 4

What else would you like to get? Your (poor) code is working:

CREATE OR REPLACE PROCEDURE proc_with_cursor(  P_PARAM1_IN NUMBER, P_PARAM2_IN NUMBER, P_CURSOR_OUT OUT SYS_REFCURSOR) IS
    v_sql   VARCHAR2 (32767);
    v_script   VARCHAR2 (32767);
    v_bind_vars VARCHAR2 (32767);
BEGIN
    v_sql := 'BEGIN OPEN :1 FOR :2 ';
    v_bind_vars := P_PARAM1_IN||', '||P_PARAM2_IN;
    v_sql := v_sql||' USING '||v_bind_vars||'; ';
    v_sql := v_sql||' END;';

    v_script := 'select sysdate fromm dual where 1= :bind_first and 2 = :bindsecond';

    EXECUTE IMMEDIATE v_sql USING P_CURSOR_OUT, v_script;     

EXCEPTION 
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('error: '||SQLCODE||' - '||SQLERRM);   
END;


DECLARE
    cur SYS_REFCURSOR;
BEGIN
    proc_with_cursor(1,2, cur);
END;

Prints error: -923 - ORA-00923: FROM keyword not found where expected on the DBMS_OUTPUT.

If you like to get the exception then do

CREATE OR REPLACE PROCEDURE proc_with_cursor(  P_PARAM1_IN NUMBER, P_PARAM2_IN NUMBER, P_CURSOR_OUT OUT SYS_REFCURSOR) IS
    v_sql   VARCHAR2 (32767);
    v_script   VARCHAR2 (32767);
    v_bind_vars VARCHAR2 (32767);
BEGIN
    ...    
    EXECUTE IMMEDIATE v_sql USING P_CURSOR_OUT, v_script;     

EXCEPTION 
    WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('error: '||SQLCODE||' - '||SQLERRM);  
        RAISE;  
END;

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

You cannot bind object names (e.g. cursor names) and variable names into dynamic SQL, only values. I think this may do what you intend:

PROCEDURE proc_with_cursor(  p_param1_in NUMBER, p_param2_in NUMBER, p_cursor_out OUT SYS_REFCURSOR)
IS
    v_script   VARCHAR2 (32767);
BEGIN
    v_script := 'select sysdate from dual where 1= :bind_first and 2 = :bindsecond';

    OPEN p_cursor_out FOR v_script USING p_param1_in, p_param2_in;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('error: '||sqlcode||' - '||sqlerrm); 
END;

Upvotes: 2

Related Questions