Home » SQL & PL/SQL » SQL & PL/SQL » How to get left padded sequence values in pl/sql variable to insert it in table for ID creation (mer (Oracle 9i,Windows XP)
How to get left padded sequence values in pl/sql variable to insert it in table for ID creation (mer [message #444465] Mon, 22 February 2010 02:42 Go to next message
anil029
Messages: 14
Registered: February 2010
Junior Member
Hi,
I have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile.The sequence is used to generate ids as :

CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE  NOCYCLE  NOORDER


I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:

CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
   (sTraineeNo  IN  NUMBER,
    nReturned_O OUT NUMBER)
   IS
 
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
 
 
FOR ln_loop_idx IN 1..sTraineeNo LOOP
 
select LPAD(aaa_foc.trainee_id.nextval,3,'0') into nSequence from dual;
INSERT INTO aaa_foc.user_profile
     (
       USER_NAME,
       DESCRIPTION_TXT,
       PASSWORD_TXT)
VALUES
       ('TRAINEE'|| nSequence,
        'Trainee'|| nSequence,
        'PASSWORD'||nSequence);
 
ln_insert_cnt := ln_insert_cnt + 1;
 
END LOOP;
       commit;
         nReturned_O := ln_insert_cnt;
 
END IF;
EXCEPTION
  WHEN OTHERS THEN
      nReturned_O := 0;
      dbms_output.put_line('The error occurred is'|| ':::' || SQLCODE || ':::'||SQLERRM) ;
 
END;
/

Can anyone suggest me the possible reason for this.Thanks in advance.

Regards,
Anil
Re: How to get left padded sequence values in pl/sql variable to insert it in table for ID creation [message #444467 is a reply to message #444465] Mon, 22 February 2010 02:47 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi,

nSequence is defined as number in your code.
Hence the issue.

I guess no need to define a variable for this . You can directly use the lpad... code inside concatination.

Regards
Prajakta K
Re: How to get left padded sequence values in pl/sql variable to insert it in table for ID creation [message #444468 is a reply to message #444465] Mon, 22 February 2010 02:59 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with seq as (select level num from dual connect by level <= 5)
  2  select 'TRAINEE'||to_char(num,'fm000') nam
  3  from seq
  4  /
NAM
-----------
TRAINEE001
TRAINEE002
TRAINEE003
TRAINEE004
TRAINEE005

Regards
Michel
Previous Topic: ora-12516 ora-06512 after repair chained rows
Next Topic: Delete
Goto Forum:
  


Current Time: Fri Dec 02 12:17:05 CST 2016

Total time taken to generate the page: 0.25907 seconds