Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure

[Vinu]

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

 

[Marco Kalter]

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.

 

[Vinu]

Hi Marco,
Thank you for the reply. Do you have any code samples for the same? I need only the delphi part...
Thanks,
Pradeep

 

[Marco Kalter]

You can check out the TOracleObject chapter in the User's Guide. The TOracleObject.ObjElements paragraph includes an example of populating a collection.

 

[Vinu]

Thank you Marco..

 

[icegood]

Hi Marco! What about associative array?
http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/05_colls.htm

 

[Schlueter]

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
MyTestp_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

 

[Marco Kalter]

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 _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;

 

[Schlueter]

Thank you Marco, it works ...
Rolf

 

[Schlueter]

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?

 

[Marco Kalter]

You cannot pass a PL/SQL Table of records. For compound array types you need to use a collection as described above.