Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Stored Procedure Error

    Here is my code to create an SP:

    Code:
    CREATE OR REPLACE PROCEDURE attendance
      (sdate IN DATE,
       edate IN DATE)
    AS
    BEGIN
      SELECT ALL EM.EM_KEY, EM.EM_DPKEY, EM.EM_TMKEY, EM.EM_LAST_NAME,
      EM.EM_FIRST_NAME, ATX.ATX_CLOCK_IN, ATX.ATX_CLOCK_OUT, ATX.ATX_DATE
      FROM ATX, EM
      WHERE (ATX.ATX_EMKEY = EM.EM_KEY) AND ATX.ATX_DATE BETWEEN TO_CHAR(sdate) AND TO_CHAR(edate) ORDER BY ATX.ATX_DATE, EM.EM_LAST_NAME;
    END attendance;
    /
    SHOW ERRORS
    Now, I get these errors when ran:

    Code:
    Warning: Procedure created with compilation errors.
    
    Errors for PROCEDURE ATTENDANCE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/3      PLS-00428: an INTO clause is expected in this SELECT statement
    6/3      PL/SQL: SQL Statement ignored
    Can someone see where I'm going wrong? Thanks, Jeremy
    Nothing better than a good ride.

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

    Cool

    <QUOTE>
    -----------------------------------------------------------------
    6/3 PLS-00428: an INTO clause is expected in this SELECT statement
    </QUOTE>

    Oracle is trying to tell you that you have not defined any variables to receive the results of the query.

    A 'select' statement inside a procedure requires variables defined in order to receive and be able to manipulate the results of the query.

    You have to write the query as:

    SELECT COLS,... INTO VARS... FROM ....

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Can you help me out? I thought I did that in my declare block but must had been mistaken. If I were to run the query by itself, and replaced "sdate" and "edate" with dates, it works properly. Thanks, Jermey
    Nothing better than a good ride.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    aren't you passing in the dates from outside??

    PHP Code:
    CREATE OR REPLACE PROCEDURE attendance
         
    (sdate IN DATE,
          
    edate IN DATE)

          
    cursor data_cursor is
    SELECT 
            EM
    .EM_KEY
            
    EM.EM_DPKEY
            
    EM.EM_TMKEY
            
    EM.EM_LAST_NAME,
            
    EM.EM_FIRST_NAME
            
    ATX.ATX_CLOCK_IN
            
    ATX.ATX_CLOCK_OUT
            
    ATX.ATX_DATE
      FROM 
            ATX

            
    EM
      WHERE 
           
    (ATX.ATX_EMKEY EM.EM_KEY) AND 
            
    ATX.ATX_DATE BETWEEN 
              sdate 
    AND edate 
      ORDER BY 
            ATX
    .ATX_DATE
            
    EM.EM_LAST_NAME;

    AS
    BEGIN
      
    for v_data IN data_cursor 
          loop
            
    (updateinsert or whatever stuff you want to do)
          
    end loop;

    END;

    Personally, I normally convert dates to character strings when they come in and manipulate then.

    if the between gives you errors use the to_date function.
    Also, you never say what you want to do with the data.
    Last edited by The_Duck; 07-21-03 at 19:06.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Duck...Same error as before. Got any ideas? Thanks, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I edited my code above.
    state what you are trying to do.

    Are you passing in the 2 date variables? I am guessing yes.

    What do you want to do with your output?

    Throw it in a new table? send it back out the proc?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Well...I'm a developer and I was told I could create a Crystal Report from data coming from a Stored Procedure. All I need to do is create a procedure that will take my two variables, sdate and edate, and run a query and output to the screen as any other query. Can you help? Thanks, Jeremy
    Nothing better than a good ride.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    sounds good to me.

    You need to throw your output into variables.

    Try this:

    PHP Code:
    CREATE OR REPLACE PROCEDURE attendance
      
    (     sdate IN DATE,
            
    edate IN DATE,
            
    V_EM_KEY OUT varchar2
            
    V_EM_DPKEY OUT varchar2
            
    V_EM_TMKEY OUT varchar2
            
    V_EM_LAST_NAME OUT varchar2,
            
    V_EM_FIRST_NAME OUT varchar2
            
    V_ATX_CLOCK_IN OUT varchar2
            
    V_ATX_CLOCK_OUT OUT varchar2
            
    V_ATX_DATE  OUT varchar2
       
    )
    AS
    BEGIN
      SELECT 
            EM
    .EM_KEY
            
    EM.EM_DPKEY
            
    EM.EM_TMKEY
            
    EM.EM_LAST_NAME,
            
    EM.EM_FIRST_NAME
            
    ATX.ATX_CLOCK_IN
            
    ATX.ATX_CLOCK_OUT
            
    ATX.ATX_DATE
      INTO
            V_EM_KEY

            
    V_EM_DPKEY
            
    V_EM_TMKEY
            
    V_EM_LAST_NAME,
            
    V_EM_FIRST_NAME
            
    V_ATX_CLOCK_IN
            
    V_ATX_CLOCK_OUT
            
    V_ATX_DATE
      FROM 
            ATX

            
    EM
      WHERE 
           
    (ATX.ATX_EMKEY EM.EM_KEY) AND 
            
    ATX.ATX_DATE BETWEEN 
                sdate 
    AND edate 
      ORDER BY 
            ATX
    .ATX_DATE
            
    EM.EM_LAST_NAME;

    END;

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

  9. #9
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Re: Stored Procedure Error

    Hi
    The way the stored procedures work in case of select statement, is that you specify into clause, which have the local variables names declared in the DECLARE section.
    Hope that helps.
    Thanx and Regards
    Aruneesh

    Originally posted by JCScoobyRS
    Here is my code to create an SP:

    Code:
    CREATE OR REPLACE PROCEDURE attendance
      (sdate IN DATE,
       edate IN DATE)
    AS
    BEGIN
      SELECT ALL EM.EM_KEY, EM.EM_DPKEY, EM.EM_TMKEY, EM.EM_LAST_NAME,
      EM.EM_FIRST_NAME, ATX.ATX_CLOCK_IN, ATX.ATX_CLOCK_OUT, ATX.ATX_DATE
      FROM ATX, EM
      WHERE (ATX.ATX_EMKEY = EM.EM_KEY) AND ATX.ATX_DATE BETWEEN TO_CHAR(sdate) AND TO_CHAR(edate) ORDER BY ATX.ATX_DATE, EM.EM_LAST_NAME;
    END attendance;
    /
    SHOW ERRORS
    Now, I get these errors when ran:

    Code:
    Warning: Procedure created with compilation errors.
    
    Errors for PROCEDURE ATTENDANCE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/3      PLS-00428: an INTO clause is expected in this SELECT statement
    6/3      PL/SQL: SQL Statement ignored
    Can someone see where I'm going wrong? Thanks, Jeremy

  10. #10
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Originally posted by The_Duck
    sounds good to me.

    You need to throw your output into variables.

    Try this:

    PHP Code:
    CREATE OR REPLACE PROCEDURE attendance
      
    (     sdate IN DATE,
            
    edate IN DATE,
            
    V_EM_KEY OUT varchar2
            
    V_EM_DPKEY OUT varchar2
            
    V_EM_TMKEY OUT varchar2
            
    V_EM_LAST_NAME OUT varchar2,
            
    V_EM_FIRST_NAME OUT varchar2
            
    V_ATX_CLOCK_IN OUT varchar2
            
    V_ATX_CLOCK_OUT OUT varchar2
            
    V_ATX_DATE  OUT varchar2
       
    )
    AS
    BEGIN
      SELECT 
            EM
    .EM_KEY
            
    EM.EM_DPKEY
            
    EM.EM_TMKEY
            
    EM.EM_LAST_NAME,
            
    EM.EM_FIRST_NAME
            
    ATX.ATX_CLOCK_IN
            
    ATX.ATX_CLOCK_OUT
            
    ATX.ATX_DATE
      INTO
            V_EM_KEY

            
    V_EM_DPKEY
            
    V_EM_TMKEY
            
    V_EM_LAST_NAME,
            
    V_EM_FIRST_NAME
            
    V_ATX_CLOCK_IN
            
    V_ATX_CLOCK_OUT
            
    V_ATX_DATE
      FROM 
            ATX

            
    EM
      WHERE 
           
    (ATX.ATX_EMKEY EM.EM_KEY) AND 
            
    ATX.ATX_DATE BETWEEN 
                sdate 
    AND edate 
      ORDER BY 
            ATX
    .ATX_DATE
            
    EM.EM_LAST_NAME;

    END;

    This may sound dumb but how do I call it then? I have tried:

    PHP Code:
    EXEC attendance('01-JUL-2003','15-JUL-2003'
    And I get the following error:

    PHP Code:
    BEGIN attendance('01-JUL-2003','15-JUL-2003'); END;

          *
    ERROR at line 1:
    ORA-06550line 1column 7:
    PLS-00306wrong number or types of arguments in call to 'ATTENDANCE'
    ORA-06550line 1column 7:
    PL/SQLStatement ignored 
    Thanks, Jeremy
    Nothing better than a good ride.

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    If you're trying to get the data out to crystal, you need to use a REF CURSOR.

    Ie (in a package spec)

    TYPE CurType is REF CURSOR;

    in a function in the package

    PROCEDURE Fred( Parm1 IN DATE, Parm2 IN DATE, curResults OUT CurType ) IS
    BEGIn
    OPEN curResult FOR SELECT ....
    END;

    In crystal...
    BEGIN
    MyPackage.Fred( date1, date2, cursortype );
    END;

    Google for "package ref cursor" for fuller syntax/details.

    Hth
    Bill

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    Do what Bill said.
    I hate Crystal reports.


    I thought Crystal does all this for you at the GUI level as you build the report?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I will but isn't there a way to query the sp we created and get data back instead of that error? Thanks, Jeremy
    Nothing better than a good ride.

  14. #14
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    My point here is that I'm learning and I was told that a stored procedure could benefit me in this situation because it allows me to run a query with variables that would be passed when calling the stored procedure. This sp would be ran from an application that I'm writing and it would be easiest if I could do something like:

    PHP Code:
    EXEC attendance('01-JUL-2003','15-JUL-2003'
    and have results returned. Thanks, Jeremy
    Nothing better than a good ride.

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by JCScoobyRS
    I will but isn't there a way to query the sp we created and get data back instead of that error? Thanks, Jeremy
    yes there is.
    If you use my code you could then do a DBMS_OUTPUT.put line

    set serveroutput on

    throw this line into your proc after the select statement, like this:
    PHP Code:
    dbms_output.put_line ('data  -->  '|| V_EM_KEY||'   '||V_EM_DPKEY||'   '||V_EM_TMKEY||'   '||V_EM_LAST_NAME||'   '||        V_EM_FIRST_NAME||'   '|| V_ATX_CLOCK_IN||'   '|| V_ATX_CLOCK_OUT||'   '||V_ATX_DATE); 
    Otherwise you could add error-handling to your SP.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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