Home » SQL & PL/SQL » SQL & PL/SQL » problem on declaration section
problem on declaration section [message #212519] Fri, 05 January 2007 11:35 Go to next message
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 #212521 is a reply to message #212519] Fri, 05 January 2007 11:56 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
'married', 'male' and 'female' are strings and should be enclosed into single quotes.
Re: problem on declaration section [message #212528 is a reply to message #212519] Fri, 05 January 2007 12:25 Go to previous messageGo to next message
yaskumar
Messages: 5
Registered: January 2007
Location: U.S
Junior Member
thanxx littlefoot.

i will give u feedback after solving it

bye
Re: problem on declaration section [message #212536 is a reply to message #212528] Fri, 05 January 2007 13:31 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It doesn't really need that long time to process it.
Instead of        if v_ms =  married  then
you should have   if v_ms = 'married' then
The same goes for the rest of it.
Re: problem on declaration section [message #212567 is a reply to message #212519] Fri, 05 January 2007 20:59 Go to previous messageGo to next message
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 #212568 is a reply to message #212519] Fri, 05 January 2007 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Please visit http://tahiti.oracle.com & read the fine PL/SQL Reference manual or visit http://asktom.oracle.com for many find coding examples.
Just curious, is this your 1st programming class/language?

[Updated on: Fri, 05 January 2007 21:11] by Moderator

Report message to a moderator

Re: problem on declaration section [message #212585 is a reply to message #212568] Sat, 06 January 2007 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
.. and while you are busy reading anyway, take a look at an online dictionary.
Try to look up
u, r, rite, m and thanx.
Re: problem on declaration section [message #212588 is a reply to message #212585] Sat, 06 January 2007 01:51 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL, Frank! Smile is a lovely way to start a day Smile

@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>
Previous Topic: Select column Dynamically
Next Topic: How to convert VARCHAR2 to BLOB ?
Goto Forum:
  


Current Time: Sun Dec 04 12:27:00 CST 2016

Total time taken to generate the page: 0.08317 seconds