ORA-01036 on insert record problem

[Dragoljub]

Hi,
I make web application on a platform with next tools: Delphi 7 with Intraweb 10, DOA Ver. 4.1 for Delphi and Oracle11g. I am working a few days with DOA and I am very, very pleased. I have only one problem and this is "ORA-01036 on insert problem". So, I am trying to insert a record into the table "Cases" with a next structure:

desc Cases
Name Null Type
------------- -------- -----------------
CASEID NOT NULL NUMBER
CASENBR NOT NULL VARCHAR2(6 CHAR)
EVIDNBR VARCHAR2(20 CHAR)
REGID NUMBER
CONTMETTYPEID NOT NULL NUMBER
CASETYPEID NOT NULL NUMBER
BUSPARTID NOT NULL NUMBER
OWNERID NUMBER
REPLID NUMBER
OUREPLID NUMBER
CASENAME NOT NULL VARCHAR2(70 CHAR)
CASEDESC CLOB
CREATEDATE NOT NULL TIMESTAMP(6)
FORWARDDATE TIMESTAMP(6)
LASTCHGDATE TIMESTAMP(6)
PRIORITY NOT NULL NUMBER
CASESTATUS NOT NULL NUMBER
ARCHIVED NOT NULL NUMBER
LOCKEDD NOT NULL NUMBER
LASTREPLIER NUMBER
OUANSWDATE TIMESTAMP(6)
OUANSWDESC CLOB
CLANSWDATE TIMESTAMP(6)
CLANSWDESC CLOB

I made storage procedure with next input parameters:

PROCEDURE INSERT_NEW_CASE (
I_CASENBR CASES.CASENBR%TYPE,
I_EVIDNBR CASES.EVIDNBR%TYPE,
I_REGID REGISTARS.REGID%TYPE,
I_CONTMETTYPEID CONTMETTYPES.CONTMETTYPEID%TYPE,
I_CASETYPEID CASETYPES.CASETYPEID%TYPE,
I_BUSPARTID BUSPARTNERS.BUSPARTID%TYPE,
I_CASENAME CASES.CASENAME%TYPE,
I_CASEDESC VARCHAR2,
I_PRIORITY CASES.PRIORITY%TYPE,
I_USERNAME USERS.USERNAME%TYPE)

and "insert" part of procedure body is:

SELECT CASE_GEN.NextVal INTO V_CaseID FROM Dual;
INSERT INTO Cases(CaseID,CaseNbr,EvidNbr,RegID,ContMetTypeID,CaseTypeID,
BusPartID,CaseName,CaseDesc,CreateDate,Priority, CaseStatus, Archived, Lockedd)
VALUES
(V_CaseID,I_CaseNbr,I_EvidNbr,I_RegID,I_ContMetTypeID,
I_CaseTypeID,I_BusPartID,I_CaseName,EMPTY_CLOB(),CURRENT_DATE,I_Priority, 0, 0, 0);

I use "spInsNewCase: TOracleQuery" component and within it I declared:

SQL =
BEGIN
INSERT_NEW_CASE
I_CASENBR, :I_EVIDNBR, :I_REGID,
:I_CONTMETTYPEID, :I_CASETYPEID,
:I_BUSPARTID, :I_CASENAME, :I_CASEDESC,
:I_PRIORITY, :I_USERNAME);
END;

and Variables =
spInsNewCase.SetVariable('I_CASENBR', ); -- String
spInsNewCase.SetVariable('I_EVIDNBR', ); -- String
spInsNewCase.SetVariable('I_REGID', ); -- Integer
spInsNewCase.SetVariable('I_CONTMETTYPEID', ); -- Integer
spInsNewCase.SetVariable('I_CASETYPEID', ); -- Integer
spInsNewCase.SetVariable('I_BUSPARTID', ); -- Integer
spInsNewCase.SetVariable('I_CASENAME', ); -- String
spInsNewCase.SetVariable('I_CASEDESC', ); -- String
spInsNewCase.SetVariable('I_PRIORITY', ); -- Integer
spInsNewCase.SetVariable('I_USERNAME', ); -- String

Delphi part of code is:
try
with UserSession, spInsNewCase do begin
SetVariable('I_CASENBR', UserSession.CurrCaseNbr);
SetVariable('I_EVIDNBR', EvidNbrEdit.Text);
SetVariable('I_REGID', UserSession.Paramm1);
SetVariable('I_CONTMETTYPEID', UserSession.Paramm2);
SetVariable('I_CASETYPEID', UserSession.Paramm3);
SetVariable('I_BUSPARTID', UserSession.Paramm4);
SetVariable('I_CASENAME', CaseNameEdit.Text);
SetVariable('I_CASEDESC', CaseDescMemo.Text);
SetVariable('I_PRIORITY', UserSession.Paramm5);
SetVariable('I_USERNAME', UserSession.UserName);
Execute;
end
except
on E:EOracleError ......
end;

When I press "PostBtn" on Intraweb form I then get the following message:

"ORA-01036: illegal variable name/number"

and program is crashed, but RECORD IS INSERTED !!!
In Oracle SQL Developer everithing is OK, for example i enter next command:
BEGIN
INSERT_NEW_CASE
('A1B2C3', '1.11', 1, 4, 1, 11,
'aaaaaaaaa', 'bbbbbbb', 1, 'Rosko');
END;
and Oracle insert data into database.
So, where is the problem?

 

[Marco Kalter]

If the record is inserted then it seems that the insert statement itself executes correctly. Can you include the OracleMonitor unit and use our Oracle Monitor utility to verify which statement leads to the ORA-01036 error?