Hi All,
I have an oracle stored procedure that accepts a table of records as its input parameter:
TYPE JREC is record
(
CUSTOMERID COMPANYS.COMPANYID%TYPE ,
JOBID JOBS.JOBID%TYPE,
-----
);
TYPE TJOBLIST IS TABLE OF JREC INDEX BY BINARY_INTEGER;
PROCEDURE UPDATEJOBS(JRECS IN TJOBLIST )
IS
BEGIN
FOR i IN JRECS .FIRST .. JRECS .LAST LOOP
-----PROCEDURE BODY---
Please let me know how can I call this procedure from Delphi. I am using RAD Studio 2007
Thanks,
Pradeep
PL/SQL Tables cannot be passed to a procedure. You would need to use a collection object (varray, nested table) instead, and pass it as a TOracleObject instance.
Hi Marco,
Thank you for the reply. Do you have any code samples for the same? I need only the delphi part...
Thanks,
Pradeep
You can check out the TOracleObject chapter in the User's Guide. The TOracleObject.ObjElements paragraph includes an example of populating a collection.
Sorry,
but the example in User's Guide doesn't show how to pass a list to a StoredProcedure's param.
In database I've defined a type:
CREATE OR REPLACE TYPE T_String_Table AS TABLE OF VARCHAR2(100) NOT NULL;
and a StoredProcedure:
CREATE OR REPLACE PROCEDURE MyTest(p_Data IN t_String_Table) AS
BEGIN
-- some SQL statements
END;
In Delphi I've a TOracleQuery with this SQL:
BEGIN
MyTest(:p_data);
END;
Please give me an example, how to pass a StringList to the query. And of which type has my param (p_data) do be declared?
Best regards
Rolf
The variable needs to be declared as an Object type:
YourQuery.DeclareVariable('p_data', otObject);
In Delphi you create a TOracleObject instance for the t_String_Table type and associate it with the :p_data variable:
StringTable := TOracleObject.Create(YourQuery.Session, 'T_STRING_TABLE', '');
YourQuery.SetComplexVariable('p_data', StringTable);
Assign the data through the Elements array property:
StringTable.Elements[0] := 'Hello';
StringTable.Elements[1] := 'World';
Now you can execute the query to call the procedure and pass the data:
YourQuery.Execute;
Thank you Marco, it works ...
Rolf
Hi Marco,
is it also possible to pass a table of records to the procedure?
On database I've created an array table with two fields, at example 'item_number' and 'quantity'.
But if I try to pass my record by ...elements[x] I get an error message because of 'incompatible types: variant and record ...'
Any idea?
You cannot pass a PL/SQL Table of records. For compound array types you need to use a collection as described above.