Re: Stored function from ODBC = Syntax error or access violation

Jamie Pearson wrote:

> This call works fine when using the Microsoft ODBC for Oracle or the
> DataDirect Technologies (Merant/Intersolv) Oracle ODBC drivers. I see in
> Oracle documentation that procedures should be wrapped with begin/end
> pairs.

You do need a BEGIN END wrapper when dealing with PL/SQL at "native" SQL level. To explain in Delphi/BDE/ODBC terms (which will also be applicable for VB and C/C++).

The TQuery class allows me to call native SQL, SQL passthrough, or whatever else it's called. I give it a SQL string. It takes that, and send it through like that, as is, unchanged, to the database engine.

When doing this, the SQL _must_ be syntactically correct and within the SQL standards of the database that is receiving the SQL. Or else it will fail. When using this to call an Oracle stored proc, the BEGIN END wrappers will be required, as that is what Oracle expects.

The TStoredProc class allows me to call a stored pocedure. I simply give the name of the stored proc and supply the values for the input and output variables. Then the object is activated/executed. No BEGIN END wrappers.

In the background, the BDE/ODBC/whatever driver is called with a *different* API call, than the normal "send a SQL statement" call used in the TQuery class.

The API (in BDE, ODBC and OCI, dbLib and others) has a specific "call a stored proc" API call. Using this, will result in the driver building the applicable SQL statement that needs to be send to the database. In the case of Oracle, the driver will wrap that statement into a BEGIN END.

> Don't see any example for a stored fuction.

A stored function and stored proc are very similar, with the exception that some functions can be used in SQL SELECT statements. You can for example do this:
SELECT
  my_function( 'foobar' )
FROM dual

The cursor that is created in the client as a result, will contain the returned value of that function.

In PL/SQL, you can use the function like this: DECLARE
  x number(6);
BEGIN
  x := my_function( 'foobar' );
  // do some interesting stuff with x
END;
> Seems begin/end pair would
> defeat the purpose of using ODBC by making the application backend
> dependent.

Only if you use the db abstraction and interface layer (ODBC for example), incorrectly.

IMO, the days of making an application database independant, is past. The exceptions are generic report writers and reporting tools. Applications are a dime a dozen. The business spend a lot more on their server and database platforms. To provide a ROI on that, you need applications that make best possible use of those server platforms... and not a generic application that can also support some other platform and database that will never see the light of day in that environment.

Software projects fail ito costs and time, applications fail ito meeting business requirements, because the database server and back-end are treated as a Black Box. And one of the prime reasons for treating that as a black box is this silly and very stupid idea that the application should be developed as database and server independant.

--
Billy