Re: Dynamic PL/SQL. Retrieving Value

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 11 Oct 1999 16:02:29 +0100
Message-ID: <939654235.12387.0.nnrp-02.9e984b29@news.demon.co.uk>

In your example, you should not put a bind variable into the string sent to dbms_sql.parse.

The critical section of your code should look more like this:

     v_select_string := 'SELECT ' || v_sequence_name || '.NEXTVAL' ||
                        ' FROM DUAL';

     DBMS_SQL.PARSE(cur_select,v_select_string, DBMS_SQL.NATIVE);
     dbms_sql.define_column(CUR_SELECT, 1, V_SEQUENCEID);

     w := DBMS_SQL.EXECUTE(cur_select);

     IF (DBMS_SQL.FETCH_ROWS(CUR_SELECT) != 0) THEN
        dbms_sql.column_value(cur_select,1,v_sequenceid); 
     END IoF;

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

    Keith Jamieson wrote in message <7tsqjq$su3$1_at_ezekiel.eunet.ie>...     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
         v_sequence_name    VARCHAR2(30);
         v_sequenceid       NUMBER;
         v_module_name VARCHAR2(30) := 'UNKNOWN';
         v_module_type VARCHAR2(12) := 'UNKNOWN';
         cur_select     PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
         v_select_string VARCHAR2(500);
         w              PLS_INTEGER;
     BEGIN
         IF p_debug_on
         THEN
             DBMS_OUTPUT.ENABLE;
         ELSE
             DBMS_OUTPUT.DISABLE;
         END IF;
         v_module_name := 'GET_SEQUENCEID';
         v_module_type := 'FUNCTION';
         DBMS_OUTPUT.PUT_LINE('IN '|| v_module_type || ' ' || =
v_module_name );
         v_sequence_name := p_sequence_name;
         v_select_string := 'SELECT ' || v_sequence_name || '.NEXTVAL' =
||


                            ' INTO :v_sequenceid' ||


                            ' FROM DUAL';


         DBMS_SQL.PARSE(cur_select,v_select_string, DBMS_SQL.NATIVE);
         =
DBMS_SQL.BIND_VARIABLE(cur_select,'v_sequenceid',v_sequence_name);
         w := DBMS_SQL.EXECUTE(cur_select);
         DBMS_SQL.CLOSE_CURSOR(cur_select);
         RETURN v_sequenceid;
     EXCEPTION
         WHEN OTHERS
         THEN
             DBMS_SQL.CLOSE_CURSOR(cur_select);
             DBMS_OUTPUT.ENABLE;
             DBMS_OUTPUT.PUT_LINE('An unknown error occurred. Please =
contact Technical Support');
             RETURN NULL;
     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.                Received on Mon Oct 11 1999 - 10:02:29 CDT