May cause (ora-01006: binding variable does not exist) problems

There are many times that all kinds of reasons can cause this problem. Now we will talk about a relatively small reason.

V_QUERY_SQL  =  ‘SELECT  COUNT(DISTINCT (B.BATCH_ID))  FROM T_DD_RES T
        JOIN T_DD_REQ Q
        ON T.ID_DD_REQ = Q.ID
        JOIN T_DD_BATCH B
        ON T.BATCH_ID = B.BATCH_ID  WHERE T.INSERT_TIME >= TRUNC(PI_DATE)
         AND T.INSERT_TIME < TRUNC(PI_DATE) + 1
         AND Q.REQUEST_DATE >= TRUNC(PI_DATE)
         AND Q.REQUEST_DATE < TRUNC(PI_DATE) + 1 
         AND B.CDATE >= TRUNC(PI_DATE)
         AND B.CDATE < TRUNC(PI_DATE) + 1’

OPEN PO_EXPCUR FOR V_QUERY_SQL USING …

The SQL in the stored procedure, such as the above code, throws the exception that ora-01006: bound variable does not exist when calling. When viewing the SQL statement, make sure that all variables exist. But why is this exception thrown?

Finally, we find out the reason, because the SQL statement returns an integer value, but here we give this value to the cursor, so we throw this exception. Change the cursor related statements to the following SQL, and everything will be OK.

EXECUTE IMMEDIATE V_QUERY_SQL INTO PO_AMOUNT USING…

 


Read More: