Re: Urgent : OCI Gurus : Please Help . . .

tanksali <tanksali_at_ix.netcom.com> wrote:

> The main problems that I am facing are with respect to the dynamic binding
> of the variables.
> Also obndra does not allow bind by position.

> Any help or ideas on how I can make my program fire the stored procedure
> given at run time with the arguments provided will be enormously
> appreciated.

Can only tell with OCI 8.
Do you need to find out about number of arguments? Or will you always get all necessary arguments, so you do not need to care about number, just count those you get? In case you need to assign your call parameters in the appropriate sequence to the actual parameters of some stored procedure and maybe substitute empty parameters, you need to know about the procedure header. I have not tried, but the only way to get this information seems to be some OCIDescribe or OCIStmtExecute with OCI_DESCRIBE_ONLY option (at least this works for tables, I do not know if ot works for procedures. This info should be kept in some cache, it seems.).

For table operations you do not need to care about datatypes. It should be ok, to bind any variables with SQLT_STR datatype. The OCI will do datatype conversion for you. This only applies for basic datatypes like integer or varchar2. In my OCI8 manual, there is a table what datatypes may be converted into which other.
(It is by the way not so easy to interpret OCIDescribe info in the right way to get the SQLT_... datatypes out of it.)

So, in case you get the procedure arguments in the right ordering and number, you may do the binding by name (:name1, ..., :namen) or by position (no clue about OCI7) and use Datatype SQLT_STR. Much easier, you might just literally copy the values you receive into the statement string, but you need to mess around with quotes or so. You do not need to bind anything, in this case.

By the way: what about return parameters? Are there any? Cursors? Simple OUT Parameters?

In the end, you need to form some SQL Statement string like begin;\n<procname>(arg1,arg2,arg3,...);\nend; where argx is replaced by the arg value or by a symbolic name :namex and a related bind operation.

Then prepare statement, do the binds (even for OUT parameters) and execute it.

Again, this is OCI8, hope this is applicable for you. Sorry otherwise. Regards, Philipp Received on Wed Jul 07 1999 - 03:53:32 CDT