Re: Oracle/ODBC/Procedure/Cursor -- how ?

>

I was successfully able to execute the stored procedure using OracleTypes.CURSOR provided with Oracle JDBC Driver. But that would make my code driver dependent. I have asked about this to Oracle Support. I am attaching the mail I sent to Oracle in regarding this. If you can help me, please.

Also this is an example procedure with only one select statement. I may have more complex PL/SQL statements which can be done effectively from inside a stored procedure. But ultimatey I want to return a REF CURSOR from the procedure.

--
shiv
vihs_at_rocketmail.com

==========================================================================
Hi,

I want to execute a stored procedure in Oracle7 using JDBC and ODBC.
I use Oracle7 on Solaris, Oracle JDBC Driver (OCI) in my Java code and
Oracle ODBC Driver for C code.

Details follow ..
-----------------------------------------------------------------------

I have a package like this :

CREATE OR REPLACE PACKAGE MyPackage AS
  TYPE MyRec IS RECORD
  (
    City VARCHAR2(20);
    Sales NUMBER(8,2);
  );

  TYPE MyCurTyp IS REF CURSOR RETURN MyRec;
END;

I have a stand-alone procedure like this :

CREATE OR REPLACE PROCEDURE MyProcedure
( state IN VARCHAR2, MyCur IN OUT MyPackage.MyCurTyp ) AS
BEGIN
  OPEN MyCur FOR
    SELECT City_Name, SUM(SALES) FROM City, Sales
      WHERE City.State_id=state
END;
-----------------------------------------------------------------------

How do I execute this procedure using ODBC/JDBC ??

JDBC DatabaseMetaData/ ODBC SQLProcedureColumns() on this stored
procedure gives the following result :

COLUMN #1
----------------------------------------------------------------------
Column Name    : STATE
Table Owner    : MYSCHEMA
Data Type      : 12
Data Type Name : VARCHAR2
ColumnSize     : 0
Scale          : 0
Column Type    : java.sql.DatabaseMetaData.procedureColumnIn

COLUMN #2
----------------------------------------------------------------------
Column Name    : MYCUR
Table Owner    : MYSCHEMA
Data Type      : 1111
Data Type Name : REF CURSOR
ColumnSize     : 0
Scale          : 0
Column Type    : java.sql.DatabaseMetaData.procedureColumnReturn

COLUMN #3
----------------------------------------------------------------------
Column Name    : CITY
Table Owner    : MYSCHEMA
Data Type      : 12
Data Type Name : VARCHAR2
ColumnSize     : 20
Scale          : 0
Column Type    : java.sql.DatabaseMetaData.procedureColumnReturn

COLUMN #4
----------------------------------------------------------------------
Column Name    : SALES
Table Owner    : MYSCHEMA
Data Type      : 3
Data Type Name : NUMBER
ColumnSize     : 22
Scale          : 0
Column Type    : java.sql.DatabaseMetaData.procedureColumnReturn
----------------------------------------------------------------------

To execute the procedure, I succeeded doing the following :

 1: CallableStatement cstmt =
    connection.prepareCall( "{call CITYBREAKUP(?,?)}" );
 2: cstmt.setString( 1, "LA" );
 3: cstmt.registerOutParameter( 2, OracleTypes.CURSOR );
 4: cstmt.execute();
 5: ResultSet rs = (ResultSet)cstmt.getObject(2);
 6: while ( rs.next() )
 7: {
 8:   System.out.println( rs.getString("CITY") +
 9:                       "\t" +
10:                       rs.getString("SALES") );
11: }

Now I want to write a generic method to execute a procedure given the
procedure name and parameters.
For this, I will get Procedure Column Information into a vector and
pass it to executeProcedure() method :

Vector procColumns = getProcColumnInfo();
executeProcedure( procName, procColumns );

Inside executeProcedure, I should dynamically form the sql string with
appropriate number of "?" marks as there are parameters. ( In this
case I would have formed "{call CITYBREAKUP(?,?)}" ). Then bind/set
parameters and execute the statement.

In this case, there were only two parameters, CITY and MYCUR, whereas
getProcColumnInfo returns me information about FOUR columns. (
Although the last two are MyRec entires ).

• How will I distinguish dynamically in my code about which is a CURSOR
  parameter and how may COLUMNS it returns ? In this case I would want
  to know that second parameter is a cursor and it returns a result
  set having two columns.

• Now suppose I had another parameter, call it "REGION" after MYCUR in
  MYPROCEDURE, I would have got information about FIVE columns. In
  that case how will I know which is a CURSOR record parameter ( like
  CITY/SALES ) and which is a normal parameter ( like REGION )

• Also in the code example I have given above to execute the procdure,
  I could not use java.sql.Types.OTHER in registerOutParameter (Line
  No.3). That mean that my program will fail to work with other JDBC
  Drivers supporting Oracle. I can't do driver specific programming. I
  just want the work done and my code should work with all JDBC Drivers.
  ( Although we can publish that our product works best with Oracle
  JDBC Driver and recommend users to buy your JDBC Drivers, we can't take
  things for granted ). What is your suggestion/solution for this ?

• Please also tell me how to do the same thing in ODBC, because I am
  also writing C code to do the same thing.

Thanx in advance.

--
shiv

==========================================================================


As I have said here, I want to write a generic executeProcedure() method given
the procedure name and parameters. How will I find how many columns does a REF
CURSOR return ??

Ex :

procedure ( cur IN OUT MyCur );
-- where MyCur is REF CURSOR return my%ROWTYPE and
-- where table 'my' has say one col.

In this case cur would return me one col. If table 'my' had two column, cur
would return two columns.

Suppose the procedure was liek this

procedure ( cur IN OUT MyCur, p2 IN OUT NUMBER );
-- where MyCur is REF CURSOR return my%ROWTYPE and
-- where table 'my' has say one col.
SQLProcedureColumns() would return me info about three columns, one for 'CUR',
one for the MY table column, and one for 'P2'. I would not know how many
columns 'CUR' is going to return. In this case, would I consider P2 as one of
the columns returned by CUR or a separate column.

Note that I would not know how the procedure was created. All I know is
information returned by SQLProcedureColumns() in ODBC and DatabaseMetaData in
JDBC.

--
shiv






-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/   Now offering spam-free web-based newsreading