Re: How get the current value from a sequence?

"Ubiquitous" <weberm_at_polaris.net> wrote in message news:cog0a7$9cj$1_at_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;
>

need to be dba to read that view

whats wrong with select seq_name.nextval fom dual? Received on Mon Nov 29 2004 - 14:43:39 CST