Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Simple = Difficult?

    All I'm trying to do is create a script that will select a filtered list from a table:

    declare
    V_STATUS_REP_ID NUMBER;
    begin
    V_STATUS_REP_ID := 335;
    select N_STATUS_REP_ID,
    D_LAUNCH_DATE
    From ISGCOSDEV.TBL_CHIP_CHIP_STATUS_REPORT
    where N_STATUS_REP_ID = V_STATUS_REP_ID;
    end;

    I get a message stating that "an INTO clause is expected in this SELECT statement". Into what? I want to be able to put this code in a stored procedure to return a dataset.
    If it's not practically useful, then it's practically useless.

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

  2. #2
    Join Date
    May 2004
    Location
    alabama
    Posts
    5
    Declare a couple more variables to hold the two values retrieved by your select clause:

    select n_status_rep_id, d_launch_date
    into vSTATUS_VARIABLE, vLAUNCH_DATE_VARIABLE
    from....

    Inside a PL/SQL block you've got to 'select into' ...

    (where in ohio?)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Columbus.

    But how do I return those variables as a multi-record dataset to a calling procedure such as Crystal Reports? Do I have to use a cursor?
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is anonymous pl/sql procedure which can not be referenced from another session. If you want to return fetched data into a calling procedure, you'll have to write function (returns only one value) or stored procedure with OUT parameters (can return more than one value).

    For example, use par_rep_id as a parameter and return launch date:
    Code:
    create or replace procedure my_proc
      (par_rep_id IN number, par_date OUT date)
      is
      l_date date;
    begin
      select d_launch_date
        into l_date
        from tbl_status_report
        where n_status_rep_id = par_rep_id;
      par_date := l_date;
    end;
    /
    How to use it? Calling procedure can be anonymous block, such as this one:
    Code:
    declare
      p_date date;
    begin
      my_proc(335, p_date);
      dbms_output.put_line('Return value is ' || p_date);
    end;
    /
    I hope I didn't make too many mistakes in code

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That will only work for scalar values, not a multi-record dataset.

    I hate to say this, but this is really simple to do using TSQL:

    create procedure MyProc(@N_STATUS_REP_ID int)
    as
    begin
    select N_STATUS_REP_ID, D_LAUNCH_DATE
    from TBL_CHIP_CHIP_STATUS_REPORT
    where N_STATUS_REP_ID = @N_STATUS_REP_ID
    end

    ...and callling it is as easy as:
    EXEC MyProc(335)

    I've been learning Oracle for about a month now, and it is not impressing me.

    This is about as close as I can get:

    SET SERVEROUTPUT ON;
    CREATE OR REPLACE PROCEDURE SP_CHIP_STATUS_REPORT(STATUS_REP_ID IN NUMBER) IS
    DECLARE
    CURSOR RECORD_CURSOR IS
    SELECT N_STATUS_REP_ID, N_PHASE_ID
    FROM TBL_CHIP_CHIP_STATUS_REPORT
    WHERE N_STATUS_REP_ID = STATUS_REP_ID;

    BEGIN
    FOR STATUS_RECORD IN RECORD_CURSOR LOOP
    DBMS_OUTPUT.PUT_LINE(STATUS_RECORD.N_STATUS_REP_ID || ', ' || STATUS_RECORD.N_PHASE_ID);
    END LOOP;

    END;
    /

    ...but I still get compilation errors.

    I mean, come on! This is basic stuff that ought to be easy to do in ay RDBMS.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Your procedure seems to be OK, just omit DECLARE statement - it is not allowed here.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Glad to hear I'm close, but don't I have to declare the cursor?
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Check out this link, it is very helpfull if you want to return resulset based on either a ref cursor or a pl/sql table.

    http://asktom.oracle.com/pls/ask/f?p...A:246014735810

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by blindman
    Glad to hear I'm close, but don't I have to declare the cursor?
    "DECLARE" must exist in an anonymous pl/sql block. But, declaring variables, cursors, exceptions, etc. in packages, procedures, functions, is done WITHOUT "DECLARE" statement.

    This is anonymous block.
    Code:
    declare
      cursor emp_cur is select ename from emp;
    begin
      for emp_rec in emp_cur loop
        dmbs_output.put_line(emp_rec.ename);
      end loop;
    end;
    /
    But, if you put this into a procedure, it will be:
    Code:
    create or replace procedure my_proc
    is
      /* You don't have DECLARE here */
      cursor emp_cur is select ename from emp;
    begin
      for emp_rec in emp_cur loop
        dmbs_output.put_line(emp_rec.ename);
      end loop;
    end;
    /
    I didn't follow Martineze's link; I hope it helped you. Just wanted to explain use of DECLARE ...

Posting Permissions

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