CODEWITHSUNDEEP
loggingplsqldynamic-sql
Roshan
Roshan

Reputation: 3592

Logging in Dynamic SQL

I have a Stored proc using dynamic sql. Is there any way to log the compilation errors occurring when SQLSTR1 compiles while calling the proc from Java. Or maybe log the variables involved.

SQLSTR1:= ' SELECT NVL(SUM(TOTAL_B),0) CURRENT_VAL,' || COL_NAME|| ' 
    FROM view1 WHERE(R_DATE  IS NULL OR R_DATE >:EOQ) 
    AND (LOCATION IN  (' || COMMA_FUNC(LOCATIO) || ') OR :LOCATIO = ''NULL'')
     GROUP BY ' || COL_NAME;  

    OPEN CURRENT_CURSOR FOR SQLSTR1 USING EOQ, LOCATIO;

The person managing the procs is not available and I need to do some debugging. Please Help.

Upvotes: 1

Views: 105

Answers (1)

Toru
Toru

Reputation: 905

At least you can display the values of bind variables by viewing v$sql_bind_capture: "V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. Each row in the view contains information for one bind variable defined in a cursor." See http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3044.htm#REFRN30310

Simple example:

DECLARE
    TYPE ct IS REF CURSOR;
    c ct;
    q VARCHAR2(255);
BEGIN
    q := 'SELECT * FROM dual WHERE dummy = :EOQ OR dummy = :LOCATIO';
    OPEN c FOR q USING 'X', 'Y';
    CLOSE c;
END;
/

SELECT
     sql_id
    ,name
    ,value_string
    ,TO_CHAR(last_captured, 'DD.MM.YYYY HH24:MI:SS')
FROM v$sql_bind_capture
WHERE NAME IN (':EOQ', ':LOCATIO');
--
a2vmmmcumuv5v   :EOQ       X   14.11.2013 22:09:05
a2vmmmcumuv5v   :LOCATIO   Y   14.11.2013 22:09:05
                         ^^^^^
                       as expected

Upvotes: 1

Related Questions