Binding of CLOB params in a procedure

[Karthik R]

Hi All,

I am trying to bind a OCI_Lob object to a CLOB parameter in a procedure. But
NULL is getting assigned to this CLOB parameter. I have added the db scripts
and the sample code written to perform this task. Kindly suggest some solution
to this problem.

Thanks,
Karthik

---

Environment: Windows XP SP2, Visual Studio 2005 SDK

DB Scripts:
Table:
create table test3 (clobcol clob);

Procedure:
CREATE OR REPLACE PROCEDURE INSERTCLOB_PRC( CLOBCOL IN CLOB, RESULT OUT NUMBER
)
IS
BEGIN
RESULT := 0;
IF CLOBCOL <> NULL THEN
INSERT INTO TEST3 VALUES (CLOBCOL);
RESULT := 1;
ELSE
INSERT INTO TEST3 VALUES ('ABCD');
RESULT := 2;

SOMETHING HERE

END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.Put_Line(SQLERRM);
RESULT := -1;
END;
/

---

C++ Code:
OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);

cn = OCI_ConnectionCreate("DB", "USR", "PWD", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);

OCI_Lob *lob;
int result = 0;
lob = OCI_LobCreate(cn, OCI_CLOB);
unsigned int iCLength = 5;
unsigned int iBLength = 0;

BOOL b = FALSE;
b = OCI_LobWrite2(lob, "Today", &iCLength, &iBLength);
OCI_Prepare(st,"BEGIN INSERTCLOB_PRC(:clobdata, :result); END;");

b = OCI_BindLob(st,":c", lob);
b = OCI_BindInt(st,":result", &result);
b = OCI_Execute(st);

b = OCI_Commit(cn);

OCI_LobFree(lob);
OCI_Cleanup();

---

[Karthik R]

I am using the latest version of OCILIB (3.9.0)

[Karthik R]

The below line in the C++ code section is incorrect.
b = OCI_BindLob(st,":c", lob); //Incorrect

I am currently using this code and still having the same problem.
b = OCI_BindLob(st,":clobdata", lob);

[Vincent Rogier]

hi,

the libray must have encountered an error at some point. Pass an error handler
to OCI_Initialize() and you'll get the error code and message

[Vincent Rogier]

HI,

your problem is in your PL/SQL code !!!!

In PL/SQL or SQL, when checking if a variable is NULL you do "IS NULL" or "IS
NOT NULL" and not "= NULL" or "<> NULL"

Update your PL/SQL code and it will work fine !

vincent

c

[Karthik R]

I modified the procedure and suggested and it is working fine. Thanks a lot.