Dynamic PL/SQL. Retrieving Value

From: Keith Jamieson <jamiesonk_at_phoenix.ie>
Date: Mon, 11 Oct 1999 15:00:51 +0100
Message-ID: <7tsqjq$su3$1@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 - 09:00:51 CDT