Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: remedial variable question

    I'm new to working in Oracle, I'm accessing the Oracle database using TOAD and simply trying to begin putting some dates into variables:

    declare
    date_1 constant DATE := '01-jan-2007';
    date_2 constant DATE := '31-jan-2007';

    begin

    select
    a.column_1
    from
    table a
    where
    a.date_range between date_1 and date_2;

    end;

    The above follows some of the examples that I've found online but appears to be expecting an "into" after the select statement.

    Can anyone point out the error here?
    Thanks,
    Bill

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    INTO what, do you want the returned data to be placed?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, it is expecting INTO. This is an example based on Scott's schema:
    Code:
    DECLARE
      date_1 CONSTANT DATE := TO_DATE('01.01.1981', 'dd.mm.yyyy');
      date_2 CONSTANT DATE := TO_DATE('31.01.1982', 'dd.mm.yyyy');
      l_cnt  NUMBER;
    BEGIN
      SELECT COUNT(*)
        INTO l_cnt
    	FROM EMP
    	WHERE hiredate BETWEEN date_1 AND date_2;
    	
      dbms_output.put_line('Count = ' || TO_CHAR(l_cnt));
    END;
    / 
    
    Count = 11
    
    PL/SQL procedure successfully completed.
    Except the INTO you are missing, there's something else here. You said that 'date_1' is a DATE variable, but you put a CHARACTER STRING into it. To you, as a human, it surely looks like a date, but to Oracle it is just a string. It *may* compile and actually do something, depending on default NLS date format set in your database. But, what works here, doesn't have to work there (i.e. in another database with different settings).

    Therefore: ALWAYS keep control over data (if you can - and here you do). Use appropriate function(s) - in this example, I used TO_DATE which converts string into a date, using specified format. Another example is TO_CHAR in a DBMS_OUTPUT.PUT_LINE. I could have left just 'l_cnt' variable, and Oracle would implicitly convert it to a character and print the output to the screen. But that's not a good habit. Never rely on implicit conversion. It might cost you much, sooner or later.

    Finally, here is the PL/SQL User's Guide and Reference book - take some time and read it to figure out how and why are things to be done in PL/SQL.

  4. #4
    Join Date
    Mar 2004
    Posts
    139
    Thanks for the responses, I don't want to put the data anywhere as you would with a select into, I just want the data returned to the data grid.

    What I'm trying to do is create the Oracle equivalent in TOAD of the following MS SQL sql statement, which will just return the results to the data grid-

    declare @date_1 as datetime, @date_2 as datetime

    set @date_1 = '01/01/2007'
    set @date_2 = '01/31/2007'

    select
    column
    from
    table
    where
    hire_date between @date_1 and @date_2
    Thanks,
    Bill

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Is there a reason why you can't just use straight SQL, since you want to view the output:

    Code:
    select
    column
    from
    table
    where
    hire_date between to_date('01/01/2007','MM/DD/YYYY') and to_date('01/31/2007','MM/DD/YYYY')
    Since TOAD responds to some SQL*Plus commands, you could do something like this, if you're reusing those same values repeatedly

    Code:
    define date_1 = '01/01/2007'
    define date_2 = '01/31/2007'
    
    select
    column
    from
    table
    where
    hire_date between to_date('&date_1','MM/DD/YYYY') and to_date('&date_2','MM/DD/YYYY')
    but this output only goes to the script output tab, so you have to look at the sub-tab in that area to see the data in a grid format.
    -cf

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Wait a minute! Your first code suggested PL/SQL. Another one seems to be pure SQL. Now, which one is it?

    PL/SQL will, most probably, return TOO-MANY-ROWS error if you leave it that way (the one you wrote) unless there's one and only one record that satisfies condition written in the WHERE clause.

    If you want to get result in TOAD, why don't you simply put it as
    Code:
    SELECT * FROM EMP
    WHERE hiredate BETWEEN TO_DATE('&date_1', 'dd.mm.yyyy')
                       AND TO_DATE('&date_2', 'dd.mm.yyyy');
    Ampersand sign will prompt you to enter values, and the result will be displayed in the Data Grid.

    ... but you've already seen all of it in Chuck's message. Being too slow, I'm afraid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •