Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How get the current value from a sequence?

Re: How get the current value from a sequence?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 29 Nov 2004 17:03:40 -0800
Message-ID: <1101776523.814468@yasure>


Dave wrote:

> "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? 

One big thing ... that being you just took another number off the top of the sequence.

What's wrong with using a different data dictionary view, I think, would have been a better question.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon Nov 29 2004 - 19:03:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US