Tag: development

Print the result of a cursor in Oracle PL/SQL

While developing Oracle PL/SQL code, sometimes one needs to debug via the old way, using DBMS_OUTPUT.PUT_LINE or inserting data into a table for a later inspection.
But when working with cursors, this is not an easy task, specially if one does not know the real output of the cursor when using dynamic SQL or simply because the cost of writing a specific output for each cursor one needs to evaluate is too high.

In order to aid this task, here’s a procedure that will print out the result of any cursor. By default, it will print the first 10 rows, but to print the entire result, just call the PRINT_CURSOR procedure with the v_maxRows = 0.

create or replace
PROCEDURE PRINT_CURSOR (
    p_refcursor IN OUT SYS_REFCURSOR, v_maxRows IN NUMBER default 10 )
AS
  v_desc DBMS_SQL.DESC_TAB;
  v_cols BINARY_INTEGER;
  v_cursor BINARY_INTEGER;
  v_varchar2 VARCHAR2(4000);
  v_number   NUMBER;
  v_date     DATE;
  v_data     VARCHAR2(32767);
  v_curRow   NUMBER;
BEGIN
  /* Convert refcursor "parameter" to DBMS_SQL cursor... */
  v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(p_refcursor);
  /* Describe the cursor... */
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
  /* Define columns to be fetched. We're only using V2, NUM, DATE for example... */
  FOR i IN 1 .. v_cols
  LOOP
    IF v_desc(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_number);
    ELSIF v_desc(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_date);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_varchar2, 4000);
    END IF;
  END LOOP;
  /* Now output the data, starting with header... */
  DBMS_OUTPUT.NEW_LINE;
  FOR i IN 1 .. v_cols
  LOOP
    v_data := v_data ||
    CASE v_desc(i).col_type
    WHEN 2 THEN
      LPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
    WHEN 12 THEN
      RPAD(v_desc(i).col_name, 22)
    ELSE
      RPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
    END || ' ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_data);
  v_data := NULL;
  FOR i IN 1 .. v_cols
  LOOP
    v_data := v_data ||
    CASE v_desc(i).col_type
    WHEN 2 THEN
      LPAD('-', v_desc(i).col_max_len+1, '-')
    WHEN 12 THEN
      RPAD('-', 22, '-')
    ELSE
      RPAD('-', v_desc(i).col_max_len+1, '-')
    END || ' ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_data);
  /* Fetch all data... */
  v_curRow := v_maxRows;
  WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
  LOOP
    v_data := NULL;
    FOR i IN 1 .. v_cols
    LOOP
      IF v_desc(i).col_type = 2 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_number);
        v_data                := v_data || LPAD(v_number, v_desc(i).col_max_len+1) || ' ';
      ELSIF v_desc(i).col_type = 12 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_date);
        v_data := v_data || RPAD(v_date, 22) || ' ';
      ELSE
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_varchar2);
        v_data := v_data || RPAD(v_varchar2, v_desc(i).col_max_len+1) || ' ';
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_data);
    if  v_maxRows <> 0 then
       v_curRow :=  v_curRow - 1;
       exit when 0 = v_curRow;
    end if;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;

Debug in R

Development in R usually is performed using JGR, or Tinn-R, editors and the R console.
Since these are no “state of the art” IDEs for R development, this is usually the best one can have.

So, how does one debug a program in R?
The usual answer is classical: with print.
But the correct answer is: using the browser() function.

The browser() function works as a break point marker.
Just write browser() where a break point is needed and run the program.
When browser() is interpreted, the program hang its execution and a new command line prompt will be provided, Browse[1]>.
This command line allows one to interact with the variables available in the scope where the program is temporary hanged.
This debug command line accepts the following debug commands:

  • n: next, advances to the next line, allows step-by-step.
  • c: continue, continues the execution, until the program ends or a new browser() function is found.
  • traceback(), shows the stack trace function call.
  • Q: quit, stop the program execution.

It does not look as cool as using the mouse to insert a break point on a line, but it works.