get data between two dates from GL_DAILY_RATES_V table

Hi, how about this:


SELECT columns
FROM TABLE
WHERE data BETWEEN CONVERSION_DATE AND CONVERSION_DATE+7;

to get all columns from CONVERSION_DATE and 7 days further.

Also be aware that Oracle date columns have a time portion built in.  You need to account for that when querying them.

If you can provide a little more detail about your issue we can provide a more detailed answer.

Sample data and expected results always help.

Hello Gerwinjansen,

 Hmm. but no. I am trying to write a procedure in pl/sql where, the parameters are from_date and to_date and i need to fetch the data from gl_daily_rates table for that given data.

here is the query that i am trying to write

create or replace procedure get_rates(FROM_DATE IN OUT DATE, TO_DATE IN OUT DATE)
as
begin 
 select from_currency, to_currency 
 from gl_daily_rates_v
[b] where "DATA BETWEEN FROM_DATE AND TO_DATE"[/b]
end;

i need the query here in BOLDED code part,, i hope that clears,,thank u again

Select allOpen in new window

How are you calling the procedure?  It might be best to pass in a varchar2 and use to_date to convert it.

In pl/sql the output of a select needs to go somewhere.  What are you doing with the results of that select?

Hello slighwv,,,

thank you for the response,

 I am complete newbie. i am trying to figure out stuff here.
 new suggestions are welcome like passing in a varchar. I know there are errors in that procedure, but i am not able to figure out them now.

I am trying to use this procedure to get/fetch the data and that data will be sent out to a csv file later.

to_date is a sql function so you cannot use it as a variable name.

If your calling language cannot handle native Oracle dates then try the code below.  You just need to know the format of the values passed in.

You don't need "OUT" unless you are passing the values back out.

Since you say you will be creating a CSV, how?  Are you planning on using UTL_FILE to write it to a file on the database server?  Are you wanting to return a CURSOR back to some calling program that will stream/write out the CSV?

create or replace procedure get_rates(FROM_DATE IN varchar2, my_TO_DATE IN varchar2)
as
begin 

for i in ( select from_currency, to_currency 
 from gl_daily_rates_v
 where CONVERSION_DATE >= to_date(from_date,'MM/DD/YYYY') AND 
CONVERSION_DATE < to_date(my_to_date,'MM/DD/YYYY')+1 )
loop
    --do some stuff here like write out a line to a file.
    --for now, just display it back to sqlplus
    dbms_output.put_line(i.from_currency || ',' || i.to_currency);
end loop;
end;

Select allOpen in new window

Notice the < and +1 to_date.  Since Oracle dates have a time, this will be '<' midnight on next day.

Thank you Slightwv,

 that was some valuable information there in the first 3 points.

Now, i understand what you tried to express. Just creating a procedure does not help. We need to send that data somewhere. And that is the only way we can test it.
Yes, It is to a csv file.
Also, Yes, I guess, i should be using a UTL file to load the data from the procedure to a csv file on the server.

But, when i try to create this procedure i am getting this error

"pls-00103 encountered the symbol when expecting one of the following"
    := . (%;

Hello

RDBMS : 11.1.0.7.0
Oracle Applications : 12.1.3
 and i am using toad for oracle and its version is 9.0.0.160

thank you

I'm not a Toad person but have not heard of any problems using their sql worksheet to create procedures.

Can you try from sqlplus?

Are you connecint to the database as the same users that owns the gl_daily_rates_v view?

If not, you likely need to add the table owner (or create a synonym):

...
from table_owner.gl_daily_rates_v
...

Yes, I guess, it is my toad problem.
I tried to create a basic procedure and still its throwing the same error.

I should award you the points as you have answered the original question. Also you have provided some usefull information.

I shall ask other questions in other posts.

Thank you slightwv