I'd like to put large strings into a CLOB

nico · Post by **nico** » Fri 12 Nov 2004 14:16

I cannot save large character objects out of bcb 5 into a 10g CLOB field. I have a procdeure, which works in C++, but I get an access failure. Does anybody know how it works?
[code]
dbsp_Procedure->Session = CBP_mdl_DBContainer->ses_Application;
dbsp_Procedure->StoredProcName = "cb_insert_text2";
dbsp_Procedure->Params->CreateParam(ftOraClob, "PV_TEXT", ptInput);
dbsp_Procedure->Params->Items[0]->AsOraClob->AllocLob();
dbsp_Procedure->Params->Items[0]->AsOraClob->AsString = edt_Suchparameter->Text;
dbsp_Procedure->Params->Items[0]->AsOraClob->Init();
[/code]

Paul · Post by **Paul** » Mon 15 Nov 2004 15:48

You can pass input parameter to stored procedure using the next code

Code: Select all

{
  TOraLob *TempCLOB;
  TempCLOB = new TOraLob(OraSession->OCISvcCtx);
  TempCLOB->CreateTemporary(ltClob);
  TempCLOB->AsString = "CLOB data";
  TempCLOB->WriteLob();
  dbsp_Procedure->ParamByName("Value")->ParamType = ptInput;
  dbsp_Procedure->ParamByName("Value")->AsOraClob = TempCLOB;
  dbsp_Procedure->Execute();
  delete TempCLOB;
}

Include OraClasses.hpp to your include section also.

Nico · Post by **Nico** » Mon 15 Nov 2004 17:44

Hello Paul.

I've tried your solution and i get an Errror by calling the
TempCLOB->CreateTemporary(ltClob)
Function.
Error: Function is not Supported.
I've included the OraClasses.hpp and compiling works well.

Do you have any idea?

Thanks

nico

Paul · Post by **Paul** » Wed 17 Nov 2004 07:20

Net-option cannot work with temporary LOBs. Try to use the next code to
solve your problem.

1) Change the body of your StoredProc to the next:

Code: Select all

    PROCEDURE INSERT_CLOB(PV_ID varchar2,PV_TEXT out clob) AS
    BEGIN
      INSERT INTO CMP_TEST (ID, TEXT) VALUES (PV_ID, EMPTY_CLOB())
      RETURNING TEXT
      INTO PV_TEXT;
    END;

2) Write the next code in your application:

Code: Select all

       OraStoredProc1->Params->Clear();
       OraStoredProc1->StoredProcName = "insert_clob";
       OraStoredProc1->Prepare();
       OraStoredProc1->Params->Items[0]->AsString = "CLOB Title";
       OraStoredProc1->Params->Items[1]->ParamType = ptInput;
       OraStoredProc1->Params->Items[1]->AsOraClob->AsString = Edit1->Text;
       OraStoredProc1->ExecProc();

nico · Post by **nico** » Wed 17 Nov 2004 09:10

Thanks Paul!

That works well

tarzan · Post by **tarzan** » Tue 13 Jun 2006 15:23

dear Paul

I use Delphi5, your code is in C.
So I haven't got StoredProcName property in Delphi.
Which is the same property in Delphi?

Can you help me?

thanks

Paul · Post by **Paul** » Wed 14 Jun 2006 06:22

Code: Select all

       OraStoredProc1.Params.Clear; 
       OraStoredProc1.StoredProcName := 'insert_clob'; 
       OraStoredProc1.Prepare; 
       OraStoredProc1.Params[0].AsString := 'CLOB Title'; 
       OraStoredProc1.Params[1].ParamType := ptInput; 
       OraStoredProc1.Params[1].AsOraClob.AsString := Edit1.Text; 
       OraStoredProc1.ExecProc;

tarzan · Post by **tarzan** » Wed 14 Jun 2006 08:52

Thanks Paul!

It's work.

You are my master.