|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
Registered: February 2010
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)
ln_insert_cnt PLS_INTEGER :=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
ln_insert_cnt := ln_insert_cnt + 1;
nReturned_O := ln_insert_cnt;
WHEN OTHERS THEN
nReturned_O := 0;
dbms_output.put_line('The error occurred is'|| ':::' || SQLCODE || ':::'||SQLERRM) ;
Can anyone suggest me the possible reason for this.Thanks in advance.