Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Simple query: an Into clause is expected...

    Why won't this simple code run in SQL Plus Worksheet?
    Code:
    DECLARE		V_FIRSTNAME VARCHAR2(50);
    
    BEGIN
    
    V_FIRSTNAME := 'TODD';
    
    SELECT  FIRSTNAME,
            LASTNAME
    FROM    PERSON
    WHERE   PERSON.FIRSTNAME = V_FIRSTNAME;
    
    END;
    I get the error "PLS-00428: an INTO clause is expected in this SELECT statement". All the google searches I have done relate to the use of this syntax in a stored procedure, but I am just trying to return a dataset in SQL Plus Worksheet.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why won't this simple code run in SQL Plus Worksheet?
    because the syntax is not valid as the error message indicates.
    I suggest you actually read the fine PL/SQL Reference manual found at http://tahiti.oracle.com
    or visit http://asktom.oracle.com for many fine coding examples.
    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
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    In PL/SQL you need to use the 'SELECT ... INTO...' construct.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    SQL> set serveroutput on
    SQL>
    DECLARE
    		
      P_FIRSTNAME PERSON.FIRSTNAME%TYPE;
      L_FIRSTNAME PERSON.FIRSTNAME%TYPE;
      L_LASTNAME PERSON.LASTNAME%TYPE;
    
    BEGIN
    
    V_FIRSTNAME := 'TODD';
    
    SELECT  FIRSTNAME, LASTNAME
            INTO 
            L_FIRSTNAME, L_LASTNAME
    FROM    PERSON
    WHERE   PERSON.FIRSTNAME = V_FIRSTNAME;
    
    DBMS_OUTPUT.PUT_LINE(L_FIRSTNAME ||' '||L_LASTNAME);
    
    END;
    If there are more than 1 persons with the first name of TODD, you'll get an error, as this way of writing code expects only 1 record to be returned from your query.

    -cf

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by anacedent
    >Why won't this simple code run in SQL Plus Worksheet?
    because the syntax is not valid as the error message indicates.
    I suggest you actually read the fine PL/SQL Reference manual found at http://tahiti.oracle.com
    or visit http://asktom.oracle.com for many fine coding examples.
    Thanks for the links. I have several Oracle manuals, but they all suck. I hope these online resources will be more helpful.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, all the resources indicate that "..an INTO clause was omitted..."

    DUH.

    I don't want to select into anything. I don't want to store the results. I don't want to analyze the results. I'm simply trying to test my code logic (actual SQL statement is much more complicated and has many variables).

    I need to return more than one record.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    To expand your example

    Code:
     SET SERVEROUTPUT ON
    
    DECLARE
    V_FIRSTNAME VARCHAR2(50);
    X_FIRSTNAME  PERSON.FIRSTNAME%TYPE;
    X_LASTNAME  PERSON.LASTNAME%TYPE;
    BEGIN
    
    V_FIRSTNAME := 'TODD';
    
    
    
    SELECT  FIRSTNAME,
               LASTNAME
    INTO       X_FIRSTNAME,
              X_LASTNAME
    FROM    PERSON
    WHERE   PERSON.FIRSTNAME = V_FIRSTNAME
    WHERE ROWNUM < 2;
    
    DBMS_OUTPUT.PUT_LINE(X_FIRSTNAME||' / '||X_LASTNAME);
    
    END;
    This illustrates a couple of points.
    1) You must define holding variables
    2) A holding variable may ONLY hold one set of values.
    3) You can use dbms_output to output a string from a sql block
    4) You must use the serveroutput command before the block to turn on the dbms_output display.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I need to return more than one row...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    so put it in a LOOP dood.

    example:
    PHP Code:

    for i in (
        
    SELECT FIRSTNAMELASTNAME FROM PERSON 
        WHERE PERSON
    .FIRSTNAME V_FIRSTNAME )
    loop

       DBMS_OUTPUT
    .PUT_LINE(i.firstname||' / '||i.lastname);

    end loop
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Use SQL*Plus (not PL/SQL):
    Code:
    DEF V_FIRSTNAME='TODD'
    
    SELECT FIRSTNAME,
               LASTNAME
       FROM PERSON
     WHERE PERSON.FIRSTNAME = '&&V_FIRSTNAME'
         AND ROWNUM < 2;
    -- Or -- Using PL/SQL:
    Code:
    VAR csr REFCURSOR;
    DECLARE
    V_FIRSTNAME VARCHAR2(30):='TODD';
    BEGIN
    OPEN :csr FOR '
    SELECT FIRSTNAME,
               LASTNAME
       FROM PERSON
     WHERE PERSON.FIRSTNAME = '''||V_FIRSTNAME||'''
         AND ROWNUM < 2';
    END;
    /
    PRINT :csr

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    LKBrwn_DBA, I believe that if you replace these two lines of your code
    Code:
    ..
     WHERE PERSON.FIRSTNAME = '''||V_FIRSTNAME||'''
         AND ROWNUM < 2';
    ..
    with
    Code:
    ..
     WHERE PERSON.FIRSTNAME = :firstname
         AND ROWNUM < 2' using V_FIRSTNAME;
    ..
    It will be safer. Also, I dont understand why the rownum < 2 if the user wants to return more than one row.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Blind Dude, to return a resultset in Oracle you'll need a cursor I believe
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, and you guys are just too funny

    Blind dude, what version btw

    Oh, and I take the cursor thing back, I thought you had a package/procedures you were calling

    Let me go look something up

    SELECT * INTO....

    you guys crack me up
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by JMartinez
    ... Also, I dont understand why the rownum < 2 if the user wants to return more than one row.

    Just copied and pasted from one of the other posts...

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    blind dude,

    What happens when you hard code the value?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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