Solved

.NEXTVAL

Posted on 2012-04-09
14
444 Views
Last Modified: 2012-04-30
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
0
Comment
Question by:royjayd
  • 6
  • 6
  • 2
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823748
>> 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
 

Author Comment

by:royjayd
ID: 37823781
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823791
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
 
LVL 32

Expert Comment

by:awking00
ID: 37823795
You probably need to select from all_sequences or dab_sequences.
0
 
LVL 32

Expert Comment

by:awking00
ID: 37823799
Oops, typo - dba_sequences
0
 
LVL 32

Expert Comment

by:awking00
ID: 37823806
Sorry, slightwv. Your response wasn't on my screen when I posted. I'll leave this alone. :-)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823807
>>Oops, typo - dba_sequences

I believe in V10 of the site, you can now edit your own comments.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823819
>>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
 
LVL 32

Expert Comment

by:awking00
ID: 37823821
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823831
>>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
 
LVL 32

Expert Comment

by:awking00
ID: 37823852
>>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
 

Author Comment

by:royjayd
ID: 37829164
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 37829178
>>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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 37829243
You could also use -
'A'||lpad(to_char(person_map.nextval),3,'0')
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to count the number of rows in multiple Oracle Tables 10 63
oracle rollup query 3 51
Read only access to a Procedure in oracle? 4 48
selective queries 7 21
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now