Oracle Database - re:stored procedure - Asked By nithin kumar1519 on 10-Nov-11 12:07 AM

TRYING TO CREATE AN SP WITH GENERATING SEQUENCE INTO IT

SEQUENCE:



create or replace procedure proc_Second
AS
BEGIN
insert into emp_information (eno,ename,job,salary,status) values(EMP_INFORMATIONID.nextval,varchar,varchar,number,number)
COMMIT;
end;

I'M GETTING COMPILATION ERRORS
Kirtan Patel replied to nithin kumar1519 on 10-Nov-11 02:32 PM
you have written create and replace both you need any one of them like if you have not compiled stored procedure before then you need to use "create" keyword else stored procedure will be already in db so you need to use "replace"

so assuming stored procedure not compiled yet .

you need to write like this 

Note : after correcting the code if you still get errror check that you have used columname = "status" which is reserved keyword of oracle change the columname also in values what "varchar" stand for as value is not clear provide some valid string there ..


CREATE procedure proc_Second
IS
BEGIN
    INSERT INTO emp_information(eno,ename,job,salary,status) values(EMP_INFORMATIONID.nextval,varchar,varchar,number,number)
    COMMIT;
END;
 
sanjeev katiyar replied to nithin kumar1519 on 19-Dec-11 06:22 AM

 Hi,

 When use  data type varchar2(20) , char  value must we enclosed single quota example ( 'JIMMY')
 you use in where varchar or char single quotation. According to me, have no compilation error
 
  create or replace procedure proc_Second
AS
BEGIN
insert into emp_information (eno,ename,job,salary,status) values(EMP_INFORMATIONID.nextval,'varchar','varchar',number,number)
COMMIT;
end;

create sequence sequence_name
start with  pass_value(1..etc)
increment by pass_number(1...etc)

 where nextval and currval are pseudo column that use show  nextval and currval show the current value

 Use that code.