Re: sequence name as a variable

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sun, 28 Mar 1999 12:13:43 +0200
Message-ID: <7dkvcn$dr2$1@weber.a2000.nl>

Tod Meinke wrote

> How can I substitute the sequence name into a variable?

The ampersand (&) is an SQL*Plus feature, not a PL/SQL feature. In fact, your approach is invalid in SQL*Plus as well, as & is really a variable that SQL*Plus will prompt you for and insert wherever you used it. It does not refer to a variable you declared in a function header.

Anyway, you need dynamic SQL to do this in PL/SQL. Not too hard though. For example (not tested, but adjusted from something I used):

  function myNextVal( pSequenceName) return number   is
    iCursor integer;
    iResult integer;
    iRowCount integer;
  begin
    iCursor := dbms_sql.open_cursor;
    dbms_sql.parse

        ( iCursor,
        , 'select ' || pSequenceName || '.nextval from dual'
        , dbms_sql.native
        );

    dbms_sql.define_column( iCursor, 1, iResult);     iRowCount := dbms_sql.execute_and_fetch( iCursor, true);     dbms_sql.column_value( iCursor, 1, iResult);     dbms_sql.close_cursor( iCursor);
    return iResult;
  exception
    when others then

      if dbms_sql.is_open( iCursor) then
        dbms_sql.close_cursor( iCursor);
      end if;
      raise_application_error
        ( -20001
        , 'myNextVal: error for ' || pSequenceName
        , true
        );

  end myNextVal;

Arjan. Received on Sun Mar 28 1999 - 04:13:43 CST