Oracle/ODBC/Procedure/Cursor -- how ?

From: ShivKumar <vihs_at_rocketmail.com>
Date: 1998/03/25
Message-ID: <6fb7ja$ld0$1@nnrp2.dejanews.com>#1/1

Hi all,

I want to execute a stored procedure in Oracle7.

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

---

Schema         : MYSCHEMA
Procedure Name : MYPROCEDURE
Column Name    : STATE
Column Type    : SQL_PARAM_INPUT
Data Type      : 12
Data Type Name : VARCHAR2
Precision      : 2000
Nullable       : 1


----------------------------------------------

Column #2

---

Schema         : MYSCHEMA
Procedure Name : MYPROCEDURE
Column Name    : MYCUR
Column Type    : SQL_PARAM_INPUT_OUTPUT
Data Type      : -3
Data Type Name : RAW
Precision      : 255
Nullable       : 1


----------------------------------------------

Column #3

---

Schema         : MYSCHEMA
Procedure Name : MYPROCEDURE
Column Name    : RETURN_VALUE
Column Type    : SQL_RETURN_VALUE
Data Type      : -3
Data Type Name : RAW
Precision      : 255
Nullable       : 1


----------------------------------------------

Column #4

---

Schema         : MYSCHEMA
Procedure Name : MYPROCEDURE
Column Name    : CITY
Column Type    : SQL_PARAM_INPUT_OUTPUT
Data Type      : 12
Data Type Name : VARCHAR2
Precision      : 20
Nullable       : 1


----------------------------------------------

Column #5

---

Schema         : MYSCHEMA
Procedure Name : MYPROCEDURE
Column Name    : SALES
Column Type    : SQL_PARAM_INPUT_OUTPUT
Data Type      : 2
Data Type Name : NUMBER
Precision      : 8
Scale          : 2
Nullable       : 1


----------------------------------------------

Good that it returns the MyRec definition also.

• But how do I get the value from this record ? • What type of variable should I bind for parameter#2 in MYPROCEDURE ?

• If somebody knows how to do this in ODBC, I can figure out with JDBC or vice-versa.

Please help.

Thanx in advance.

--

shiv
vihs_at_rocketmail.com

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 25 1998 - 00:00:00 CST