Using variables

Follow / 30 Jan 2012 at 11:06pm

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

Replies

  • Follow / 30 Jan 2012 at 11:48pm

    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).

  • Follow / 31 Jan 2012 at 1:18am

    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!

  • Follow / 31 Jan 2012 at 12:26pm

    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

     

    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

    __
    _______________________________________

    Groetjes,

       Wim de Lange

  • Follow / 31 Jan 2012 at 4:06pm

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

  • Follow / 31 Jan 2012 at 4:09pm

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

  • Follow / 31 Jan 2012 at 7:48pm

    What I am doing is run some statements manually (for example editing the SQL each time to replace the dates and other "variables") and then 1) some of those statements I export the data to a csv file and 2) I put the output of other select statements in a spreadsheet. As I was very busy I just mindlessly did this manually without thinking in automating as time is a luxury I did not have with several project's deadlines, but now that I finished two I have some time to learn this stuff and my first step was trying to use variables, which now seems to be working, so my second step is to try to grab the output of some of those selects and write a CSV (now I right-click the each output grid and use export dataset and set the options, including the file name manually, I would like to automate all of this eventually)