Re: Can PL/SQL return a table-like result set to GUI clients?
Date: 1996/08/26
Message-ID: <3221ad61.1843741_at_dcsun4>#1/1
PL/SQL can return pl/sql tables (like arrays) to clients. Additionally, pl/sql can open a cursor that a 3gl fetches from as such (the following example, for 7.2 and up, demonstrates how to open a cursor in pl/sql that depends on the inputs):
create or replace package empCurType
as
cursor c1 is select ename, mgr, dept.deptno, dname, loc from emp, dept;
type empCur is ref cursor return c1%rowtype;
procedure open_cursor( p_cursor in out empCur, p_deptno in number );
end;
/
show errors
create or replace package body empCurType as
procedure open_cursor( p_cursor in out empcur, p_deptno in number )
is
begin
if ( p_deptno in ( 10, 20 ) ) then -- open local query
open p_cursor for
select ename, mgr, dept.deptno, dname, loc
from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = p_deptno;
else
open p_cursor for
select ename, mgr, dept.deptno, dname, loc
from emp_at_aria.world, dept_at_aria.world
where emp.deptno = dept.deptno
and dept.deptno = p_deptno;
end if;
end open_cursor;
end empCurType;
/
show errors
And then the pro*c would look like 'normal' pro*c code except that the EXEC SQL OPEN Cursor statement would now be a pl/sql call as follows:
static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
typedef char asciiz; EXEC SQL TYPE asciiz IS STRING(100); SQL_CURSOR my_cursor; asciiz ename[100]; int mgr; short mgr_i; int deptno; asciiz dname[100]; asciiz loc[100]; int i;
EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL ALLOCATE :my_cursor;
EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 10 ); end;
END-EXEC;
for( ;; )
{
EXEC SQL WHENEVER NOTFOUND DO BREAK;
EXEC SQL FETCH :my_cursor
INTO :ename, :mgr:mgr_i, :deptno, :dname, :loc;
printf( "%s, %d, %d, %s, %s\n",
ename, mgr_i?-1:mgr, deptno, dname, loc );
}
EXEC SQL CLOSE :my_cursor;
}
On Sun, 25 Aug 96 16:26:34 GMT, dmausner_at_brauntech.com (Dave Mausner) wrote:
>In article <4voss6$q3s_at_crl.crl.com>, sbixby_at_crl.com (Steven W. Bixby) wrote:
>>
>>I've been messing around with PL/SQL for awhile now, doing mostly "fire
>>and forget" calls that do not have results.
>>
>>One thing I haven't seen mention of is whether an Oracle stored procedure
>>can return a result set that looks like a table to GUI front-ends.
>
>No, it cannot do that.
>
>>
>>IE, when using Delphi or another client environment, can I execute a
>>PL/SQL stored procedure and treat the results as a table, to be able to
>>browse (presumably read-only...)?
>
>No, that is not possible with pl/sql thru oracle 7.3.x.
>
>--
>Dave Mausner, Consulting Manager, Braun Technology Group, Chicago.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database