Define vs declare date varaible in Toad for Oracle 9.0.1.8 using sql

Toad for Oracle
1

I am relatively new to Oracle - having used Sql server for a few years

I am trying to create a date varaible in a script that will use it in multiple statements later in the script.

This declare statement seems to work when I execute it as a script:

declare start_dt date;

begin

select to_date(‘01-Jun-2015’,‘DD-MON-YY’) into start_dt from dual;

end;

but when I try to join to it later in the follwong where clause:

and mytable.My_START_DATE between (start_dt) and (‘30-Jun-2015’)

I get a message “ORA-00904: “START_DT”: invalid identifier”

Nay help would be appreciated.

Thanks

2

PL/SQL is different from transaction-sql. A "declare-begin-end’ in PL/SQL is the base structure of a program - it does not accept or return any values. What you are looking for in your example is a “function” - e.g. “Function start_dt return date is …begin select… end;” This will allow you to use it in a sql-script (this can also be a stored function)

3

Hey Mark,

Welcome to the Oracle world, aka “The Dark Side”!

A few items here. First, this is really a forum for Toad discussions. There’s a generic Oracle forum here at http://www.toadworld.com/platforms/oracle/f/53.aspx where your question may have better traction.

Also, your version of Toad is horribly out of date. If you intend on using it with Oracle 11gR2 or v12, you’ll likely be happier upgrading it.

A quick start to an answer to your question, it seems like a scope issue. The declaration of START_DT has context only within the scope of your PL/SQL procedure, so Oracle – and therefore Toad – knows nothing about it once your procedure completes. Toad can extend that to it’s own context of a sort, too, but not how you’re used to with SQL Server.

Fire up your question in the Oracle forum. GL!

Rich

4

It’s hard to know why you’re getting that without seeing the whole script, but my guess is you’re trying to use it outside the current block. Here’s some
recommendations…

  1. Make sure your variables are used within the master begin/end

  2. Define your variables in your declare section

  3. Put your select statements in begin/end clauses - that way you can add exceptions to them as needed and control your data better.

  4. Use
    *dbms_output.put_line *statements to see your output - this will show you the values of your variables along the way.

  5. Include exception clauses in your blocks and at the end of your anonymous block to capture errors.

Also, know that unless your query your building returns only one row, then you’re going to run into an error of fetching too many rows, unless you handle
it - again, a good use of exceptions.

Here’s an example of the things I mentioned above…

declare

start_dt
date
:=
‘07-SEP-1981’;

end_dt
date
:=
‘09-SEP_1981’;

emp_no
number;

begin

dbms_output.put_line( 'start_dt:
'||start_dt);

begin

select
e.empno

into emp_no

from scott.emp
e

where
e.hiredate
between start_dt
and end_dt;

exception
when
no_data_found
then

dbms_output.put_line( ‘No
Data Found between dates’);

end;

dbms_output.put_line( 'emp_no:
'||emp_no);

exception
when
others
then

dbms_output.put_line( 'SQLERRM:
'||SQLERRM);

end;

From: Mark J [mailto:bounce-Mark_J@toadworld.com]

Sent: Wednesday, January 28, 2015 1:42 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Define vs declare date varaible in Toad for Oracle 9.0.1.8 using sql

Define vs declare date varaible in Toad for Oracle 9.0.1.8 using sql

Thread created by Mark J

I am relatively new to Oracle - having used Sql server for a few years

I am trying to create a date varaible in a script that will use it in multiple statements later in the script.

This declare statement seems to work when I execute it as a script:

declare
start_dt date;

begin

select
to_date(‘01-Jun-2015’,‘DD-MON-YY’)
into start_dt from dual;

end;

but when I try to join to it later in the follwong where clause:

and mytable.My_START_DATE
between (start_dt)
and (‘30-Jun-2015’)

I get a message “ORA-00904: “START_DT”: invalid identifier”

Nay help would be appreciated.

Thanks

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

5

I moved the declare statement and reformatted as follows:

declare

start_dt date:= to_date(‘01-Jun-2015’,‘DD-MON-YY’);

begin

I also movred the end statement to the bottom of the script and now gett the following error:

PLS-00103: Encountered the symbol “TABLE” when expecting one of the following:

:= . ( @ % ;

The symbol ":= was inserted before “TABLE” to continue.

6

Mark: you are missing the main issue - a sql statement can only see stored objects like tables, pakages, functions etc. it can NOT see a script; thus it does not know what “start_dt” is!

7

ok - I was trying to work with Dennis’ feedback I will try to find the correct syntax for functions. I am not using PL-SQL if that matters

8

If you want to just use SQL to get the results, then you have to create and compile the function first, and then you can access it like below. If you have further questions like this, I would recommend posting them to an Oracle forum, as Rich mentioned above, as this is for Toad issues. I would also recommend a beginners overview of PL/SQL, because if your working with Oracle for any amount of time, you’re going to need it.

CREATE OR REPLACE FUNCTION my_test_function RETURN NUMBER IS

start_dt date := ‘07-SEP-1981’;
end_dt date := ‘09-SEP_1981’;
emp_no number;

begin

begin
select e.empno
into emp_no
from scott.emp e
where e.hiredate between start_dt and end_dt;
exception when no_data_found then
emp_no := 0;
end;

return emp_no;

end;

Once the function is compiled, then you can do this from SQL…

select my_test_function
from dual;