Delphi, DOA, and Oracle

[thurmana]

I'm executing the following procedure call:

DECLARE
i_rgsc varchar2(4) :='0106';
i_kcrule varchar2(3) :='012';
o_sale_rec pkg_rule_sale.salepool_tab_type;

BEGIN
sp_rule_sale(i_rgsc, i_kcrule, o_sale_rec);
END;

The output parameter, o_sale_rec is declared as a package of type table. How can I view the results of this output parameter. I'm actually trying to show my result in a DBGRID. If anyone knows how to do this or an alternative method of doing this in Delphi with DOA please let me know.

 

[Marco Kalter]

A DBGrid can only display a result set of a SQL select statement, or a cursor result set. You can either modify your procedure to return a result set, or use an other (non-data-aware) grid component.

What is the exact type definition of pkg_rule_sale.salepool_tab_type?

------------------
Marco Kalter
Allround Automations

 

[thurmana]

This line of code (pkg_rule_sale.salepool_tab_type)is defined as Oracle Table Type. It returns a list of records of multiple columns. We figured if Delphi had a way of handling this type that would solve our problem. Returning the result set leaves us with the same problem b/c the type is the same, but it will return only a single column of the table. Can Delphi handle this Oracle Table Type via one of the DOA components?

In addition, is there any printed document for DOA that goes deeper into the functionality of the components? The help file is very limited. Will the Source Code for the DOA components provide additional help as to the capability of each component?

[This message has been edited by thurmana (edited 17 January 2003).]

[This message has been edited by thurmana (edited 17 January 2003).]

[This message has been edited by thurmana (edited 17 January 2003).]

 

[Marco Kalter]

Can you show me the PL/SQL source code of the pkg_rule_sale.salepool_tab_type?

The Direct Oracle Access documentation is reasonably complete. It covers PL/SQL Tables and Collection Objects in detail and with examples. The source code is not really intended as documentation.

------------------
Marco Kalter
Allround Automations

 

[thurmana]

Thanks again for your response... I have sent the package code that you have requested to your email address. I didn't want to post it to the site b/c the content can be sensitive information due to the nature of the business we do.

 

[Marco Kalter]

Thanks. The type declaration you sent me is a PL/SQL Table of a record type. This is unformtunately not supported by Oracle Net. You would need to use a collection object (nested table, varray) instead, which is supported through the TOracleObject class.

------------------
Marco Kalter
Allround Automations

 

[thurmana]

Could you give me an example of what the script for this would be? Thanks again for the assistance you have provided us.

[This message has been edited by thurmana (edited 22 January 2003).]

 

[Marco Kalter]

You can download the CollParam demo here .

This demo calls a procedure that returns a collection of departments, and displays them in a memo.

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 23 January 2003).]

 

[thurmana]

Thanks for the help, we found an alternative method to accomplish our task. Thanks again.