questions

We are developing software for public transport companies using Oracle V7 and MS-Windows.

We have several questions concerning OCI and stored procedures/functions, which the german Oracle-hotline said it could not answer. Having a very narrow schedule and a rather nervous management, we need the answers urgently.

1. What we want to do

We want to develop an API giving access to the database. The application programmer should not care about underlying tables and relations. He must not use embedded SQL nor functions of the OCI. All SQL-statements accessing the database are located in stored procedures or functions.

To access the database an application needs to know only the name and the in-parameters of the stored procedure doing the job. The output of the operation is returned to the application in a given container.

Regarding to Oracle Germany, such dynamic handling of stored procedures is impossible using embedded SQL. Therefore we use OCI.

2. Why we can not do it completely (yet)

The manuals do not provide information about the following questions. The german Oracle hotline was not able to answer them either.

ODESSP: The function does not always return complete information about the out-parameters of a stored procedure (datatype size,precision,scale, radix)

We need more information about a stored procedure. E.g. usage of internal cursors, parameters used to control array sizes or to determine completion of operations.

OBNDRA: How does the binding of program variables to record parameters of a s.p. work?

Same for program variables and return values of stored functions?

OEXEC: It looks like you can call every stored procedure or function with OEXEC() (even several calls for queries with internal cursor).

How do you determine whether to call OEXEC() once or more if you have only the name and the information provided by ODESSP()?

When do you use OEXFET(), OEXN(), OFEN(), OFETCH() or OFLNG() for stored procedures and how?

INTERNAL CURSORS: Is there a way to deal with the internal cursor of a s.p. (e.g. cancel a fetch or reset the cursor)?

OVERLOADING: How does Oracle treat overloaded procedures? How to handle them with OCI? If you have two overloaded s.p. having the same parameter list but different parameter names accessing different database fields both compile successfully and are valid. On execution an error is returned
(ORA-ERROR 6550 / PLSQL-ERROR 307).
MISCELLANEOUS: How do you retrieve information about a single field in the database with OCI?

We were told by Oracle, that we have to use the data dictionary for further information. Which views are available?

Thanks a lot

Ulrich Dorn
Michael Gienke
Torsten Schlemm
(Software Development)

bertram+partner EDV-Consulting GmbH

Braunschweig, Germany

(I6211303 _at_ ws.rz.tu-bs.de)
Received on Thu Aug 18 1994 - 16:02:41 CEST