display select results inside anonymous block
Solution 1
You can try with this, to print your result easily:
declare
your_variable varchar2(19);
BEGIN
DBMS_OUTPUT.PUT_LINE('init..');
FOR x IN (SELECT your_column
FROM you_table
where rownum<2
order by 1)
LOOP
DBMS_OUTPUT.PUT_LINE(x.your_column);
END LOOP;
END;
Solution 2
In order to return the value of the select it needs to be selected into a container (a reference cursor or REF CURSOR). In your Declare you should include ref_cursor_out SYS_REFCURSOR;
and change your select to:
select * into ref_cursor_out ...
In SQL Developer there is an option (I am a Toad user, so I forget where in SD) that tells the IDE to load the result set into a grid to view.
[edit: per comment from @DCookie, Thanks for the catch!]
Solution 3
For oracle 12c or higher
declare
rfc sys_refcursor;
begin
open rfc for select * from table;
dbms_sql.return_result(rfc);
end;
Pascal
Updated on March 02, 2021Comments
-
Pascal over 3 years
I'm trying to debug a SELECT inside a procedure, and I'm trying to this using a anonymous block. I would like that SQL Developer simply return the last SELECT statement, but I get the error:
ORA-06550: line 21, column 5: PLS-00428: an INTO clause is expected in this SELECT statement
Inside the procedure, I have an INTO for that select, but is there a simple way that I can simply get the results for the last SELECT statement for my debugging? I'm using anonymous block and variables so that the code is as similar as possible from what's actually inside the procedure, so that I don't have to change the code
set serveroutput on format wrapped; DECLARE p_cd_doc_type number; p_dc_doc_code varchar2(200); p_dt_base date; p_qt_days number; p_vl_mov_total number; p_qt_transac number; v_dt_max date; v_dt_min date; begin p_dt_base := sysdate; p_qt_days := 1; v_dt_max := trunc(p_dt_base) + 1; v_dt_min := v_dt_max - p_qt_days; p_vl_mov_total := 0; DBMS_OUTPUT.PUT_LINE('v_dt_max = ' || v_dt_max); DBMS_OUTPUT.PUT_LINE('v_dt_min = ' || v_dt_min); select * from tb_cad_cliente a join tb_trn_transacao b on a.cd_cliente = b.cd_cliente where a.cd_doc_type = p_cd_doc_type and a.dc_doc_code = p_dc_doc_code and b.dt_row between v_dt_min and v_dt_max and b.cd_status = 3; end;
-
Pascal about 13 yearsIf i add a tblresult REF CURSOR; before the begin, I get a PLS-00201: identifier 'CURSOR' must be declared
-
DCookie about 13 yearsYour declaration of ref_cursor_out should be "ref_cursor_out SYS_REFCURSOR;"
-
Pascal about 13 yearsTks for the info. I'm aware of all that, and I did more than an hour of searching, and I couldn't find a simple way to solve my problem without creating types. After all the investigation, I still couldn't find a simple way of solving this. Maybe there is no simple way, and that's the answer. I simply don't know, even after searching. The INTO works great in my proc. Just need to know how to do it in an anonymous block, and display it on SQL Developer
-
Pascal about 13 yearsTks @DCookie. it compiles and all... I use the open tblresult for select * from (...) . I've been searching and digging... how do I print the tblresult? 100% samples are results from procs. NONE regarding anonymous blocks.. :-/
-
zep about 13 yearsThis answer can give you an idea: stackoverflow.com/questions/6265160/…