RE: Re: Using variables

Could use a pipelined function too, that way he could select directly
from the function.

 

From: [email protected] [mailto:[email protected]] On Behalf Of
Bert Scalzo
Sent: Tuesday, January 31, 2012 3:07 PM
To: [email protected]
Subject: RE: [toad] Re: Using variables

 

  

Could return a ref cursor - that would possibly approximate the sql
server style of returning a table .....

 

From: [email protected] [mailto:[email protected]] On Behalf Of
Wim de Lange
Sent: Tuesday, January 31, 2012 4:26 AM
To: [email protected]
Subject: Re: [toad] Re: Using variables

 

  

Hmm a sqlserver programmer? In PL/SQL it looks like this:

 

declare

   theDate date;

begin

    theDate := TO_DATE('20120101','YYYYMMDD') ;

    select * from myTable WHERE TRANDATE = theDate order by TRANDATE,
LOCAL_TIME;

end;

 

Worse part is, you code returns a temporary table, that is possible in
Sqlserver. But that is NOT possible in Oracle. So the above code does
not work too.

You must use cursors or varray's to return more data. So if you give
more information on what you are trying to do, we could give hints to
what you can do to solve the specific problem.

 

Groetjes,
   Wim




2012/1/31 Hugo <[email protected]>

  

Message from: Amarrete



Yes, it was a TOAD question, and I think that was the problem. I was
just creating the query, and apparently the default is "SQL Style" and
that does not work (your query does work there, but not mine), but if I
select "T-SQL Style" or even "Text' my query works, it seems I will need
to RTFM as I do not know the differences. Thanks!


_______________________________________

Historical Messages 


Author: Hugo
Date: Mon Jan 30 15:18:18 PST 2012
Yes, it was a TOAD question, and I think that was the problem. I was
just creating the query, and apparently the default is "SQL Style" and
that does not work (your query does work there, but not mine), but if I
select "T-SQL Style" or even "Text' my query works, it seems I will need
to RTFM as I do not know the differences. Thanks!

__



Author: Richard Squires
Date: Mon Jan 30 13:48:48 PST 2012
I'm a bit confused as to whether this is an SQL, PLSQL or TOAD question,
but let's assume it's a TOAD question.  You can run a statement like
yours in TOAD as follows:

select * from all_tables 
where trunc(last_analyzed) = :thedate 
order by last_analyzed;


The pop up that appears allows you to identify :thedate as a DATE and to
enter a value.

In TOAD you could have many statements in the same editor window that
use :thedate and they would use the same value, you wouldn't therefore
need to create a PLSQL programme unit and specify a variable(s).

__

Author: Hugo
Date: Mon Jan 30 13:06:57 PST 2012
I am new to Toad and I am trying to do something very simple, but cannot
figure it out, I want to set a value for a/some variable/s and then use
them, for example:

begin
    :theDate := '20120101';
    select * from myTable WHERE TRANDATE = to_date(:theDate, 'YYYYMMDD')
order by TRANDATE, LOCAL_TIME;
end;

But this has two problems to begin with, first it shows a window for me
to set the value of the variable :theDate1, second it just fails for it
expects an INTO clause (ORA-06550: line 3, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement), so, how
can I set up variables and then use them in subsequently sql statements
(my version of TOAD is 11.0.0.116 if that matters.

TIA

__
_______________________________________