checking the validity of a stored procedure statement

Question

I am trying to execute this stored procedure statement,

CREATE OR REPLACE PROCEDURE table_records_select IS
    TYPE loc_array_type IS TABLE OF VARCHAR2(100)
        INDEX BY binary_integer;
    dml_str VARCHAR2        (200);
    loc_array    loc_array_type;
BEGIN
    -- bulk fetch the list of tables
    SELECT table_name BULK COLLECT INTO loc_array
        FROM all_tab_columns;
    -- for each table, delete the records where EXCN_ID matches with EXCN_ID of t_int_excn_log table where excn_strt_tm < sysdate-7 
    FOR i IN loc_array.first..loc_array.last LOOP



        dml_str := 'select B.* from t_int_excn_log A,'
                    || loc_array(i) || ' B'
                    ||'where A.excn_strt_tm < sysdate-7 and A.excn_id=B.excn_id';

    EXECUTE IMMEDIATE dml_str ;
    END LOOP;
END;
/
SHOW ERRORS;

It seems that stored procedure is created successfully and showing valid.

But when I try to execute it, It shows various errors,

ORA-00933: SQL command not properly ended ORA-06512: at "HIAB_UAT.TABLE_RECORDS_SELECT", line 19 ORA-06512: at line 3

In the end it says 'Procedure Completed'

Can anyone help me on this.

check in which loop the error returns. Try adding something simple like dbms_output.put_line(dml_str) before the execute immediate. — OracleDev

Unknown Unknown · Accepted Answer · 2018-05-11T10:32:26

There is one mistake in your Dynamic SQL Preparation: at second line of "dml_str" preparation. Space missed just after alias "B".

Your Code:

|| loc_array(i) || ' B'
||'where A.excn_strt_tm < sysdate-7 and A.excn_id=B.excn_id';

It should be:

|| loc_array(i) || ' B '
||'where A.excn_strt_tm < sysdate-7 and A.excn_id=B.excn_id';



CREATE OR REPLACE PROCEDURE table_records_select IS
    TYPE loc_array_type IS TABLE OF VARCHAR2(100)
        INDEX BY binary_integer;
    dml_str VARCHAR2        (200);
    loc_array    loc_array_type;
BEGIN
    -- bulk fetch the list of tables
    SELECT table_name BULK COLLECT INTO loc_array
        FROM user_tab_columns;--all_tab_columns;
    -- for each table, delete the records where EXCN_ID matches with EXCN_ID of t_int_excn_log table where excn_strt_tm < sysdate-7 
    FOR i IN loc_array.first..loc_array.last LOOP



        dml_str := 'select B.* from t_int_excn_log A,'
                    || loc_array(i) || ' B '
                    ||'where A.excn_strt_tm < sysdate-7 and A.excn_id=B.excn_id';
    EXECUTE IMMEDIATE dml_str ;
    END LOOP;
END;

checking the validity of a stored procedure statement

1 Answers