.NEXTVAL

hi guys

I have a procedure which is inserting rows in
PROCEDURE insert_person
    ( input_insert             person_type   --person_type is a  TYPE
    , po_success     OUT NOCOPY CHAR
    )
IS

c_program_name CONSTANT VARCHAR2(64) := c_package_nm||'.insert_person';

bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

BEGIN

    po_success := 'Y';

    FORALL i IN input_insert.FIRST..input_insert.LAST  
    SAVE EXCEPTIONS
        INSERT INTO person_table    
            ( id
            , name            
            , lst_updt_timestamp            
            )
        VALUES
            ( person_map.NEXTVAL                    
            , input_insert(i).person_name
            , SYSTIMESTAMP
           
            );


EXCEPTION
  WHEN bulk_errors THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT
    LOOP
        person_exception_error_log
            ( pi_error_type     => 'FATAL'
            , pi_program_name   => c_program_name
            , pi_program_type   => c_program_type
            , pi_error_code     => SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
            , pi_error_msg      => SQL%BULK_EXCEPTIONS(i).ERROR_CODE
            );
    END LOOP;

    po_success := 'N';

  WHEN OTHERS THEN
    person_exception_error_log  
        ( pi_error_type     => 'FATAL'
        , pi_program_name   => c_program_name
        , pi_program_type   => c_program_type
        , pi_error_code     => SQLCODE
        , pi_error_msg      => SQLERRM
        );

    po_success := 'N';
   
END insert_person;      

In above code i >>person_map.NEXTVAL      i want to know where is person_map  defined, any idea how can i do that?

thanks
royjaydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>> i want to know where is person_map  defined

It is an Oracle Sequence:

select * from user_sequences where sequence_name='PERSON_MAP';

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6015.htm
0
royjaydAuthor Commented:
>>select * from user_sequences where sequence_name='PERSON_MAP';


it says no rows selected.  Is 'PERSON_MAP' defined anywhere or can it be any name?

thanks
0
slightwv (䄆 Netminder) Commented:
It has to be that name.  Maybe it is created in a different schema.

If not locally owned, try ALL_SEQUENCES or of you have permission DBA_SEQUENCES.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

awking00Commented:
You probably need to select from all_sequences or dab_sequences.
0
awking00Commented:
Oops, typo - dba_sequences
0
awking00Commented:
Sorry, slightwv. Your response wasn't on my screen when I posted. I'll leave this alone. :-)
0
slightwv (䄆 Netminder) Commented:
>>Oops, typo - dba_sequences

I believe in V10 of the site, you can now edit your own comments.
0
slightwv (䄆 Netminder) Commented:
>>Your response wasn't on my screen when I posted. I'll leave this alone. :-)

No problem, it happens all the time.  More ofter with the new version with the scrolling.
0
awking00Commented:
>>I believe in V10 of the site, you can now edit your own comments.<<
That can sure come in handy. What's the procedure to do that?
0
slightwv (䄆 Netminder) Commented:
>>That can sure come in handy. What's the procedure to do that?

Do you not see "Edit Comment" under your post?  It might be only if no one else posts after it, you can edit.  The next time you make a new post, see if it is there.
0
awking00Commented:
>>It might be only if no one else posts after it, you can edit.<<
That was probably the case.
>> The next time you make a new post, see if it is there.<<
Will check it out. Thanks.

It was there and this is the edit. Thanks again.
0
royjaydAuthor Commented:
when i run the query
select * from user_sequences where sequence_name='PERSON_MAP';

it works now, i was using lower case which is why it wasnt working.

From the above link::
>>>Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers

Can a create a sequence which generates unique alphanumeric values something like

A001
A002
A003

Is that possible ?
0
slightwv (䄆 Netminder) Commented:
>>it works now, i was using lower case which is why it wasnt working.

Oracle automatically makes objects UPPER case unless you force them to non-upper case (which you should never do).

>>Can a create a sequence which generates unique alphanumeric values something like

No but you can concatinate the character and format the number:
'A' || to_char(person_map.NEXTVAL,'009')

Just make sure your to_char format has enough values for the maximum size of the sequence.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
You could also use -
'A'||lpad(to_char(person_map.nextval),3,'0')
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.