|
Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Oct 2010
Posts: 5
Member
|
OP
Member
Joined: Oct 2010
Posts: 5 |
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
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Aug 1999
Posts: 22,214
Member
|
Member
Joined: Aug 1999
Posts: 22,214 |
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.
Marco Kalter Allround Automations
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Oct 2010
Posts: 5
Member
|
OP
Member
Joined: Oct 2010
Posts: 5 |
Hi Marco, Thank you for the reply. Do you have any code samples for the same? I need only the delphi part... Thanks, Pradeep
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Aug 1999
Posts: 22,214
Member
|
Member
Joined: Aug 1999
Posts: 22,214 |
You can check out the TOracleObject chapter in the User's Guide. The TOracleObject.ObjElements paragraph includes an example of populating a collection.
Marco Kalter Allround Automations
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Oct 2010
Posts: 5
Member
|
OP
Member
Joined: Oct 2010
Posts: 5 |
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Jul 2014
Posts: 19
Member
|
Member
Joined: Jul 2014
Posts: 19 |
Last edited by icegood; 06/04/15 09:02 AM.
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Mar 2000
Posts: 8 Herdecke, NRW, Germany
Member
|
Member
Joined: Mar 2000
Posts: 8 Herdecke, NRW, Germany |
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
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Aug 1999
Posts: 22,214
Member
|
Member
Joined: Aug 1999
Posts: 22,214 |
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;
Marco Kalter Allround Automations
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Mar 2000
Posts: 8 Herdecke, NRW, Germany
Member
|
Member
Joined: Mar 2000
Posts: 8 Herdecke, NRW, Germany |
Thank you Marco, it works ... Rolf
|
|
|
Re: Delphi: How to pass a table of records as a parameter to an Oracle Stored procedure
|
Joined: Mar 2000
Posts: 8 Herdecke, NRW, Germany
Member
|
Member
Joined: Mar 2000
Posts: 8 Herdecke, NRW, Germany |
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?
|
|
|
|
|
|