How get the current value from a sequence?

From: Ubiquitous <weberm_at_polaris.net>
Date: Mon, 29 Nov 2004 20:20:23 +0000 (UTC)
Message-ID: <cog0a7$9cj$1@news.utelfla.com>

I have a sequence which is used to generate receipt numbers with a max_value (the numbers are loaded to a mainframe database) and am am attempting to write a function which will determine if attempting to issue receipt numbers to the current batch will excede this value.

I am able to run a SELECT statement without any problem:

SELECT last_number, max_value
  FROM dba_sequences
 WHERE sequence_name = 'RCPTNUM_SEQ';

However, when I use this in a FUNCTION and compile it, I get a "PLS-00201: identifier 'SYS.DBA_SEQUENCES' must be declared" error mesage. Why is this happening, and does anyone know what I can do to fix this? Thanks in advance!

I'm including the coding that doesn't compile below:

CREATE OR REPLACE FUNCTION receipt_sequence_is_too_high

   RETURN BOOLEAN
IS

   return_value        BOOLEAN := FALSE ;
   v_last_number       NUMBER  := 0;
   v_max_value         NUMBER  := 0;
   v_receipt_count     NUMBER  := 0;

   v_max_receipt_num NUMBER;
BEGIN
   SELECT last_number, max_value

     INTO v_last_number, v_max_value
     FROM dba_sequences

    WHERE sequence_name = 'RCPTNUM_SEQ';

   SELECT CLOSE.total_num_file_lines

     INTO v_receipt_count
     FROM closeout_detail CLOSE, credit_card_records_received ccrr
    WHERE CLOSE.doi_application_id = ccrr.doi_application_id
      AND ccrr.appl_source = 'L'
      AND ccrr.doi_rct_date IS NULL
      AND ccrr.doi_receipt_number IS NULL
      AND CLOSE.status_flag IN('E', 'R');

   DBMS_OUTPUT.put_line('Last value in sequence: ' || v_last_value);

   DBMS_OUTPUT.put_line('Max value in sequence: ' || v_max_value);    DBMS_OUTPUT.put_line('Number of receipts: ' || v_receipt_count);

   IF v_last_number + v_receipt_count > v_last_value THEN

      DBMS_OUTPUT.put_line('ERROR: Receipt number overflow!');
      return_value := TRUE ;

   END IF;    RETURN return_value;
END receipt_sequence_is_too_high; Received on Mon Nov 29 2004 - 14:20:23 CST