Re: PL/SQL with OCI Help

In article <3gobh6$r3d_at_welby.ahc.ameritech.com>, aasadi_at_tejas.kdsi.com (Abbas Asadi) says:

I've just finished sorting out this problem too. There are a couple of options. You can either send a straight SQL SELECT or a PL/SQL block to the server. The OCI calls you use to obtain the query results are different depending which way you choose to go.

SQL: "SELECT my_field FROM my_table WHERE my_key = :vKEY" The OCI calls to use here are:
oparse
obndra or obndrv: bind input parameters to :vKEY odefin: to associate a program variable with query results oxec or oexn: to execute query
ofen or ofetch: repeat to fetch results  < or >
oexfet to execute and fetch

This first case is more familiar to a Sybase/DB-Library programmer.

PL/SQL: "BEGIN SELECT my_field INTO :vMY_FIELD FROM my_table \

          WHERE my_key = :vKEY"

NOTE: PL/SQL does NOT allow you to return query results directly - you must associate them with a output parameter (:vMy_FIELD in this example). The OCI calls for the PL/SQL example are: oparse
obndra or obndrv: bind input parameters to :vKEY obndra or obndrv: bind output query results to :vMY_FIELD oexec or oexn (repeat to obtain all query data ) NOTE: You don't need and can't use a fetch in this case.

I hope that this helps. I am working on a similar Sybase to Oracle port and I would be happy to swap war stories with you.

Good luck,
Dennis Harvey
Metropolis Software Inc.
harvey_at_metropolis.com
415/462-2226

>
>I am working on a project (ORACLE7 with PL/SQL extension) which executes
>a stored procedure on the server and return the results (if any) to users.
>This is being done in a C interface. The problem is that I do not know if
>I have to call "ofetch" after "oexec" or not. How do I know if this stored
>procedure (dynamically supplied by user at run time) returns any row or not.
>SYBASE has an easy way (a simple dblib call) indicatiny if the SP returns any
>row(s).
>
>I' ll appreciate any suggestion/help/C example.
>
>aasadi_at_texas.akd
>(214) 301-6142
Received on Thu Feb 09 1995 - 18:01:01 CET