Using collections and packages.

[Lars Hammer]

We are using packages in our oracle database. In the packages we have functions for inserting, updating and selecting collections of a type defined in the package.

E.g.
We have a table called t_parentblade.
We have a package named apiparentblade based on t_parentblade. In the package the type parentbladetype is defined as:
TYPE parentbladeType IS TABLE OF t_parentblade%ROWTYPE

The package has an insertrow(data apiparentblade.parentbladetype) function, that inserts a collection of parentbladetypes into the t_parentblade table.

When I use the package wizard to translate the PL/SQL code into Delphi (pascal) code, the parameter for insertrow is converted to:

function Insertrow(var Data: TOracleObject): Double;

We have similar Selectrow() and Updaterow() functions. Now how do I use this code to obtain the OracleObject. We tried it (we are using C++Builder) like this:

TOracleObject *Object = NULL;
AnsiString Where, OrderBy;// Two empty strings
Apiparentblade1->Selectrow(Where,OrderBy,Object);

This doesn't seem to work (object can't be NIL), so we tried to create an object of the correct type by doing the following:

TOracleObject *Object = new TOracleObject(OracleSession1,"apiparentblade.parentbladetype","");

But this also fails at runtime, because the apiparentblade.parentbladetype is not found.

What do I do?

 

[Marco Kalter]

Packaged types cannot be used. You must create the type through a "create or replace type" statement.

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

 

[Lars Hammer]

Originally posted by mkalter:
Packaged types cannot be used. You must create the type through a "create or replace type" statement.

OK, I thought that we could only use "normal" types, when creating objects, but isn't it somehow possible to use the Insertrow(), Updaterow() and Selectrow() functions described in the first mail, using TOracleObject?

 

[Marco Kalter]

As long as you use an object type created through a "create or replace type" statement, you should be able to use your Insertrow(), Updaterow() and Selectrow() functions. They should also the same object type as parameter though.

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