Re: HELP!Oracle Store Procedure

I tried the code you gave me. Oracle creates the package fine, but still give me the error:
"ORADBA.table_name must be declared first" Do you know how to declare it?

Thanks a lot.

>In article <3559e951.2670920_at_192.86.155.100>,
> tkyte_at_us.oracle.com wrote:
>
> A copy of this was sent to cosmogong_at_my-dejanews.com
> (if that email address didn't require changing)
> On Wed, 13 May 1998 14:03:30 GMT, you wrote:
>
> >I am quite new to oracle store procedure. I logged in using my login name
> >and password and try to create a simple procedure:
> >CREATE OR REPLACE PROCEDURE test_proc AS
> >BEGIN
> > SELECT * FROM ORADBA.table_name;
> >END;
> >
> >and oracle complained that ORADBA.table_name must be declared first.
> >
> >I don't know why it is so and I also wonder if this will return me a
> >recordset. I read something about cursors. Do I have to use a cursor?
> >
> >Many thanks.
> >
> >Cosmo
> >
> >-----== Posted via Deja News, The Leader in Internet Discussion ==-----
> >http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
> In short, 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;
> }
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri May 15 1998 - 11:49:55 CDT