CODEWITHSUNDEEP
sqloracle-databaseplsql
w0051977
w0051977

Reputation: 15817

PLSQL return table set

I have a PL/SQL statement like this (it is not a stored procedure or function):

DECLARE
  curstatus VARCHAR(2);
  person_id varchar(6) := 100;
BEGIN
   SELECT cur_status INTO curstatus FROM Persons WHERE PID=cast(person_id as number(6));
   IF curstatus='F' THEN
    SELECT PID FROM Reports WHERE Report_Type='F' AND PID = person_id;
   END IF;
END;

I want to return all the PID from reports (display them to screen), however I receive an error: an INTO clause is expected.

Please note that this is not a stored procedure or function. How can I return the table?

Upvotes: 0

Views: 673

Answers (4)

DB_learner
DB_learner

Reputation: 1026

You cannot call a query directly from PL/SQL block because oracle uses two different engines for SQL and PL/SQL.

All the SQL queries gets executed in SQL Engine and all the PL/SQL operations are handled in PL/SQL Engine. And when you run a query inside PL/SQL block, the dataset has to be passed from sql to pl/sql engine. And in PL/SQL engine, it needs to be bind to a memory area as a variable or collection. For this binding process we need INTO clause. Once you declare a variable and bind data with INTO clause, it becomes available for PL/SQL engine to use the data.

Upvotes: 0

Colin 't Hart
Colin 't Hart

Reputation: 7729

Surely a pure SQL solution, something like the following, would work?

   SELECT r.PID
   FROM Reports r, Persons p
   WHERE r.Report_Type = 'F' AND r.PID = p.PID
   AND p.cur_status = 'F'
   AND p.PID = 100;

Upvotes: 4

Jacob
Jacob

Reputation: 14741

The reason for the error message is there is no into in second select statement.

DECLARE
  curstatus VARCHAR(16);
  person_id varchar(6) := 100;
  p_pid VARCHAR(32);
BEGIN
   SELECT cur_status INTO curstatus FROM Persons WHERE PID=cast(person_id as number(6));
   IF curstatus='F' THEN
    SELECT PID into p_pid FROM Reports WHERE Report_Type='F' AND PID = person_id;
   END IF;
END;

You can use a function which return sys_refcursor

E.g.

CREATE OR REPLACE FUNCTION testfunc
    RETURN SYS_REFCURSOR
AS
    curstatus   persons.cur_status%TYPE;
    person_id   VARCHAR (6) := 100;
    r_cursor    SYS_REFCURSOR;
BEGIN
    SELECT  cur_status
      INTO  curstatus
      FROM  persons
     WHERE  pid = CAST (person_id AS NUMBER (6));

    IF curstatus = 'F'
    THEN
        OPEN r_cursor FOR
            SELECT  pid
              INTO  p_pid
              FROM  reports
             WHERE  report_type = 'F' AND pid = person_id;
    END IF;

    RETURN r_cursor;
END;

Upvotes: 1

Srini V
Srini V

Reputation: 11375

Try this:

DECLARE
    CURSTATUS VARCHAR ( 2 );
    PERSON_ID VARCHAR ( 6 ) := 100;
    RESULTS REPORTS.PID%TYPE;
BEGIN
    SELECT
          CUR_STATUS
    INTO
          CURSTATUS
    FROM
          PERSONS
    WHERE
          PID = CAST ( PERSON_ID AS NUMBER ( 6 ) );

    IF CURSTATUS = 'F'
    THEN
        SELECT
              PID
        INTO
              RESULTS
        FROM
              REPORTS
        WHERE
              REPORT_TYPE = 'F'
              AND PID = PERSON_ID;
        DBMS_OUTPUT.PUT_LINE  ( 'Result is:'||RESULTS );
    END IF;

END;

Upvotes: 0

Related Questions