hi,
I am getting the following error when trying to call the following procedure through SQL developer Version 4.1.1.19.
Database version :Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
This error will come only when we run the procedure using RUN option(CNTRL+F10) of package/procedure.
When the procedure is executed by writing stub in SQL WORKSHEET , it will run successfully and return the result.
Please let me know whether any work around is available for this issue.
Error:
Connecting to the database LLLD.
Invalid column index
Process exited.
Disconnecting from the database LLLD.
CREATE PROCEDURE new_procedure (
O_LIST_REF OUT NOCOPY SYS_REFCURSOR,
O_RTRN_CD OUT NOCOPY number,
O_ERR_NBR OUT NOCOPY VARCHAR2,
O_ERR_DESC OUT NOCOPY VARCHAR2,
O_ERR_OBJECT_NAME OUT NOCOPY VARCHAR2
)
as
BEGIN
OPEN O_LIST_REF FOR SELECT ......;
EXCEPTION
WHEN NO_DATA_FOUND THEN
O_RTRN_CD := 7;
O_ERR_NBR := SQLCODE;
O_ERR_DESC := SUBSTR (SQLERRM, 1, 100);
O_ERR_OBJECT_NAME := 'new_procedure';
WHEN OTHERS THEN
O_RTRN_CD := 4;
O_ERR_NBR := SQLCODE;
O_ERR_DESC := SUBSTR (SQLERRM, 1, 100);
O_ERR_OBJECT_NAME := 'new_procedure';
END new_procedure;
Stub:
DECLARE
O_LIST_REF SYS_REFCURSOR;
O_RTRN_CD NUMBER;
O_ERR_NBR NUMBER;
O_ERR_DESC VARCHAR2(200);
O_ERR_OBJECT_NAME VARCHAR2(200);
BEGIN
new_procedure(
O_LIST_REF => O_LIST_REF ,
O_RTRN_CD => O_RTRN_CD,
O_ERR_NBR => O_ERR_NBR,
O_ERR_DESC => O_ERR_DESC,
O_ERR_OBJECT_NAME => O_ERR_OBJECT_NAME
);
DBMS_SQL.RETURN_RESULT( O_LIST_REF );
dbms_output.put_line( O_RTRN_CD);
dbms_output.put_line(O_ERR_NBR);
dbms_output.put_line(O_ERR_DESC);
dbms_output.put_line( O_ERR_OBJECT_NAME );
END;
Thank you.
Manjusha