Home » SQL & PL/SQL » SQL & PL/SQL » Basic PL/SQL (Ora 9.2 Win NT)
Basic PL/SQL [message #442671] Tue, 09 February 2010 20:51 Go to next message
pbutera
Messages: 5
Registered: January 2010
Junior Member
Ok guys,
I ma buy preparing the certification and I got some exercise I can't solve even it seems quite easy.
Here is the question:
1. create a block PL/SQL which declares 5 variables:
v_varchar CHARACTER
v_number NUMBER
v_boolean1 BOOLEAN
v_date The same datatype as field HIREDATE from table EMP
v_boolean2 The same as v-boolean1

2. Assign these values to the respective variables:
The literal '25 is my birthday'
The first two characters from v_varchar
TRUE or FALSE depending on the expression "v_number is greater than 25"
Today's date
The opposite of v_boolean1

here is what I proposed:

DECLARE
v_varchar varchar2(255);
v_number number(7,2) :=0;
v_boolean1 boolean;
v_date emp.hiredate%type;
v_boolean2 v_boolean1%type;
BEGIN
v_varchar:='17 is my birthday';
v_number:=to_number(lpad(v_varchar,2));
v_boolean1:=(v_varchar>25);
v_date:=sysdate;
v_boolean2:=-v_boolean1;
END;
/
for what result which result ?

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 10

Sorry for coming probably quite basic and thanks in advance for your help.
Pierre
Re: Basic PL/SQL [message #442672 is a reply to message #442671] Tue, 09 February 2010 20:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/


  1  DECLARE
  2  v_varchar varchar2(255);
  3  v_number number(7,2) :=0;
  4  v_boolean1 boolean;
  5  v_date emp.hiredate%type;
  6  v_boolean2 v_boolean1%type;
  7  BEGIN
  8  v_varchar:='17 is my birthday';
  9  v_number:=to_number(lpad(v_varchar,2));
 10  v_boolean1:=(v_varchar>25);
 11  v_date:=sysdate;
 12  --v_boolean2:=-v_boolean1;
 13* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 10


> v_boolean1:=(v_varchar>25);
Line above make no sense.
BOOLEAN can be assigned only TRUE or FALSE
Re: Basic PL/SQL [message #442681 is a reply to message #442671] Tue, 09 February 2010 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
v_number:=to_number(lpad(v_varchar,2));

lpad is for (left) padding not for getting a substring.

Quote:
v_boolean2:=-v_boolean1;

A boolean is only TRUE or FALSE, what is the meaning of -TRUE?

Regards
Michel
Re: Basic PL/SQL [message #442703 is a reply to message #442671] Wed, 10 February 2010 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You've said:
  • v_boolean1 BOOLEAN
  • TRUE or FALSE depending on the expression "v_number is greater than 25"
  • v_boolean1 := (v_varchar>25);

Opposite from TRUE is FALSE (and vice versa); it is, as previously said, not achieved by a minus (-) sign, but NOT.

I'd say that you were quite close to the solution; try again and say if it still doesn't work.
Re: Basic PL/SQL [message #442743 is a reply to message #442671] Wed, 10 February 2010 06:00 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
your particular error is related to line

v_boolean1:=(v_varchar>25);

it should read

v_boolean1:=(v_varchar>'25');

There are as was noted other errors in your code too.

Good luck, Kevin
Re: Basic PL/SQL [message #442744 is a reply to message #442743] Wed, 10 February 2010 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, based on the requirement:Quote:
TRUE or FALSE depending on the expression "v_number is greater than 25"
it should probably be
v_boolean1:=(v_number>25);
Re: Basic PL/SQL [message #442745 is a reply to message #442671] Wed, 10 February 2010 06:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK my bad there.
Re: Basic PL/SQL [message #442787 is a reply to message #442671] Wed, 10 February 2010 08:57 Go to previous messageGo to next message
jitendra.prakash
Messages: 8
Registered: October 2009
Junior Member
I found the problem in the line.

v_number:=to_number(lpad(v_varchar,2));

I would like to suggest u to use substr like
v_number:=to_number(substr(v_varchar,1,2));

next lines are perfect.




Re: Basic PL/SQL [message #442788 is a reply to message #442787] Wed, 10 February 2010 08:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
next lines are perfect.


Words fail me......
Re: Basic PL/SQL [message #442792 is a reply to message #442787] Wed, 10 February 2010 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
next lines are perfect.

so answer this question:
Quote:
A boolean is only TRUE or FALSE, what is the meaning of -TRUE?

Regards
Michel
Re: Basic PL/SQL [message #442808 is a reply to message #442792] Wed, 10 February 2010 13:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not entirely true?
Re: Basic PL/SQL [message #442826 is a reply to message #442808] Wed, 10 February 2010 20:01 Go to previous messageGo to next message
pbutera
Messages: 5
Registered: January 2010
Junior Member
Thanks all of you for your help. And thank you especially for ignoring my english mistakes, I wrote the message at about 2AM, so I wasn't fresh anymore and I was obviously tired and upset for not finding what was wrong.
1. I had to use substr instead of lpad;
2. The biggest one: it was indeed
v_boolean1:=(v_number>25);

in stead
v_boolean1:=(v_varchar>25);

3. it was
  v_boolean2:=not(v_boolean1);

instead of
v_boolean2:=-v_boolean1;

(shame on me for this one).
But one thing was misleading, why have I had an error at line 1 ?
Thanks for all for your help and @BlackSwan, I hope I am down with the posting guidelines now...
Below is the full corrected code.
Regards
DECLARE
  v_varchar varchar2(255);
  v_number number(7,2) :=0;
  v_boolean1 boolean;
  v_date emp.hiredate%type;
  v_boolean2 v_boolean1%type;
BEGIN
  v_varchar:='17 is my birthday';
  v_number:=to_number(substr(v_varchar,1,2));
  v_boolean1:=(v_number>25);
  v_date:=sysdate;
  v_boolean2:=not(v_boolean1);
END;
Re: Basic PL/SQL [message #442828 is a reply to message #442826] Wed, 10 February 2010 20:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>But one thing was misleading, why have I had an error at line 1 ?
The anonymous PL/SQL block starting at LINE 1 has an error.
Please refer to my post in this thread that show the whole procedure & COMPLETE error response.
In addition to LINE 1 it more clearly states what the error is on LINE 10.

This is a classic case why the Posting Guidelines state to use CUT & PASTE of the WHOLE session (which I provided).
Re: Basic PL/SQL [message #442831 is a reply to message #442826] Wed, 10 February 2010 21:35 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
what's the use of this ? Am I missing anything in your post?
ind> DECLARE
  2    v_varchar varchar2(255);
  3    v_number number(7,2) :=0;
  4    v_boolean1 boolean;
  5    v_date emp.hiredate%type;
  6    v_boolean2 v_boolean1%type;
  7  BEGIN
  8    v_varchar:='17 is my birthday';
  9    v_number:=to_number(substr(v_varchar,1,2));
 10    v_boolean1:=(v_number>25);
 11    v_date:=sysdate;
 12    v_boolean2:=not(v_boolean1);
 13  END;
 14  /

PL/SQL procedure successfully completed.


sriram
Re: Basic PL/SQL [message #442886 is a reply to message #442831] Thu, 11 February 2010 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
what's the use of this ? Am I missing anything in your post?

I think you're missing an understanding of what the OP is asking for in the first post.
Re: Basic PL/SQL [message #442898 is a reply to message #442886] Thu, 11 February 2010 03:44 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think that @ramoradba has jumped into a topic without reading what's being said so far, especially OP's remark that "Below is the full corrected code" (so no wonder that it completed successfully).
Previous Topic: Time difference
Next Topic: How to display a name in 100 columns using dual
Goto Forum:
  


Current Time: Thu Dec 08 22:11:25 CST 2016

Total time taken to generate the page: 0.16635 seconds