Regarding the CLOB datatype of Oracle

[loveojha2]

Hi All,

I am facing some problem with Oracle's CLOB datatype.

For a COLB datatype column in the Oracle when I specify Varchar(2000) in server job it gives me
error ORA-24909: call in progress. Current operation cancelled

The description of the Error (from a site)

ORA-24909: call in progress. Current operation cancelled
Cause: The OCI call was invoked when another call on the connection was in progress.
Action: Check if the OCI call is supported when the call is in progress under special conditions; for example, if it is being used by a signal handler.

If I use LongVarchar(2000) in place of Varchar(2000), Job runs fine.
I suppose the datatype within the Datastage is just for the purpose of documentation, it treats everything as characters only (except validation)(correct me if I am wrong).

So the question is what changes when it becomes longvarchar instead varchar, more over can I achieve it through varchar also?

The length of the test data that I am using for CLOB column is not more than few characters.

[ray.wurlod]

That message has nothing whatsoever to do with the fact that you're casting a CLOB as a VarChar. Read the error message again. The Oracle server is busy at the time. Something preceding hasn't finished when the next call is made through the OCI. Have your Oracle DBA help you to trace what requests are being received by Oracle.

[loveojha2]

That message has nothing whatsoever to do with the fact that you're casting a CLOB as a VarChar. Read the error message again. The Oracle server is busy at the time. Something preceding hasn't finished when the next call is made through the OCI. Have your Oracle DBA help you to trace what requests are being received by Oracle.

True Ray, I have understood the error.
But I am facing this problem only when I use Varchar(2000), this problem
disappears when I use Longvarchar(2000). Why?

[loveojha2]

Moreover, I have tried the same thing on more than two machines (two different Oracle databases).

[chulett]

But I am facing this problem only when I use Varchar(2000), this problem disappears when I use Longvarchar(2000). Why?

Because Longvarchar is the proper datatype to use with a CLOB. Use it and stop worrying about it.

If you really want to use a Varchar then you'll need to CAST the CLOB to a VARCHAR2 or wrap it in a TO_CHAR function - but only do that if you are certain that the contents of it will never exceed the maximum size of a Varchar.

[loveojha2]

Thank you very much Chulett.