Oracle OCI determine if field is NCLOB or CLOB

Question

I am looking for a simple way to determine if a field in Oracle is an NCLOB.

When I perform an Attribute Get on the field, I always get back 112 (SQLT_CLOB), regardless of whether or not the field is a CLOB or an NCLOB. Here is a code snippet of what I am talking about.

Associated Query: 'select DATA from NCLOBTest2., DATA is an NCLOB in this case.

            ...
for I := 1 to CMaxFields do 
begin
  if oci.ParamGet( StmtHandle, OCI_HTYPE_STMT, FErrHandle, @APDesc, I ) <> OCI_SUCCESS then
    Break;
//At this Point APDesc refers to the field **DATA**.

while CheckError( oci.AttrGet( APDesc, OCI_DTYPE_PARAM, @APName, @ANameSz, OCI_ATTR_NAME, FErrHandle ) ) = OCI_STILL_EXECUTING do;

//APName and ANameSz, come back as 'DATA' and 8, respectively.  8 not 4, because it's a Unicode Environment.

while CheckError( oci.AttrGet( APDesc, OCI_DTYPE_PARAM, @ADbtype, nil, OCI_ATTR_DATA_TYPE, FErrHandle ) ) = OCI_STILL_EXECUTING do;
  DbType := ADbType;  //HERE IS THE PROBLEM, ADBTYPE IS ALWAYS SQLT_CLOB.

Is there no such thing SQLT_NCLOB? I need to know if the field is a CLOB or an NCLOB, so that I can properly read the N/CLOB later on. I know I can call LobCharSetForm and LobCharSetID if I have an actual LOBLocator instance, but this would require many extra steps.

Thank you!

EDIT: Here is a summary of the steps I take trying to get LobCharSetID when defining fields. Similar code works just find when binding parameters. Maybe someone can tell me what step(s) I am missing.

Summary of coding steps:
        CheckError( oci.HandleAlloc( EnvHandle, @FStmtHandle, OCI_HTYPE_STMT, 0, nil ) );      //sse, allocating a statement handle
        CheckError( oci.StmtPrepare( StmtHandle, FErrHandle, PChar( ServerSQL ), Length( ServerSQL )*sizeof(char), OCI_NTV_SYNTAX, OCI_DEFAULT ) );
        while CheckError( oci.AttrGet( StmtHandle, OCI_HTYPE_STMT, @FStmtType, nil, OCI_ATTR_STMT_TYPE, FErrHandle ) ) = OCI_STILL_EXECUTING do;
        //EnvHandle and StmtHandle must be allocated properly to get this far.  FStmtType comes back correctly as OCI_STMT_SELECT (1).  
        I := 32768;
        oci.AttrSet( StmtHandle, OCI_HTYPE_STMT, @I, 0, OCI_ATTR_PREFETCH_MEMORY, FErrHandle );
        ACode := oci.StmtExecute( FSvcHandle, StmtHandle, FErrHandle, Nrows, Noffs, nil, nil, OCI_DEFAULT );
        oci.ParamGet( StmtHandle, OCI_HTYPE_STMT, FErrHandle, @APDesc, I ) <> OCI_SUCCESS
        //APDesc contains an OCIParam
        CheckError (oci.DescriptorAlloc (TOracle8Connection(aSQLParam.owner.owner.Connection).EnvHandle , @LobLocator, OCI_DTYPE_LOB, 0, nil));
        ACode := oci.DefineByPos( StmtHandle, @OCIDefine( FBind ), FErrHandle, Pos, @ALobLocator, MaxInt, SQLT_CHR, nil, nil, nil, OCI_DYNAMIC_FETCH );  
        CheckError(oci.LobLocatorIsInit(Connection.EnvHandle, FErrHandle, ALOBLocator, @isLobInitialized));  //RETURNS FALSE... UH OH!
        CheckError(oci.LobCharSetId(Connection.EnvHandle, FErrHandle, ALOBLocator, @lobCHAR_CODE));   //CRASH... OCI_INVALID_HANDLE

The last two lines are the problematic ones. What do I have to do to properly poll the LobLocator's Charset. Please note that the LobLocator's charset is not the same as the define handle's charset. The define handle's charset could be OCI_UTF16ID, while the Clob's charset might be OCI_WE8MSWIN1252. The same holds for the SQLCS value, it also doesn't have to match.

According to a google search (so it could be wrong) NCLOB is just a special type of CLOB the OCILobCharSetForm() can be used to determine the type. http://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci16m14.htm#454948 — Robert Love, Jul 07 '15 at 21:52
@RobertLove, thank you for your thoughts. As I mention above, I am trying to avoid the call to OCILobCharSetForm. :) That said, I have tried using it, it works fine for binding the sql parameters, but I get an invalid handle error when I try to read fields. Unfortunately, the oci error message doesn't specify which of my handles are invalid... oci can be irritating like that. — sse, Jul 08 '15 at 06:16
@sse Why are you "trying to avoid the call"? ;) The call will be in-process, with no roundtrip to the server I guess, so it won't cost you anything in practice. Anyway, you will have to call oci.AttrGet(OCI_ATTR_CHARSET_FORM/OCI_ATTR_CHARSET_ID) to retrieve the CLOB encoding, so it won't change anything. — Arnaud Bouchez, Jul 08 '15 at 06:24
@ArnaudBouchez, why do you believe the call will be in process? Doesn't the LOBLocator need to be opened to read the Lob Charsets? That said, even if there is a roundtrip, I may have no choice but to call OCILobCharSetForm/ID to get the info. I cannot seem to get passed the OCI_INVALID_HANDLE when calling OCILobCharsetForm/ID. I will update the question with what I am doing. Would you be able to provide a few lines of code as an answer? Thank you. — sse, Jul 08 '15 at 18:57
@ArnaudBouchez, I edited the question with an additional code fragment. I am not sure why I get OCI_INVALID_HANDLE when polling LobLocator information. — sse, Jul 10 '15 at 23:01
@ArnaudBouchez, ps, I don't think you can call "oci.AttrGet(OCI_ATTR_CHARSET_FORM/OCI_ATTR_CHARSET_ID) to retrieve the CLOB encoding". this would work if it were a define handle (OCI_HTYPE_DEFINE), but it's not, and there is no OCI_HTYPE_LOB to perform the get. I think that's why Oracle invented ociLobCharSetID+ociLobCharSetForm. Thank you for your suggestion though. :) — sse, Jul 10 '15 at 23:08

Oracle OCI determine if field is NCLOB or CLOB

0 Answers0