Solved

return a recordset with a stored procedure through ODBC

Posted on 2001-07-11
10
614 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
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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now