2

I have two queries.

Query 1.

Below PL/SQL is not working. I want to store the output into varable test1 and test2. It's saying ORA-00923: FROM keyword not found. Not sure what is wrong

DECLARE    
  file_id NUMBER(10) NOT NULL :=5;
  test1   varchar(100);
  test2   varchar(100);

BEGIN

  DBMS_OUTPUT.PUT_LINE('File Id: ' || file_id);

  SELECT table_name 
    INTO test1,
         (CASE owner
            WHEN 'SYS' THEN  'The owner is SYS'
            WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
          END) 
    INTO test2 
    FROM all_tables 
   WHERE rownum <= 1;

END;

Query 2.

In PL/SQL if i just use the select statement without into clause it's not working. Is it a rule that i need to use into clause. The below one does not work. If i want to spool a output from PL/SQL program do i need to store the output of column into the variable and do a dbms_output?

DECLARE

  file_id  NUMBER(10) NOT NULL :=5;
  test1 varchar(100);
  test2 varchar(100);

BEGIN

  DBMS_OUTPUT.PUT_LINE('File Id: ' || file_id);

  SELECT table_name,
         CASE owner
           WHEN 'SYS' THEN 'The owner is SYS'
           WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
         END
    FROM all_tables;

END;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Arav
  • 4,957
  • 23
  • 77
  • 123

1 Answers1

7

You only need one INTO clause in a PL/SQL query, for example:

SELECT table_name, CASE owner WHEN bla bla ... END
INTO test1, test2
FROM all_tables;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Thanks a lot. In case if i want to spool a select query do i need to have a dbms_output of test1,test2? – Arav Apr 06 '11 at 01:48
  • If you're running this in SQL*Plus, you could use dbms_output; you just need to call `SET SERVEROUT ON` before running the block. – Jeffrey Kemp Apr 06 '11 at 03:39
  • So is it a rule in PLSQL without into clause select statements can't be used? – Arav Apr 06 '11 at 05:28
  • 1
    No, just in the example you gave. The other way a SQL statement may be embedded in PL/SQL is in a CURSOR definition - no INTO clause required - but then you have the INTO when you do a FETCH from it. – Jeffrey Kemp Apr 06 '11 at 07:25
  • 2
    Think about it: the result of a SQL query is some data. If you run SQL by itself in a SQL*Plus session, SQL*Plus takes the results and shows them on the screen. Within a PL/SQL block, you need to explicitly define where it should put the results. – Jeffrey Kemp Apr 06 '11 at 07:26