PLSQL CASE WHEN CONDITION
15,361
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;
Related videos on Youtube
Author by
Arav
Updated on June 04, 2022Comments
-
Arav almost 2 years
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;
-
Arav about 13 yearsThanks a lot. In case if i want to spool a select query do i need to have a dbms_output of test1,test2?
-
Jeffrey Kemp about 13 yearsIf you're running this in SQL*Plus, you could use dbms_output; you just need to call
SET SERVEROUT ON
before running the block. -
Arav about 13 yearsSo is it a rule in PLSQL without into clause select statements can't be used?
-
Jeffrey Kemp about 13 yearsNo, 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 about 13 yearsThink about it: the result of a SQL query is some data. If you run SQL by itself in a SQLPlus session, SQLPlus 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.