Re: [Code Tester for Oracle - Discussion Forum] Can I create a variable in TOAD/SQL to prompt for a date(s)?

Toad for Oracle
1

'&variable_name' i.e. '&date1' '&date2' should prompt for input.

This has nothing to do with Toad as Toad us just an I.D.E.

It has everything to do with Oracle sql, *sqlplus, PL/Sql.

Hope this helps

On May 19, 2017 1:29 AM, "hrishi14" bounce-hrishi14@toadworld.com wrote:

Can I create a variable in TOAD/SQL to prompt for a date(s)?

Thread created by hrishi14
Group,

I'm using TOAD to query the Oracle database. My better expertise is in VB.net. I have very little SQL experience.

My question: Can I create a variable in TOAD/SQL to prompt for a date(s)? Here is what I want to do:

Question 1: Enter Beginning Date (user to enter date value that looks like this: 01/01/2015)
Question 2: Enter Ending Date (user to enter ending date value, i.e. 04/30/2015)

I then will need to create two further variables based on these first two to represent the prior years dates (i.e 01/01/2014 and 04/30/2014). I would know how to do this in VB.net. Unfortunately I'm not sure about PL/SQL (or whatever it is that TOAD uses). Can you teach me how to do this?

Thanks for your assistance.

HRISHI

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Code Tester for Oracle Forum notifications altogether.

Code Tester for Oracle - Discussion Forum

Flag this post as spam/abuse.

2

I have typed this program but still not getting the execution…

DECLARE
start_date DATE;
end_date DATE;
begin
start_date := to_date(‘2017/05/16 09:00:09’ , ‘yyyy/mm/dd hh24:mi:ss’);
end_date := to_date(‘2017/05/16 09:00:10’,‘yyyy/mm/dd hh24:mi:ss’);
end;

SELECT * FROM UTV.TICK
WHERE (TICK_DATE BETWEEN to_date(‘start_date’)
AND to_date(‘end_date’))
ORDER BY TICK_DATE;

is is showing this error::

ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol “SELECT”

what to do can anyone help???

3

Not sure how your code snippit may fit into something else, but you can do what you are trying to do in regular sql.

Put the format models for the dates directly in the select statement. See Below

Just run this by itself. (no declare, begin end, etc.)

SELECT * FROM UTV.TICK
WHERE TICK_DATE BETWEEN to_date('2017/05/16 09:00:09' , 'yyyy/mm/dd hh24:mi:ss')
AND to_date('2017/05/16 09:00:10','yyyy/mm/dd hh24:mi:ss')
ORDER BY TICK_DATE;

I am not where I can test this right now but it should work

GB

On Sat, May 20, 2017 at 2:35 AM, hrishi14 bounce-hrishi14@toadworld.com wrote:

RE: Re: [Code Tester for Oracle - Discussion Forum] Can I create a variable in TOAD/SQL to prompt for a date(s)?

Reply by hrishi14
I have typed this program but still not getting the execution........

DECLARE
start_date DATE;
end_date DATE;
begin
start_date := to_date('2017/05/16 09:00:09' , 'yyyy/mm/dd hh24:mi:ss');
end_date := to_date('2017/05/16 09:00:10','yyyy/mm/dd hh24:mi:ss');
end;

SELECT * FROM UTV.TICK
WHERE (TICK_DATE BETWEEN to_date('start_date')
AND to_date('end_date'))
ORDER BY TICK_DATE;

is is showing this error::

ORA-06550: line 9, column 1:
PLS-00103: Encountered the symbol "SELECT"

what to do can anyone help????

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Code Tester for Oracle Forum notifications altogether.

Code Tester for Oracle - Discussion Forum

Flag this post as spam/abuse.

--
Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

4

YES IT is working but …i want to try while declaring this variable into start_date and end_date

5

You don’t have to do any of that. Write you SQL statement:

SELECT * FROM UTV.TICK
WHERE (TICK_DATE BETWEEN to_date(:start_date)
AND to_date(:end_date))
ORDER BY TICK_DATE;

When you will execute this statement in TOAD, a pop-up window will open with two variables. Change data type to DATE and input your values.

6

The issue you’re going to have in trying to do that is that you can’t simply do a select inside of an anonymous block. You need to do something with it (put it into a cursor would be the most common choice). In your anonymous block, you are creating the start_date and end_date but once you’re outside of that block those don’t exist any longer (they fall out of scope) so your select statement doesn’t have the ability to see them. Remember that Oracle sees an anonymous block as a single executable statement (DECLARE … END).

If you want to do it outside of an anonymous block you need to use DECLARE to set the variables and be sure to run it as a script (F5). Something like this:

define start_date = “‘2017/05/16 09:00:09’, ‘yyyy/mm/dd hh24:mi:ss’”;
define end_date = “‘2017/05/16 09:00:10’, ‘yyyy/mm/dd hh24:mi:ss’”;

select *
from UTV.TICK
where (TICK_DATE between TO_DATE (&start_date) and TO_DATE (&end_date))
order by TICK_DATE;