0

I'm trying to exexcute a pl/sql block using dbms_output to obtain a console output but without reason the unique answer by SQL developer is 'Anonymous block finished'.

declare
    v_count integer;
    lines dbms_output.chararr;
    num_lines number;
 begin
    dbms_output.ENABLE;
    num_lines := 0;

    for r in (select table_name, owner from all_tables
              where owner = 'SYSAME'
              and table_name like 'SYZ%'
              and table_name not in (select distinct table_name from all_tab_columns
                                      where OWNER = 'SYSAME'
                                      and table_name like '%SYZ%'
                                      and column_name not like '%FL_IDE_FILIALE%')) 

    loop
        execute immediate 'select count(*) from ' || r.table_name || ' where fl_ide_filiale = 12' 
            into v_count;
        dbms_output.Put_line(r.table_name ||';'|| r.owner ||';'|| v_count ||';'|| SYSDATE );    
        num_lines := num_lines + 1; 
    end loop;

    dbms_output.get_lines(lines, num_lines);
    dbms_output.Put_line(num_lines);

    FOR i IN 1..num_lines LOOP
      dbms_output.Put_line(lines(i));
    END LOOP; 

end;

Any idea why i don't obtain any output ?

Q2: is there any way to plot the output not with a string line and in a table-like way. In this case someting like a select with this values : r.table_name, r.owner, v_count, SYSDATE

1

Aswering Question2:

You can't show the result like Sql Server when you build an anonymous block in PL-SQL. You have to do it using procedure or function. ps: function must return a sys_refcursor.

But hey, I had similar problem... What I've done for this case was to make a stored procedure to pass a string of values and show it like a table using simple math and drawing techniques. (I don't have the stored procedure, it was from my previous work)

1

You must run SET SERVEROUTPUT ON before you execute the PL/SQL script.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.