| 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  |
anil029
Messages: 15
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
