Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored Proc Question
On 3 Oct 1997 13:25:07 GMT, "Billy Verreynne" <vslabs_at_onwe.co.za> wrote:
[snip]
>
>I think you're missing the point Tim. In SQL-Server a stored proc can
>contain a single SELECT statement, e.g.
>CREATE PROCEDURE xyz AS
>BEGIN
> SELECT * FROM foobar
>END
>
>This stored proc behaves exactly like a normal SQL SELECT statement in
>SQL-Server. The advantages are that the SELECTs (query logic) for an
>application reside on the server, and that instead of sending a long SELECT
>statement across the network, only the stored proc name is transmitted by
>the client.
>
>This is not possible with Oracle, without using something like Oracle OLE
>objects.
>
With 7.2 and 7.3 of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on.
Here is a 7.2 / 7.3 example in SQL*Plus and PRO*C
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 );
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 empCurType;
/
show errors
REM --------------- SQL*Plus using RefCursor to display results ------
variable C refcursor
exec empCurType.open_cursor( :C, 10 )
print C
REM ----------- PRO*C Getting the cursor ------------------REM
REM typedef char asciiz; REM EXEC SQL TYPE asciiz IS STRING(100); REM SQL_CURSOR my_cursor; REM asciiz ename[40]; REM int mgr; REM short mgr_i; REM int deptno; REM asciiz dname[50]; REM asciiz loc[50]; REM int i;
REM EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 10 ); end; REM END-EXEC; REM REM for( ;; ) REM { REM EXEC SQL WHENEVER NOTFOUND DO BREAK; REM EXEC SQL FETCH :my_cursor REM INTO :ename, :mgr:mgr_i, :deptno, :dname, :loc; REM REM printf( "%s, %d, %d, %s, %s\n", REM ename, mgr_i?-1:mgr, deptno, dname, loc ); REM } REM EXEC SQL CLOSE :my_cursor;
Also, The beta version of Microsoft's latest Oracle ODBC driver (v2.0) is available at: http://www.microsoft.com/msdownload/rds/rdsdownload.htm. It is part of the RDS Server 1.5 Beta download. When you download it you have the option to install any of the included components (ADO, OLE DB, the Oracle ODBC driver, etc...).
Some of the features of the new driver are:
.....
- It can return a resultset from a stored procedure.
.....
>regards,
>Billy
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities