Devart Forums

Using an OCI connection with Delphi 5 and ODAC version 5 against an Oracle 10 database, we receive these error messages when trying to retrieve a sequence number using an Oracle stored procedure

ORA-20004: syntax error attempting to parse "3 "
ORA-06512: at "SYS.DBMS_DESCRIBE", line 119
ORA-00931: missing identifier
ORA-06512: at line 1

This message appears in several places when the code looks like (one example):

spGetClaimID.Prepare;
spGetClaimID.ExecProc;
NewClaimID := spGetClaimID.ParamByName('New_ID').AsInteger;
spGetClaimID.Close;
spGetClaimID.UnPrepare;

where spGetClaimID is a TOraStoredProcedure with single OUT parameter of NEW_ID. As far as we can tell with the debugger it does the ExecProc with a null value in the parameter and never returns a value from the stored procedure.

The stored procedure script is

-- "Set scan off" turns off substitution variables.
Set scan off;

CREATE OR REPLACE PROCEDURE GET_NEW_CLAIM_ID (NEW_ID OUT INTEGER)
AS
ID_TO_CHECK INTEGER;
CONFLICTING_ROWS INTEGER;
BEGIN
LOOP
SELECT NEW_CLAIM_ID.NEXTVAL INTO ID_TO_CHECK FROM DUAL;
SELECT COUNT INTO CONFLICTING_ROWS FROM CLAIM WHERE CLAIM_ID = ID_TO_CHECK;
EXIT WHEN CONFLICTING_ROWS = 0;
END LOOP;
NEW_ID := ID_TO_CHECK;
END;
/

We are using the OCI created using the Oracle 10 Instant Client installer.

Please check that the StoredProcName property of the TOraStoredProc component is set to GET_NEW_CLAIM_ID.