PLSQL Store cursor with unknown data type in a variable (using FETCH INTO)

Question

I am new to PLSQL and trying to accomplish the following: loop through all columns in the user's schema and output unique values for each column.
I am attempting this with a nested cursor, the initial cursor being each column and the nested cursor being the unique values for each column. The problem I am having appears to be that the nested cursor has values of various types (depending on the column) and is unable to be placed INTO a variable defined as varchar2. This question suggests that this should work, and that date and numeric vars will be converted to characters implicitly. However, I can't seem to get that to work, with my code producing the following error:

ORA-06502: PL/SQL: numeric or value error.

I have tried (unsuccessfully) forcing the cursor to be a character variable by using to_char():
'FETCH TO_CHAR(row_cursor) INTO...'
Which also does not work.

Is there a way to store an unknown type of data so that it can be output? Is there a better way to approach listing unique values for all columns in a schema?

EDIT: Based on @Kaushik-Nayak 's comment, I took a deeper look at some of the columns/tables that were being processed. Some of the internal Oracle views appear to have deprecated types (I began to see a lot of ORA-00932: inconsistent datatypes: expected CHAR got LONG) errors (which led me to this question). To get around that, I prefixed all of the tables I wanted analyzed with a common prefix (in this case 'MY_') and added a WHERE clause with an assist from the SUBSTR() function to get only columns from tables with the common prefix from the user_tab_columns table. This change resolved the problem:

DECLARE
  row_cursor SYS_REFCURSOR;
  var_rowval VARCHAR2(4000);

BEGIN
  FOR c IN (SELECT table_name, column_name FROM user_tab_columns WHERE SUBSTR(table_name, 1, 2) = 'MY') 
  LOOP

Here is my (original) code:

DECLARE
  row_cursor SYS_REFCURSOR;
  var_rowval VARCHAR2(500);

BEGIN
  FOR c IN (SELECT table_name, column_name FROM user_tab_columns) 
  LOOP
      OPEN row_cursor
        FOR 'SELECT DISTINCT ' || c.column_name || ' FROM ' || c.table_name;
          LOOP
            FETCH row_cursor INTO var_rowval;
            dbms_output.put_line(c.table_name || ', ' || c.column_name || ': ' || var_rowval );
            EXIT WHEN row_cursor%NOTFOUND;
          END LOOP;
      CLOSE row_cursor;
  END LOOP;      
END;

Problem is not with the data type but with the size. You probably have a column with value whose size is greater than 500.You must keep size of var_rowval sufficiently large(maybe 4000) to accommodate all types of columns .However, you may still face issue if you have CLOB columns. So, it is better to redesign your code to avoid such errors. — Kaushik Nayak
– Kaushik Nayak, Commented Nov 13, 2017 at 5:00
Not relevant to question but you cannot use a SYS_REFCURSOR the way you are using. Correct that at first place. — XING
– XING, Commented Nov 13, 2017 at 8:22

Ankit Mongia · Accepted Answer · 2017-11-28 13:07:26Z

I ran you code on my schema and it worked fine without any errors. My table has columns of data type varchar2,Number and Date. As told by @Kaushik Nayak please increase the size of your variable "var_rowval(500)" to "var_rowval(4000)".

One more suggestion please do not run this code for all the tables as when you are printing it using dbms_output.put_line() you will get buffer overflow error. For understanding you can take up 2 to 3 tables and try to print distinct values of there columns.

Sill of you want to find the column that is creating the issue, below is the code that will help you in achieving that.

    set serveroutput on      
DECLARE
  row_cursor SYS_REFCURSOR;
  var_rowval VARCHAR2(500);

BEGIN
  FOR c IN (SELECT * /*table_name, column_name*/ FROM user_tab_columns) 
  LOOP
  begin
  OPEN row_cursor
        FOR 'SELECT DISTINCT ' || c.column_name || ' FROM ' || c.table_name;
          LOOP

            FETCH row_cursor INTO var_rowval;
            dbms_output.put_line(c.table_name || ', ' || c.column_name || ': ' || var_rowval );
            EXIT WHEN row_cursor%NOTFOUND;
          END LOOP;
      CLOSE row_cursor;
           exception
            when others then 
           dbms_output.put_line('Table Name:'||' '||c.table_name||' '||'column name'||c.column_name||','||'data type:'||' '||c.data_type);
            EXIT;
            end;  

  END LOOP;      
END;
/

Collectives™ on Stack Overflow

PLSQL Store cursor with unknown data type in a variable (using FETCH INTO)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related