Stored Procedure to Return data in Oracle 1

[ericg]

In MSSQL Server you could use a Select statement in a stored procedure to return many rows of data. In Oracle, it seems that you need PL/SQL to open a cursor, return the rows in a cursor and then print the cursor using SQL/Plus or a similar tool. I need to use an ODBC connection to run a stored procedure to return many rows of data via a stored procedure. This was simple in SQL Server - so how do I do this in Oracle. It seems that I am missing something, as this should be very straighforward. My front end is written to talk to SQL Server stored procedures to return data, and I want to do a minimal amount of changes to get this front end to talk to Oracle. Many of the selects are too complex to run directly as select statements without procedures.

 

[MikeJones]

Hmm, I'm not sure *exactly* what you mean, but an oracle stored function may be able to help you, the thing is this can only return 1 thing where as a stored procedure can return many.

You say though that the stored procedure would return many rows of data which kind of suggests that you want to run a Query, which is neither a function or an SP, so I'm not sure there either. on a few occasions I think we may have a had a similar problem in these occasions we have used a stored function to return a seperated string list, the calling code however must know what to expect as it will just get many strings back. 1 string for each row.

HTH,

Mike.

 

[Fredzo]

Since you didn't specify the language your front-end is written in it's a little difficult to give an exact answer. I write in VB and to do this with ADO as the middleware declare a variable as a ADO Recordset and then use an ADO Command object to send the SQL to Oracle and return an ADO Recordset. If you need more specifics I can send a code snippet in VB.

 

[ericg]

The front end is a Macintosh/Windows Database, 4th Dimension, which connects to Oracle via ODBC. I'm using the 4th Dimension ODBC extensions. A code example in VB would be helpful, as would a general description of what has to be done in the Oracle Procedure/Package to get the data there.

 

[braxton]

I think you need to return a reference cursor or an array type from oracle. I use JDBC, not ODBC, but the fundamentals are similar.

1) create an array type in oracle:
create or replace TYPE char_varray as VARRAY(1000) OF CHAR(30);

2) prepare the statement, set the output parameter, and execute it. this example passes an array to myPROC, but it's similar to get it back.

String sql="{call myPROC(?)}"; 
	OracleCallableStatement stmt =(OracleCallableStatement) conn.prepareCall(sql);
	ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("CHAR_VARRAY",conn);
	ARRAY newArray = new ARRAY(arrayDesc, conn, inputArr);
	stmt.setARRAY(1, newArray); 
	stmt.execute();