You Asked

With PHP you have the print_r function, useful to print a row of a loop, array's, etc.
Does PLSQL offer some sort of replacement?

Note: I'm not allowed to combine PLSQL with PHP.

Thanks.

and Chris said...

You can use dbms_output.put_line to display text. This doesn't accept arrays though:

SQL> declare
  2     arr dbms_sql.number_table;
  3  begin
  4    arr(1) := 1;
  5     arr(2) := 2;
  6    dbms_output.put_line(arr);
  7  end;
  8  /
  dbms_output.put_line(arr);
  *
ERROR at line 6:
ORA-06550: line 6, column 3:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

To show the contents of an array, loop through it printing the contents one-by-one:

SQL> declare
  2     arr dbms_sql.number_table;
  3  begin
  4    arr(1) := 1;
  5     arr(2) := 2;
  6     for i in 1 .. 2 loop
  7      dbms_output.put_line(arr(i));
  8     end loop;
  9  end;
 10  /
1
2

Similarly, to display the fields of a record type you need to explicitly state each one.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Does it also work for cursors?

Francois M, April 14, 2016 - 9:33 am UTC

Thanks for your reply.

Is it possible to use approach the same for cursors?
I'm aware of keys in array's, but this isn't the case for cursors.

DECLARE
  r_table the_db_table%rowtype;
  
  cursor c_table is
  select * from table;
BEGIN
  for r_table in c_table loop
    for r in <row.first>..<row.last> loop
      dbms_output.put_line(..)
    end loop;
  end loop;
END;
/

Many thanks.

April 14, 2016 - 10:32 am UTC

You can use a similar method for cursors. The cursor loop (for r_table in c_table) fetches all the rows from it. You can remove the inner loop.

To print the rows, you need to state the columns in the call to put_line:

DECLARE
  cursor c_table is
    select 1 n, 'a' c from dual
    union all
    select 2 n, 'b' c from dual;
BEGIN
  for r_table in c_table loop
    dbms_output.put_line('n: ' || r_table.n || ' c: ' || r_table.c);
  end loop;
END;
/

n: 1 c: a
n: 2 c: b

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Does it also work for cursors?

More to Explore

DBMS_OUTPUT

PHP's print_r(row) equivalent in PL/SQL

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Does it also work for cursors?

More to Explore

DBMS_OUTPUT