Solved

return a recordset with a stored procedure through ODBC

Posted on 2001-07-11
10
634 Views
Last Modified: 2013-12-12
i'm trying to return a recordset from a stored procedure through an odbc connection.  it works fine with a standard sql statement but fails when i call a stored proc.

the stored procedure in question executes a normal table query and performs some conditional logic on the returned values and some additional var values are included in the final resultset that the proc returns.   i've tried using dbms_output as well as out variables to return the values but neither seems to work through the odbc.

i get an sql error, [ORA-00900].
0
Comment
Question by:Daniel Stanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 3

Expert Comment

by:mathavra
ID: 6275267
Try the following:

With SQL*Plus:
--------------
Use an anonymous PL/SQL block if you plan on calling Oracle stored PL/SQL objects via ODBC.  The following example is an acceptable format:
 
  "begin myproc(...); end;"  
 

Without SQL*Plus:
-----------------

As a workaround, if SQL*Plus is not available but you have the ODBC Driver installed, you can also use this with "ODBC Test" to execute anonymous
PLSQL-Blocks.


Explanation:
============
 
You cannot simply use "execute myproc(...);" because this is a SQL*Plus specific-routine which, in effect, wraps the procedure in an anonymous PL/SQL block as described above.
0
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 6275483
that still doesn't give me a resultset back from odbc, do you think the problem is with the package that i'm using for output? "DBMS_OUTPUT".
0
 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6276024
yep, that must be it.
The manual says "Package DBMS_OUTPUT enables you to display output from PL/SQL blocks and subprograms(..) You display the information by calling the procedure get_line or by setting SERVEROUTPUT ON in SQL*Plus or Enterprise Manager"

I don't suppose you redirect your output to SQL*PLUS ??
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Daniel Stanley
ID: 6278293
there is no redirect to sql*plus, i'm trying to pull the stored procedure recordset into access using pass through technology and odbc connect.
0
 
LVL 7

Author Comment

by:Daniel Stanley
ID: 6335885
my question is more directed at what package i should be using in my stored procedure to return a resultset through an odbc call.
0
 
LVL 4

Expert Comment

by:fva
ID: 6406435
No package is required. You should create a cursor inside the stored proc and pass it as an OUT parameter to the ODBC caller. From there on you use that as a reference to an already opened result-set. I don't know much on ODBC, so I cannot give you exact details or sample code, but at Stored Proc side that's the Oracle way.

F.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 75 total points
ID: 6499034
In order to allow Oracle Stored Procedures to return a set of data you will have to define a REF
CURSOR or PL/SQL Table as OUT parameter.
If you decide to use ref cursor: you have to previously declare the REF CURSOR within a package, then:

create or replace package result_pkg is
-- Type declarations
type genericCursor is ref cursor;
end resultset_pkg;


once you compiled it, you will be able to return REF CURSORS in your procedures:

create or replace procedure test ( regout OUT resul_pkg.genericCursor)
is
begin
open regout for select * from user_objects;
end;


Try this one:
This is the best method i.e. todefine a REF CURSOR. You can open the cursor as normal process. Also,
the ref cursor can be reuse for other SQL statement. You can define multiple procedure with difference
SQL statement but use the some REF Cursor.


Create testing Package:
========================

CREATE OR REPLACE PACKAGE PkgTest AS
TYPE empcur IS REF CURSOR;
PROCEDURE GetEmpRecords(p_x in number,p_y in number, p_z in number, p_cursor OUT empcur);
END PkgTest;
/
CREATE OR REPLACE PACKAGE BODY PkgTest AS
PROCEDURE GetEmpRecords(p_x in number,p_y in number, p_z in number, p_cursor OUT empcur) IS
BEGIN
-- define and open output cursor
OPEN p_cursor FOR
select nodeid from nodetable where nodeparent in (p_x)
and nodechild in (p_y) and nodetype = p_z
END GetEmpRecords;
END PkgTest;
/

Use SQLPlus to test:
====================
set serveroutput on
declare
A PkgTest.empcur;
v_nodeid nodetable.nodeid%TYPE;
begin
PkgTest.GetEmpRecords(1,2,3, A);
loop
fetch A into v_nodeid;
exit when A%NOTFOUND;
dbms_output.put_line(v_nodeid);
end loop;
close A;
end;
/
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7038108
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7053136
Recommended disposition:

    Accept schwertner's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7053548
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question