| problem on declaration section [message #212519] |
Fri, 05 January 2007 11:35  |
yaskumar
Messages: 5
Registered: January 2007
Location: U.S
|
Junior Member |
|
|
hello all
please look after my pl/sql procedure.
there's an error which i unable to recognise,
also convey me what i m missing.
my procedure is like -
declare
v_sex varchar2(10);
v_ms varchar2(10);
v_age number(10);
begin
v_sex:='&sex';
v_ms:='&ms';
v_age:=&age;
if v_ms=married then
dbms_output.put_line('driver is insured');
elsif
v_sex=male then
if v_age>=30 then
dbms_output.put_line('driver is insured');
else
dbms_output.put_line('driver is not insured');
end if;
elsif
v_sex=female then
if v_age>=25 then
dbms_output.put_line('driver is insured');
else
dbms_output.put_line('driver is not insured');
end if;
end if;
end;
/
if v_ms=married then
*
Error at line 9:
ORA-06550: line 9, column 10:
PLS-00201: identifier 'MARRIED' must be declared
ORA-06550: line 9, column 2:
pl/sql: statement ignored
please tell me how will i declare it on declaration section.
regards
yaskumar
|
|
|
|
|
|
|
|
|
|
| Re: problem on declaration section [message #212567 is a reply to message #212519] |
Fri, 05 January 2007 20:59  |
yaskumar
Messages: 5
Registered: January 2007
Location: U.S
|
Junior Member |
|
|
u r rite littlefoot.
now i got the result.
but one more thing i want to ask.
i mentioned
v_ms:='&ms';
so why i need again to put MARRIED on single quotes.
V_MS is varchar type that's why i put &ms in single quotes.
actually you told me to do & i got the result but still
now i m confused.
please make me clear about it.so that in future i will
not make this type of mistake.
thanx for being with me.
regards
yaskumar
|
|
|
|
|
|
|
|
| Re: problem on declaration section [message #212588 is a reply to message #212585] |
Sat, 06 January 2007 01:51 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
LOL, Frank! Smile is a lovely way to start a day 
@yaskumar: you wrote an anonymous PL/SQL block. It is OK for testing purposes, but I believe you can't use it in any production - no end user interacts with your code through SQL*Plus so that it could see messages your code returns.
Instead, create a FUNCTION, pass 3 parameters and return the result. Perhaps such a code will be more understandable to you as you'll avoid "single quotes confusion". Function strictly declares parameters' datatype. Of course, you'll still have to enclose strings ('married', 'male', ...) into single quotes. Why? You'll find that in the manual Anacedent suggested.
So, here it is, a little bit modified:CREATE OR REPLACE FUNCTION fun_insurance (
v_sex IN CHAR,
v_ms IN CHAR,
v_age IN NUMBER
)
RETURN CHAR
IS
retval VARCHAR2(100);
BEGIN
IF v_ms = 'married'
THEN
retval := 'driver is insured';
ELSIF v_sex = 'male'
THEN
IF v_age >= 30
THEN
retval := 'driver is insured';
ELSE
retval := 'driver is not insured';
END IF;
ELSIF v_sex = 'female'
THEN
IF v_age >= 25
THEN
retval := 'driver is insured';
ELSE
retval := 'driver is not insured';
END IF;
END IF;
RETURN (retval);
END;
/
SQL> SELECT fun_insurance('male', 'not married', 30) result FROM dual;
RESULT
------------------------------------------------------------
driver is insured
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
