469,128 Members | 1,661 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

Procedure problem

37
Hi
I have a problem with procedure.

i have a table patient_list
SQL> desc patient_list;
Name Null? Type
----------------------------------------- -------- ------------------------
PATIENT_ID NOT NULL VARCHAR2(10)
PATIENT_NAME VARCHAR2(25)
AGE VARCHAR2(3)
SEX VARCHAR2(8)
ADDRESS VARCHAR2(50)
TYPE_OF_APPOINTMENT VARCHAR2(10)

i am trying to write a procedure to generate the patientid Automatically

the procedure is
create or replace procedure getpatientid(patientid out varchar2,status out varchar2)
is
begin
select nvl(max(to_number(substr(patient_id,2),'9999')),'0 ') into patientid from patient_list;
patientid:=patientid+1;
patientid:='P'||patientid;
status:='0';
commit;
exception
when others then
status:='7';
end;
/
SQL> variable p varchar2;
SQL> variable s varchar2;
SQL> exec getpatientid(:p,:s);

PL/SQL procedure successfully completed.

SQL> print p;

P
--------------------------------
1

SQL> print s;

S
--------------------------------
7

It is generating the id automatically,
but also it gives the exception.
why this is happening.
plese help me
Sep 28 '07 #1
1 1141
debasisdas
8,127 Expert 4TB
If u want to return a value ,why not use a function instead of a procedure with out parameter.

kindly post the exception message also
Sep 28 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Thiemo Kellner | last post: by
7 posts views Thread by Tod Thames | last post: by
7 posts views Thread by Jeff Wang | last post: by
6 posts views Thread by Wojciech Wendrychowicz | last post: by
9 posts views Thread by fniles | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.