CODEWITHSUNDEEP
oracle-database
Baljeet
Baljeet

Reputation: 438

Error with LONG data type in cursor

I am trying to generate a spool file through anonymous block below in order to find out views on a particular table.

declare
cursor c1 is select view_name,text from users_view;
rt c1%rowtype;
begin
open c1;
loop
fetch c1 into rt;
exit when c1%notfound;
dbms_output.put_line(rt.view_name || '|' || rt.text);
end loop;
end;

When I run it, I get an error like "numeric or value errors", however if I remove text(LONG) column from cursor definition the block goes through without any error.

I understand that we can not use LONG data type in a where clause but is it that it can't be fetched in a cursor as well? If yes, what can be the alternative in this case?

Upvotes: 3

Views: 1124

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

In this case the error indicates that you've hit the buffer limit - dbms_output.put_line wont handle such large amount of data.

After looking at the problem more closely, it's not the dbms_output.put_line issue, not yet, it's the, as Alex Poole has pointed out in the comment to your question, it's the cursor problem. So I would suggest you to use simple Select statement(option #2 in the answer). If you go for a workaround

create table <<name>> as 
  select view_name
       , to_lob(text) 
   from user_views

for example, you will be able to use cursor but then dbms_output.put_line will stop you

To generate a spool file you have at least two options:

  1. Use UTL_FILE package to write data to a file.

  2. Let SQL*PLUS do the job. For example:

    set feedback off;
set termout off;
set colsep "|";
set long 20000;   -- increase the size if it's not enough
set heading off;
set linesize 150; -- increase the size if it's not enough


spool <<file path\file name>>

select view_name
     , text
  from user_views

spool off;

At the end you will have a similar output in your <<file path\file name>> file:

ALL_APPLY_CONFLICT_COLUMNS    |select c.object_owner, 
                              |       c.object_name,
                              |       c.method_name,
                              |       c.resolution_column, c.column_name,
                              |       c.apply_database_link
                              |  from all_tab_columns o, 
                              |       dba_apply_conflict_columns c
                              | where c.object_owner = o.owner
                              |   and c.object_name = o.table_name   
                              |   and c.column_name = o.column_name

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191255

Not directly addressing the long issue, but if you want to find out which views refer to a particular table, rather than searching through the view source you can query the data dictionary:

select owner, type, name
from all_dependencies
where referenced_type = 'TABLE'
and referenced_owner = user -- or a specific schema
and referenced_name = '<my_table_name>';

This will also list any triggers on the table, etc., so if you are only interested in view you can add and type = 'VIEW'.

Of course, this may just give you a smaller list of views to examine in more detail to see how the table is used by each one, but it's easier than searching all 300 of your views manually... and it might mean you don't need to get the text of the large views with more than 32k characters which are causing the long problem in the first place.

Upvotes: 2

Related Questions