CODEWITHSUNDEEP
sqloracle-databaseplsql
user6119513
user6119513

Reputation:

Alternatives to DBMS_OUTPUT in plsql

I am trying to find a way to capture output from an sql statement. The debugger I would like to use does not support DBMS_OUTPUT, so that is not an option. This is also a live database, so creating a new table or procedure is out of the question. Any ideas?

declare
  type NumberArray is array(100) of clock_in_out.pr_emp_id%Type;
  type DateArray is array(1000) of date;
  emps NumberArray;
  times DateArray;

BEGIN
  select unique pr_emp_id
  BULK COLLECT INTO emps
  from clock_in_out;

  FOR i IN emps.FIRST .. emps.LAST
  LOOP
    select time_in_out
    BULK COLLECT INTO times
    from clock_in_out
    where pr_emp_id = emps(i)
    order by time_in_out;

    FOR j IN 2 .. times.COUNT LOOP

      IF ( 86400 * ( times(j) - times(j-1) )) <= 5 THEN
        DBMS_OUTPUT.PUT_LINE('Result Found' );
        DBMS_OUTPUT.PUT_LINE(emps(i) );
        DBMS_OUTPUT.PUT_LINE( times(j) );
        DBMS_OUTPUT.PUT_LINE( 86400 * ( times(j) - times(j-1) ));
        DBMS_OUTPUT.PUT_LINE(' ');
      END IF;

    END LOOP;
  END LOOP;
END;
/

Upvotes: 2

Views: 2725

Answers (3)

hogi
hogi

Reputation: 896

In SQL-Developer you may want to add

set serveroutput on;

Upvotes: 0

oratom
oratom

Reputation: 291

Hm, without chance of creating a table, or a (table-)function, actually i only see following options:

1) if it´s applicable to have a volatile output and your debugger can show session-info, then user DBMS_APPLICATION_INFO.set_action(action_name => 'up to 32 characters'); to display the output

2) if you have (write-)access to a directory, you can use the utl_file - package (fopen, put_line, close) to write your output to a logfile

3) you might use utl_tcp - package to send the output to a listenig application on your client (open_connection, write_text, close_connection), but that requires ACL-settings which allow you establish such connections

Upvotes: 1

user2058738
user2058738

Reputation: 369

  1. Create a logging table in another database, preferably test db.
  2. Create db_link
  3. Insert log into logging table.

Problem solved. You did not create any objects in your live database. Seriously tho, switch debugger. Oracle SQL Developer is free and supports dbms_output.

Upvotes: 0

Related Questions