| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored Procedure Question
That's awesome! Thanks. Will it work with ADO Recordsets?
Thomas Kyte wrote:
> A copy of this was sent to "Ian Steward" <isteward_at_mediaone.net>
> (if that email address didn't require changing)
> On Fri, 23 Jul 1999 02:05:26 GMT, you wrote:
>
> >I am just starting to learn Oracle so be gentle. Here is my question:
> >
> >I am trying to create a stored procedure as follows:
> >
> >CREATE PROCEDURE SP_BOMINQ (ASSY CHAR ) AS
> >BEGIN
> >SELECT Assembly,Component,Usage,Revision,UOM,Datein,Dateout,ChangId,Line
> >FROM BOM
> >WHERE Assembly = ASSY
> >Order By Line,Datein;
> >END;
> >
> >This will not compile and gives me the following error:
> >
> >PLS-00428: an INTO clause is expected in this SELECT statement.
> >
> >I cannot see why I would need an INTO clause in a simple Select statement.
> >Can someone advise?
> >
> >- Ian S.
> >
>
> If you are trying to write a stored procedure that returns a result set then....
>
> it'll look like this:
>
> create or replace function sp_ListEmp return types.cursortype
> as
> l_cursor types.cursorType;
> begin
> open l_cursor for select ename, empno from emp order by ename;
> return l_cursor;
> end;
> /
>
> With 7.2 on up 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 an example:
>
> create or replace package types
> as
> type cursorType is ref cursor;
> end;
> /
>
> create or replace function sp_ListEmp return types.cursortype
> as
> l_cursor types.cursorType;
> begin
> open l_cursor for select ename, empno from emp order by ename;
>
> return l_cursor;
> end;
> /
>
>
> REM SQL*Plus commands to use a cursor variable
>
> variable c refcursor
> exec :c := sp_ListEmp
> print c
>
> -----------------------------------------------------
>
> and the Pro*c to use this would look like:
>
> static void process()
> {
> EXEC SQL BEGIN DECLARE SECTION;
> SQL_CURSOR my_cursor;
> VARCHAR ename[40];
> int empno;
> EXEC SQL END DECLARE SECTION;
>
> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
>
> EXEC SQL ALLOCATE :my_cursor;
>
> EXEC SQL EXECUTE BEGIN
> :my_cursor := sp_listEmp;
> END; END-EXEC;
>
> for( ;; )
> {
> EXEC SQL WHENEVER NOTFOUND DO break;
> EXEC SQL FETCH :my_cursor INTO :ename, empno;
>
> printf( "'%.*s', %d\n", ename.len, ename.arr, empno );
> }
> EXEC SQL CLOSE :my_cursor;
> }
>
> And the java to use this could be:
>
> import java.sql.*;
> import java.io.*;
> import oracle.jdbc.driver.*;
>
> class curvar
> {
> public static void main (String args [])
> throws SQLException, ClassNotFoundException
> {
> String driver_class = "oracle.jdbc.driver.OracleDriver";
> String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";
>
> String query = "begin :1 := sp_listEmp; end;";
> Connection conn;
>
> Class.forName(driver_class);
> conn = DriverManager.getConnection(connect_string, "scott", "tiger");
>
> CallableStatement cstmt = conn.prepareCall(query);
> cstmt.registerOutParameter(1,OracleTypes.CURSOR);
> cstmt.execute();
> ResultSet rset = (ResultSet)cstmt.getObject(1);
>
> while (rset.next ())
> System.out.println( rset.getString (1) );
> cstmt.close();
> }
> }
>
> The following is thanks to marktoml_at_hotmail.com (mark tomlinson)..
>
> If you use ODBC here is a working example, but it requires the use of the
> 8.0.5.2.0 or later Oracle ODBC driver, and an 8.0.5 server.
>
> '
> ' 1) Create a form with 1 Text control (Text1) and 1 List Control
> (List1) and
> ' 1 Button (btnExecute).
> ' 2) The only code that you need is a Click method on your button.
> Here is the Code.
> '
> '
> Private Sub btnExecute_Click()
> 'PL/SQL Code
> '===========
> '
> 'CREATE OR REPLACE package reftest as
> ' cursor c1 is select ename from emp;
> ' type empCur is ref cursor return c1%ROWTYPE;
> ' Procedure GetEmpData(en in varchar2,EmpCursor in out empCur);
> 'END;
> '
> '
> 'CREATE OR REPLACE package body reftest as
> ' Procedure GetEmpData
> '(en in varchar2,EmpCursor in out empCur) is
> 'begin
> ' open EmpCursor for select ename from emp where ename LIKE en;
> 'end;
> 'end;
> '
> Dim cn As New rdoConnection
> Dim qd As rdoQuery
> Dim rs As rdoResultset
> Dim cl As rdoColumn
> Static Number As Integer
>
> List1.Clear
> Number = 0
> cn.Connect = "uid=scott; pwd=tiger; DSN=MSLANGORL;"
> 'enable the MS Cursor library
> cn.CursorDriver = rdUseOdbc
> 'Make the connection
> cn.EstablishConnection rdNoDriverPrompt
>
> sSQL = "{call RefTest.GetEmpData(?,?)}"
>
> Set qd = cn.CreateQuery("", sSQL)
>
> qd.rdoParameters(0).Type = rdTypeVARCHAR
> qd(0).Direction = rdParamInputOutput
> qd(0).Value = Text1.Text
> qd.rdoParameters(1).Type = rdTypeVARCHAR
>
> 'Dynamic or Keyset is meaningless here
> Set rs = qd.OpenResultset(rdOpenStatic)
>
> Do
> Debug.Print
> Debug.Print
>
> Do Until rs.EOF
> For Each cl In rs.rdoColumns
> If IsNull(cl.Value) Then
> List1.AddItem "(null)"
> ' Debug.Print " "; cl.Name; "NULL"; Error trap for
> null fields
> Else
> List1.AddItem cl.Value
> ' Debug.Print " "; cl.Name; " "; cl.Value;
> End If
> Next
> Debug.Print
> rs.MoveNext
> Loop
> Loop While rs.MoreResults
> cn.Close
>
> End Sub
>
>
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
--
"Funkify your life" - The Funky Meters
 |
 |