Re: Dynamic PL/SQL. Retrieving Value

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 11 Oct 1999 10:23:22 -0400
Message-ID: <h=IBODWtThkG2AZwM0=GVxi3rqmy@4ax.com>

A copy of this was sent to "Keith Jamieson" <jamiesonk_at_phoenix.ie> (if that email address didn't require changing) On Mon, 11 Oct 1999 15:00:51 +0100, you wrote:

>I have written the following PL/SQL function:
>
>CREATE OR REPLACE
>FUNCTION get_sequenceid(p_sequence_name IN VARCHAR2,
> p_debug_on IN BOOLEAN := FALSE)
> RETURN PLS_INTEGER
> IS

[snip]
> BEGIN

[snip]
> v_select_string := 'SELECT ' || v_sequence_name || '.NEXTVAL' ||
> ' INTO :v_sequenceid' ||
> ' FROM DUAL';

[snip]

> END;
>/
>and when I execute it I get the following error
>
> *
>ERROR at line 4:
>ORA-06550: line 4, column 28:
>PLS-00357: Table,View Or Sequence reference 'MEMBER_SEQ' not allowed in this context
>ORA-06550: line 4, column 1:
>PL/SQL: Statement ignored
>
>What I am trying to do is to return the sequence id to the calling program. I am assuming that there is a coding bug.
>
>

you don't use "INTO :bindvariable" in dbms_sql, you just "select" and dbms_sql.column_value the value out later. Here is an example:

tkyte_at_8.0> create or replace function get_seq( p_seqname in varchar2 ) return

                                                                       number
  2  as
  3      l_theCursor        integer default dbms_sql.open_cursor;
  4      l_columnValue    number default NULL;
  5      l_status        integer;
  6  begin
  7         dbms_sql.parse(  l_theCursor,
  8                      'select ' || p_seqname || '.nextval from dual',
  9                       dbms_sql.native );
 10  
 10      dbms_sql.define_column( l_theCursor, 1, l_columnValue );
 11      l_status := dbms_sql.execute(l_theCursor);
 12      if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
 13      then
 14             dbms_sql.column_value( l_theCursor, 1, l_columnValue );
 15      end if;
 16      dbms_sql.close_cursor( l_theCursor );
 17      return l_columnValue;

 18 end get_seq;
 19 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create sequence my_seq;

Sequence created.

tkyte_at_8.0>
tkyte_at_8.0> exec dbms_output.put_line( get_seq( 'my_seq' ) ) 1

PL/SQL procedure successfully completed.

>

--
See http://osi.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 Received on Mon Oct 11 1999 - 09:23:22 CDT