This discussion is archived
6 Replies Latest reply: Dec 5, 2012 2:12 PM by Justin Cave RSS

Simple Question... general syntax

977995 Newbie
Currently Being Moderated
I am clearly new to Oracle. Simple question though. I have a Procedure that runs a fairly involved query. The procedure is called and parameters are passed into it. While I am testing, I just want to DECLARE my variables so it will run properly. I am trying the following:

DECLARE
Var1 Varchar(2) := TO_CHAR('11/01/2012', 'MM/DD/YYYY')
Var2 Varchar(2) := TO_CHAR('11/30/2012', 'MM/DD/YYYY')

BEGIN
Select....;
END;

My query runs fine if I cut it out and enter values for all the variables. Is this the proper syntax to stuff a value for testing? I get an error saying the Select statement needs an INTO statement when I do it this way.

Thanks,
Dave
  • 1. Re: Simple Question... general syntax
    sb92075 Guru
    Currently Being Moderated
    when all else fails Read The Fine Manual

    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm

    http://asktom.oracle.com

    site above contain many fine coding examples
    Is this the proper syntax to stuff a value for testing?
    no
    I get an error saying the Select statement needs an INTO statement when I do it this way.
    error message is correct.
    the result set from SELECT must go somewhere.
  • 2. Re: Simple Question... general syntax
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
    >
    Var1 Varchar(2) := TO_CHAR('11/01/2012', 'MM/DD/YYYY')
    Var2 Varchar(2) := TO_CHAR('11/30/2012', 'MM/DD/YYYY')
    >
    Var1 and Var2 are already character datatypes so using TO_CHAR doesn't make any sense. Just use
    >
    Var1 Varchar(2) := '11/01/2012';
    Var2 Varchar(2) := '11/30/2012;
    >
    If they were DATE variables and you used TO_DATE then it would make sense.
    >
    Var1 DATE := TO_DATE('11/01/2012', 'MM/DD/YYYY');
    Var2 DATE := TO_DATE('11/30/2012', 'MM/DD/YYYY');
    >
    And note that you need a semicolon at the end of each statement. Untested:
    declare
    v_ename emp.ename%type;
    begin
    select ename into v_ename from emp where rownum = 1;
    dbms_output.put_line(v_ename);
    end;
  • 3. Re: Simple Question... general syntax
    KeithJamieson Expert
    Currently Being Moderated
    are you using sqlplus or some other oracle client tool?


    Can you post the entire code and also enclose it in code tags like this:
    \
       
    your code goes here
    \
    What you may want is something like this:
    DECLARE
    V_startdate date := TO_date('&startdate', 'MM/DD/YYYY')
    V_enddate date := TO_date('&enddate', 'MM/DD/YYYY')
    
    BEGIN
      myproc(v_startdate,v_enddate);
    END;
    You need to provide the signature of the procedure(what the parameters are , what datatypes they are, whether they are in , out, or in out parameters
  • 4. Re: Simple Question... general syntax
    Purvesh K Guru
    Currently Being Moderated
    974992 wrote:
    I am clearly new to Oracle. Simple question though. I have a Procedure that runs a fairly involved query. The procedure is called and parameters are passed into it. While I am testing, I just want to DECLARE my variables so it will run properly. I am trying the following:

    DECLARE
    Var1 Varchar(2) := TO_CHAR('11/01/2012', 'MM/DD/YYYY')
    Var2 Varchar(2) := TO_CHAR('11/30/2012', 'MM/DD/YYYY')
    As other have pointed out, you should be sure that you require a Varchar variable and not a Date.
    If you need varchar, then you have to specify proper width to it. And syntactically correct; Each PL/SQL statement ends with a Semi-colon, that includes variable declarations as well.

    It should be the following way:
     DECLARE
       Var1 Varchar(10) := '11/01/2012';
       Var2 Varchar(10) := '11/30/2012';
    Width of VARCHAR should be specified in BYTES/CHAR(s); the above example is for width in Bytes assuming Single Byte Character set. Also as SB pointed out, you need to read the concepts guide for PL/SQL to understand it and then practice it.
    Happy learning.
  • 5. Re: Simple Question... general syntax
    977995 Newbie
    Currently Being Moderated
    Okay... so maybe it actually is working. I just assumed that the error saying it needs an "INTO" statement was some sort of error. If all I am doing is running a select statement and populating the values of my variables for testing purposes and trying to run it in TOAD. I assumed that the results of the Select would just show on my output window. All I am trying to do is test before I add my sql to my code and pass parameters in via code. So I really need to review the results. Is this not the best way to test this?

    Thanks,
    Dave
  • 6. Re: Simple Question... general syntax
    Justin Cave Oracle ACE
    Currently Being Moderated
    If you are getting an error that an INTO is missing, that is an error. But since you're not showing the line of code that is throwing an error, it's hard for us to comment-- we can only suggest problems with the code that you did post.

    If you are populating variables from a SELECT statement, you would either need to use an INTO clause in your SELECT statement (with or without a BULK COLLECT), i.e.
    SELECT some_column, some_other_column
      INTO some_variable, some_other_variable
      FROM some_table
     WHERE some_condition 
    or you would need to have to do that assignment in PL/SQL, i.e.
    FOR x IN (some_query)
    LOOP
      some_variable := x.some_column;
      some_other_variable := x.some_other_column;
    
      ...
    END LOOP;
    If you are populating a local variable in a PL/SQL block, that would not be displayed in TOAD. You could enable DBMS_OUTPUT in TOAD and then use the DBMS_OUTPUT.PUT_LINE procedure to output whatever debugging data you want TOAD to read and display. But that is generally not a sensible way to test a query.

    Justin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points