Updating CLOB column

[diesel]

Hi

I have a CLOB column and I need to use C++ builder to pass a very long string to a stored proc.The stored proc takes in the long string and should store it to the clob column.
It works without raising any errors but only stores about 4000 characters of my 30000 or so.
I am not allowed to use direct access from C++ using a stream and TBlobField->LoadFromStream, even though this works okay, due to access violations to the table.

C++ Builder Code
String Clobfunction = **Large amount of text from memo **
//stores the data to table
m_pStoredProcedure->CallProcedure
("sp_UpdateTaskFunction", OPENARRAY(Variant,
(taskName, Clobfunction ,description,
taskkey)));

// to access the data from the table
String& function =
m_pTaskDataSet->Fields->FieldByName("FUNCTION")->AsString;

PL/SQL stored proc code
CREATE OR REPLACE PROCEDURE sp_UpdateTaskFunction
(vTaskName IN TASKS.TASKNAME%TYPE,
vFunction IN TASKS.FUNCTION%TYPE,
vDescription IN TASKS.DESCRIPTION%TYPE,
vTaskkey IN TASKS.TASKKEY%TYPE)
IS
v_taskkey NUMBER;
BEGIN

SELECT COUNT(*) INTO v_taskkey FROM TASKS WHERE TASKKEY = vTaskkey;
IF v_taskkey > 0 THEN
UPDATE TASKS SET
-- "function" is the clob column to update
FUNCTION = vFunction,
TASKNAME = vTaskName,
DESCRIPTION = vDescription
WHERE TASKKEY = vTaskkey;

COMMIT;
END IF;
END;
/

Do I need to change my stored proc and how?
Or do I need to change the way I pass the large string to the stored proc?
Thanks in advance.

 

[Marco Kalter]

The stored procedure is okay. You should change the C++Builder code and use a TOracleQuery with a PL/SQL Block to call the procedure. For the function parameter you should declare a otCLOB variable, and assign a temporary LOB (TLOBLocator.CreateTemporary). This way you can pass the CLOB data to the procedure.

 

[diesel]

Thanks for the reply
I am still not sure how to do this.
My table is TASKS, clob column is "FUNCTION"
My Oracle Session is m_GlobalOracleSession.

int otClob;
TLOBLocator *LOB = new TLOBLocator(m_GlobalOracleSession, otCLOB, true);

I am still not sure how one could use this LOB value to insert data into my TASKS table through the stored procedure.
How do I assign the string value I have from the memo, into the clob column using the solution you have suggested?
My apologies if it is obvious but I am new to Oracle.

 

[Marco Kalter]

After creating the temporary TLOBLocator instance you can simply assign the string value to its AsString property. Now the CLOB holds the string value, and can be passed to the procedure call.