Oracle Database Discussions

    PROCEDURE Logincheck(

                    v\_ExecSpname IN CLOB,

                    v\_ExecQueryparam IN XMLTYPE DEFAULT NULL,

                    v\_GlobalParam XMLTYPE DEFAULT NULL)

    AS

            v\_Error NVARCHAR2(32767);

            C INTEGER;

            N INTEGER;

            RC SYS\_REFCURSOR;

    BEGIN

            INSERT INTO GTT.tGLOBAL

            SELECT  XT.\*

            FROM    XMLTABLE('/QueryParam'

            PASSING v\_GlobalParam

            COLUMNS "CompID" NUMBER(5) PATH 'CompID' ,

            "FinYearID" NUMBER(4) PATH 'FinYearID' ,

            "MenuID" NUMBER(5) PATH 'MenuID' ,

            "EmployeeID" NUMBER(5) PATH 'EmployeeID' ,

            "CallByPanel" NVARCHAR2(10) PATH 'CallByPanel') XT;

            C := SYS.DBMS\_SQL.OPEN\_CURSOR(false);

            SYS.DBMS\_SQL.PARSE (C, v\_ExecSPName, DBMS\_SQL.native) ;

            SYS.DBMS\_SQL.BIND\_VARIABLE (C, ':XMLDOC', v\_ExecQueryParam) ;

            N := SYS.DBMS\_SQL.EXECUTE (C) ;

            SYS.DBMS\_SQL.GET\_NEXT\_RESULT (C, RC) ;

            SYS.DBMS\_SQL.RETURN\_RESULT (RC) ;

    EXCEPTION

    WHEN NO\_DATA\_FOUND THEN

            NULL;

    WHEN OTHERS THEN

            SYS.DBMS\_OUTPUT.PUT\_LINE('# Dynamic Depth : ' || SYS.UTL\_CALL\_STACK.DYNAMIC\_DEPTH);

            DECLARE

                    v\_depth NUMBER(5) := 0;

            BEGIN

                    IF (SYS.UTL\_CALL\_STACK.DYNAMIC\_DEPTH > 0) THEN

                            v\_depth :=SYS.UTL\_CALL\_STACK.DYNAMIC\_DEPTH;

                    END IF;

                    Record\_Error(v\_depth);

                    --                        v\_Error := SYS.DBMS\_SQL.LAST\_ERROR\_POSITION;

                    --                        SYS.DBMS\_OUTPUT.PUT\_LINE('#Error Occured On Offset :' || v\_Error);

            END;

    END LOGINCHECK;

This is the procedure Called at Line 39:

PROCEDURE Record_Error(

            v\_depth NUMBER)

IS

    PRAGMA AUTONOMOUS\_TRANSACTION;

TYPE SQL_ERROR_LOG_T

IS

    TABLE OF SQL\_ERROR\_LOG%RowTYPE;

    Error\_Log SQL\_ERROR\_LOG\_T;

BEGIN

    Error\_Log := SQL\_ERROR\_LOG\_T();

    Error\_Log.EXTEND(v\_depth);

    FOR i IN 1..v\_depth

    LOOP

            Error\_Log(i).ERROR\_TIME := SYSTIMESTAMP;

            Error\_Log(i).ERROR\_NUMBER := SYS.UTL\_CALL\_STACK.ERROR\_NUMBER(i);

            Error\_Log(i).ERROR\_MSG := SYS.UTL\_CALL\_STACK.ERROR\_MSG (i);

            Error\_Log(i).UNIT\_LINE := SYS.UTL\_CALL\_STACK.UNIT\_LINE (i);

            Error\_Log(i).SUBPROGRAM := SYS.UTL\_CALL\_STACK.CONCATENATE\_SUBPROGRAM(SYS.UTL\_CALL\_STACK.SUBPROGRAM(i));

            Error\_Log(i).ERROR\_DEPTH := SYS.UTL\_CALL\_STACK.ERROR\_DEPTH;

            Error\_Log(i).HOST := SYS\_CONTEXT('USERENV','HOST');

            Error\_Log(i).IP\_ADDRESS := SYS\_CONTEXT('USERENV','IP\_ADDRESS');

            Error\_Log(i).SESSIONID := SYS\_CONTEXT('USERENV','SESSIONID');

    END LOOP;

    FORALL i IN 1..v\_depth

    INSERT

    INTO    SQL\_ERROR\_LOG

            (

                    ERROR\_TIME

                  , ERROR\_NUMBER

                  , ERROR\_MSG

                  , UNIT\_LINE

                  , SUBPROGRAM

                  , ERROR\_DEPTH

                  , HOST

                  , IP\_ADDRESS

                  , SESSIONID

            )

            VALUES

            (

                    Error\_Log(i).ERROR\_TIME

                  , Error\_Log(i).ERROR\_NUMBER

                  , Error\_Log(i).ERROR\_MSG

                  , Error\_Log(i).UNIT\_LINE

                  , Error\_Log(i).SUBPROGRAM

                  , Error\_Log(i).ERROR\_DEPTH

                  , Error\_Log(i).HOST

                  , Error\_Log(i).IP\_ADDRESS

                  , Error\_Log(i).SESSIONID

            );

    COMMIT;

END Record_Error;

When i call this procedure with Wrong Parameters values

DECLARE

v_Proc CLOB := to_clob('ss');

v_Param XMLTYPE := XMLTYPE('<a/>');

v_GlobalParam XMLTYPE := xmltype('<b/>');

BEGIN

REL.LOGINCHECK(V_EXECSPNAME => v_Proc,V_EXECQUERYPARAM => v_Param,V_GLOBALPARAM => v_GlobalParam);

--rollback;

END;

It Gives me Dbms Output

But Error Returned (Why BAD DEPTH INDICATOR when value of DYNAMIC_DEPTH = 2))

I am using Oracle 12c

Message was edited by: Sunil K. Still Getting Error;