Devart Forums

Posted: **Fri 30 Mar 2007 19:10**

I have the following code which is Borland C++ Builder 5.0. The ODAC version is 5.70.0.30:

void updateXMLTypeColumn(Ora::TOraSession* os, const AnsiString keys[], const AnsiString keyValues[], int keyLength, const AnsiString& xmlColumnName, const AnsiString& tableName, const AnsiString& fileName) {
        TOraQuery* oq = 0;
        TFileStream* fs = 0;
        AnsiString sql = "UPDATE "+tableName+" SET "+xmlColumnName+" = :XVAL ";
        try{
           if(keyLength>0){
              for(int i = 0; i Session = os;
           oq->SQL->Add(sql);
           oq->AutoCommit = true;
           TOraXML* xmlo = oq->Params->FindParam("XVAL")->AsXML;
           xmlo->OCISvcCtx = os->OCISvcCtx;

           xmlo->LoadFromStream(fs);
           oq->Execute();
        }
        __finally{
                if(fs){delete fs;}
                if(oq){
                        if(oq->Active){
                                oq->Close();
                        }
                        delete oq;

                }
        }
}//end updateXMLTypeColumn

The line:

Code: Select all

oq->Execute();

tries to run the query and put the XMLType to the database. It is where the error "OCI function is not linked" is popping up. I thought XMLType columns were supported with NET. I can use BLOBs fine this way, but I can not get XMLType to work at all while using NET. They work fine while using OCI, but because of some deployment issues I have a requirement to use both TOraXML and NET connections and can not use OCI. Any help is appreciated. We would be willing to get funding for the fix if we can have a priority put on this as we have purchased multiple versions of ODAC for different bug fixes over the past couple years.

Thanks,

Wade

Posted: **Sat 31 Mar 2007 14:58**

I have downloaded the latest version 6.0 and it has the same problem. So, currently using NET I can not use XML in the Oracle database with any version of ODAC.

Posted: **Mon 02 Apr 2007 08:16**

ODAC supports XMLTYPE in OCI mode only. When the Net option of TOraSession component is True, you can work with XMLTYPE through CLOB. For example, the following SQL statement can be used to update XMLTYPE column:
Code:
UPDATE Table1 SET Column1 = XMLTYPE(:Val)
Set the DataType property of the VAL parameter to ftOraClob. If you are using ODAC 6, set the TemporaryLobUpdate option of the TOraQuery to True.
In ODAC 5 you should manually call the CreateTemporary and WriteLob methods of TOraLob before calling the Execute method of TOraQuery.

Posted: **Mon 02 Apr 2007 18:09**

plash, have you tested this with ODAC 5? I added the temp clob code and it runs over it without error, but it doesn't update the column. The record is in the database, and if using OCI and the XMLType with the previous code it works fine. The code reads:

Code: Select all

void updateXMLTypeColumn(Ora::TOraSession* os, const AnsiString keys[], const AnsiString keyValues[], int keyLength, const AnsiString& xmlColumnName, const AnsiString& tableName, const AnsiString& fileName) {
        TOraQuery* oq = 0;
        TFileStream* fs = 0;
        AnsiString sql = "UPDATE "+tableName+" SET "+xmlColumnName+" = XMLType(:XVAL) ";
        try{
           if(keyLength>0){
              for(int i = 0; i Session = os;
           oq->SQL->Add(sql);
           oq->AutoCommit = true;
           TOraParam* xp = oq->Params->FindParam("XVAL");
           xp->DataType = ftOraClob;

           //TOraXML* xmlo = oq->Params->FindParam("XVAL")->AsXML;
           //xmlo->OCISvcCtx = os->OCISvcCtx;
           //xmlo->LoadFromStream(fs);

           TOraLob* c = xp->AsOraClob;
           c->OCISvcCtx = os->OCISvcCtx;
           c->CreateTemporary(ltClob);
           c->LoadFromStream(fs);
           c->WriteLob();

           oq->Execute();
        }
        __finally{
                if(fs){delete fs;}
                if(oq){
                        if(oq->Active){
                                oq->Close();
                        }
                        delete oq;

                }
        }
}//end updateXMLTypeColumn

Thanks,

Wade

Posted: **Mon 02 Apr 2007 20:22**

OK. I have figured out the issue with the code you gave me plash. Apparently ODAC fails silently when the parameter is wrapped in XMLType() as it is in this code. This may be the case for all functions wrapping parameters. I was trying to work around the issue I was having with your code. So, I was going to make a new table and dump into a CLOB column then just use a single SQL statement in the database with no CLOB wrapped by an XMLType call and move from the one table into the other. I found I received an error explaining I needed to mark the parameter as input or output. So, I did that and my CLOB code worked. I then thought changing your code to just include that part would work, and whala! it worked. Thanks so much. For anyone else trying to use XMLType columns and ODAC with a NET connection here is the code (my method has extra code to allow passing in the keys etc because it is used to update multiple tables from other code):

Code: Select all

void updateXMLTypeColumn(Ora::TOraSession* os, const AnsiString keys[], const AnsiString keyValues[], int keyLength, const AnsiString& xmlColumnName, const AnsiString& tableName, const AnsiString& fileName) {
        TOraQuery* oq = 0;
        TFileStream* fs = 0;

        AnsiString sql = "UPDATE "+tableName+" SET "+xmlColumnName+" = XMLType(:XVAL) ";
        try{
           if(keyLength>0){
              for(int i = 0; i Session = os;
           oq->SQL->Add(sql);
           oq->AutoCommit = true;

           TOraParam* xp = oq->Params->FindParam("XVAL");
           xp->DataType = ftOraClob;
           xp->ParamType = ptInput;

           TOraLob* c = xp->AsOraClob;
           c->OCISvcCtx = os->OCISvcCtx;
           c->CreateTemporary(ltClob);
           c->Truncate(0);
           c->WriteLob();
           c->LoadFromStream(fs);
           c->WriteLob();

           oq->Execute();
        }
        __finally{
                if(fs){delete fs;}
                if(oq){
                        if(oq->Active){
                                oq->Close();
                        }
                        delete oq;

                }
        }
}//end updateXMLTypeColumn

Remember if you forget to include the

Code: Select all

xp->ParamType = ptInput;

line it will fail with no error raised, but the database simply will not be updated.

Thanks plash,

Wade

Devart Forums

ODAC: "OCI function is not linked" error while using NET connections and attempting to use TOraXML

ODAC: "OCI function is not linked" error while using NET connections and attempting to use TOraXML

RE: ODAC: "OCI function is not linked" error while using NET connections and attempting to use TOraXML