Here is the normal stored procedure we used to test.
CREATE OR REPLACE
PACKAGE vern_pkg IS
PROCEDURE pop (p_table_name IN VARCHAR2,
p_text_value IN vern_test.text_value%TYPE,
p_num_value IN vern_test.num_value%TYPE DEFAULT null,
p_dt IN vern_test.dt%TYPE DEFAULT SYSDATE);
PROCEDURE get_sysdate(P_DT IN OUT VERN_TEST.DT%TYPE);
END; -- Package spec
/
CREATE OR REPLACE
PACKAGE BODY vern_pkg IS
PROCEDURE pop (p_table_name IN VARCHAR2,
p_text_value IN vern_test.text_value%TYPE,
p_num_value IN vern_test.num_value%TYPE DEFAULT NULL,
p_dt IN vern_test.dt%TYPE DEFAULT SYSDATE) IS
v_sql VARCHAR2 (4000);
BEGIN
v_sql :=
'INSERT INTO '
|| p_table_name
|| ' VALUES ('''
|| p_text_value
|| ''', '
|| p_num_value
|| ', TO_DATE('''
|| p_dt
|| ''',''DD-MON-YYYY''))';
EXECUTE IMMEDIATE v_sql;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
PROCEDURE get_sysdate (p_dt IN OUT vern_test.dt%TYPE) IS
BEGIN
p_dt := SYSDATE;
END;
END;
/
Here we pass correct info and get a success using sqlplus:
SQL> EXEC VERN_PKG.pop ('VERN_TEST', 'BOGUS', 12345, '01-JAN-2010');
PL/SQL procedure successfully completed.
Here we pass the incorrect table name 'XVERN_TEST' via sqlplus:
SQL> EXEC VERN_PKG.pop ('XVERN_TEST', 'BOGUS', 12345, '01-JAN-2010');
ORA-00942: table or view does not exist
PL/SQL procedure successfully completed.
When running this stored procedure from VC, is there any way to capture the ORA error generated?
BTW, this is mostly greek to me and I rely heavily on my DBA to provide this info so I might be using incorrect terminology.