Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql block
pl/sql block [message #213943] Fri, 12 January 2007 21:54 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello all

i need to make a pl/sql block where user accept months(in char)
and year (in number) which display all the date along with the corresponding days belonging to the months and year.
format would be like -

date day
---------- ------
01-jan-82 monday
02-jan-82 tuesday
--- --
--- --

hope i can make u understand.

i had a block but i m confused how would i get the result.
declare
 v_month varchar2(20);
  v_yera date:=to_char(&year, 'yyyy');
   v_date date;
    v_day date;
begin
 v_month:='&month';
  select to_char(hiredate, 'dd-mon-yyyy')"date", to_char(hiredate, 'day')"day" into
   v_date, v_day from emp
     where to_char(hiredate, 'yyyy')=v_year
       and to_char(hiredate, 'mon')=v_month;
         dbms_output.put_line('date= '||v_date||' day= '||v_day);
end;
/


error occured i.e

where to_char(hiredate, 'yyyy)=v_year
PL/SQL:ORA-00923:FROM keyword not found where expected
ORA-06550: line 8, column 6
PL/SQL:SQL statement ignored

please make me correct and also make me understand about the
mistake.

thanxx

regards
ishika











Re: pl/sql block [message #213944 is a reply to message #213943] Fri, 12 January 2007 22:18 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi ishika

first of all you are taking variable v_yera and using v_year in the block.

make it correct.

ashu
Re: pl/sql block [message #213947 is a reply to message #213943] Fri, 12 January 2007 22:37 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello ashu.

that's by mistake i have type yera in place of year.

it's
v_year date:=to_char(&year, 'yyyy');

regards
Re: pl/sql block [message #213982 is a reply to message #213947] Sat, 13 January 2007 06:12 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood your task, you have to enter a month name (string), a year (number), and output all days in that month. So, what are 'emp' table and 'hiredate' column doing here? It seems that you didn't understand the question. True, you did produce some code, but - as it is - it is absolutely irrelevant to the problem.

Or, it may be that I didn't get the point, misunderstood your question and your code has to do something with the whole thing.

I'd be glad to help, bot NOT to write the code instead of you. First think about it, write a pseudo-code so that it is clear what and how it is to be done, then write your PL/SQL procedure and ask for help if necessary.

By the way, do you HAVE TO use PL/SQL for it? It can be done in SQL.
Re: pl/sql block [message #213985 is a reply to message #213947] Sat, 13 January 2007 06:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There are some oddities in your code:

v_year date:=to_char(&year, 'yyyy');

v_year is a date. Why do you convert users input to a char then? You should convert users input to a date.

Why alias the columns in your query if you do a select into?

This query is very likely to return more than one row for a given year-month combination. Therefor you should use a cursor.

You say the error is
where to_char(hiredate, 'yyyy)=v_year
yet your code does have a closing quote after the format-mask.

Please copy-paste and don't make up/mix versions of errors or code.
Re: pl/sql block [message #214024 is a reply to message #213985] Sat, 13 January 2007 13:05 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi ishika,

LF is right that can be done in SQL so why do you want to write plsql.

Tell us if you face the error.

Ashu
Previous Topic: about sql query
Next Topic: HELP - Cursor, Table Variable, and Update
Goto Forum:
  


Current Time: Mon Dec 05 04:42:51 CST 2016

Total time taken to generate the page: 0.14796 seconds