TOraclePackage: Passing an array as one of the parameters???

[TomH]

Im trying to call a function defined in Oracle and I have been doing this fine with other packages, but this one has three of its parameters defined as arrays:

Most of the parameters are defined like this:

var1 IN OUT VARCHAR2,

but the ones I am having trouble with are defined like this:

var2 IN OUT GLBL_DEFS.NUM_ARRAY,

I found the GLBL_DEFS and the NUM_ARRAY is defined like this:

TYPE NUM_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

What I need to know is how to pass an array for parameters like this...

In looking at the other posts, I suspect that the TOracle Package isn't capable of this and that I will have to use TOracleQuery and write a small PL/SQL for this - but I don't know how to do that!

Any help would be very greatly appreciated!
Thank you

 

[Marco Kalter]

The TOraclePackage component does indeed have a simplified interface and can only pass scalar parameters.

For program units with complex parameters, you can use the Package Wizard. It will create a class that encapsulates your package's functionality. You can also explicitly create a TOracleQuery with a PL/SQL Block for this call.

 

[TomH]

Marco,

I tried the wizard, but for some reason, the package doesn't show up in the selection list...

Can you please point me to somewhere where I can find an example of a PL/SQL block to do this sort of thing?

 

[Marco Kalter]

It will be much easier to use the Package Wizard. I can imagine only 2 reasons why your package does not show up in the list:

1. You have not checked the "Show all packages" option.

2. You are connected through an Oracle user that does not have execute privileges on this package.

Could this be the case?

 

[TomH]

It isn't a rights issue, but I just figured out that what I need to call is in the procedures secion o fthe oracle schema mgr. It is NOT a package...

Is there a way to put this procedure into a package? Or is there an easier way to call a procedure?

I know I sound like an idiot, but I really need help on this. I have called small procedures that I have written myself (as if they were functions) in SQL, is it possible to do the same with this procedure? I got a book on PL/SQL in the hope that I can properly define a small PL/SQL block to call the procedure after setting up my arguments to match those of the procedure - I've just never done it before...

Any helpful hints would be greatly appreciated..
~Tom

 

[Marco Kalter]

If you create a little package with a procedure that calls this procedure, you will get the code generated for you.

Alternatively, you can invoke the Direct Oracle Access help and search for the "PL/SQL Table" keyword. This section will explain exactly how you can call a program unit with a PL/SQL Table parameter. It includes a complete example.

 

[TomH]

Actually - I nailed it yesterday. The book I got on PL/SQL was very helpful for structure. I just created an anonymous block declared my variables, assigned values to them and called the procedure passing the variables as arguments.

Thanks to the fact that the TOracleQuery can run PL/SQL, I'm styl'n now...

Thanks!
~Tom