ORA-06502 PL/SQL numeric or value error utl_file.put_line()

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line XX

Bu hatayı utl_file.put_line  paketi ile db link kullanarak remote database den aldığım kodun yazımında yaşadım..

SQL> declare

2    l_clob clob;

3    l_tmp long;

4    l_offset number := 1;

5    lFile UTL_FILE.file_type;

6   begin

7     loop

8      select dbms_lob.substr@DB_TESTDB(dbms_metadata.get_ddl@DB_TESTDB('PACKAGE BODY','ILKER','PKG_ADMIN'), 4000, l_offset )

9      into l_tmp

10     from dual@DB_TESTDB;

11

12     exit when l_tmp is null;

13     l_clob := l_clob || l_tmp;

14     l_offset := l_offset + length(l_tmp);

15    end loop;

16   lFile := UTL_FILE.fopen('EXPORT_DIR', 'createscr.sql','w');

17   UTL_FILE.put(lFile, l_clob);

18   UTL_FILE.fclose(lFile);

19 -- dbms_output.put_line( 'l_clob length is ' || length(l_clob) );

20 end;

21 /

*

ERROR at line 1:

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

ORA-06512: at line 17

Normal şartlarda ORA-06502: PL/SQL: numeric or value error hatası için farklı düşünebiliriz. Fakat utl_file için bu hata veriliyorsa durum biraz farklılaşıyor.

Bunun sebebi UTL_FILE.PUT_LINE paketinin maximum alabileceği değer 32767 bytes Bknz https://docs.oracle.com/utl_file

Eğer çektiğiniz datanın boyutuna bakarsanız bu değerden büyük olduğunu göreceksiniz.

Bu sorunu ben DBMS_XSLPROCESSOR paketi ve CLOB2FILE procedure kullanarak aştım.

SQL> declare

2     l_clob clob;

3     l_tmp long;

4     l_offset number := 1;

5     lFile UTL_FILE.file_type;

6   begin

7    loop

8      select dbms_lob.substr@DB_TESTDB(dbms_metadata.get_ddl@DB_TESTDB('PACKAGE BODY','ILKER','PKG_ADMIN'), 4000, l_offset )

9      into l_tmp

10     from dual@DB_TESTDB;

11

12    exit when l_tmp is null;

13    l_clob := l_clob || l_tmp;

14    l_offset := l_offset + length(l_tmp);

15   end loop;

16 -- lFile := UTL_FILE.fopen('EXPORT_DIR', 'createscr.sql','w');

17 -- UTL_FILE.put(lFile, l_clob);

18 -- UTL_FILE.fclose(lFile);

19 -- dbms_output.put_line( 'l_clob length is ' || length(l_clob) );

20 DBMS_XSLPROCESSOR.clob2file(l_clob,'EXPORT_DIR','createscr1.sql';

21 end;

22 /

Buna dair farklı bir kaydı support üzerinden de  inceleyebilir siniz. 

Insertinga clob with length greater than 8192 with utl_file.put(); getsORA-06502 (Doc ID 760144.1)

ORA-22992: cannot use LOB locators selected from remote tables

Ben bu hatayı remote db üzerinden, DDL scriptini almak istediğimde yaşadım.

Buda Remote db üzerinden CLOB bir datayı alamayacağım anlamına geliyor

SELECT DBMS_METADATA.GET_DDL@DB_TESTDB('TABLE','T','ILKER') FROM DUAL@DB_TESTDB;

ERROR:

ORA-22992: cannot use LOB locators selected from remote tables

no rows selected

SQL>

Workaround olarak substring'e çevirerek bir çözüm sağlayabilir siniz.

Select DBMS_LOB.SUBSTR@DB_TESTDB(DBMS_METADATA.GET_DDL@DB_TESTDB('TABLE','T','ILKER'),4000,1) from dual@DB_TESTDB;

ORA-06553 PLS-564 DB link CLOB Column ERROR

ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server

Bu hatayı clob içeren DDL 'i Veritabanımdan db Link ile remote Veritabanı üzerinden almak isterken yaşadım.

insert into LocalTempTableWithClob1 SELECT DBMS_METADATA.GET_DDL@DB_TESTDB('PACKAGE BODY', 'PKG_ADMIN_DBA', 'ILKER') FROM DUAL@DB_TESTDB;

*ERROR at line 1:ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server

Başka bir örnek:

create table LocalTempTableGetDDL as SELECT DBMS_METADATA.GET_DDL@DB_TESTDB('PACKAGE BODY', 'PKG_ADMIN_DBA', 'ILKER') FROM DUAL@DB_TESTDB;

*

ERROR at line 1:

ORA-06553: PLS-564: lob arguments are not permitted in calls to remote server

DB link ile remote veri tabanı üzerinde yapabileceklerimizin sınırlı. Bazı limitleri var. Bu şekilde bir seferde ancak 4000 bayt alabileceğimiz için workaround olarak bir loop içerisinde tekrar tekrar almayı deneyebilirsiniz.

declare

    l_clob clob;

    l_tmp long;

    l_offset number := 1;

begin

    loop

      select dbms_lob.substr@DB_TESTDB(DBMS_METADATA.GET_DDL@DB_TESTDB('ILKER','PKG_ADMIN_DBA','PACKAGE BODY'), 4000, l_offset )

      into l_tmp

      from dual@DB_TESTDB;

   exit when l_tmp is null;

   l_clob := l_clob || l_tmp;

   l_offset := l_offset + length(l_tmp);

  end loop;

 dbms_output.put_line( 'l_clob length is ' || length(l_clob) );

end;

/

Burada dikkat edilmesi gereken konu tekrar tekrar gidilmesi gerektiği. Her ortam ve DB için uygun olmayabilir. 

ORA-04052 Remote query fail

Bu hatayı db link ile remote db den bir distributed query çekmek isterken aldım.

SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE, os.source_size
  FROM dba_objects@db_link o, dba_object_size@db_link os
   WHERE o.OWNER=os.owner
       and o.OBJECT_NAME = os.name
       and o.OBJECT_TYPE = os.type
       and o.object_name = upper(p_OBJECT_NAME)
   

LINE/COL ERROR
-----------------------------------------------------------------
ORA-04052: error occurred when looking up remote object
SYS.IDL_UB2$@DB_LINK
ORA-00604: error occurred at recursive SQL level 1
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-201: identifier 'UNDEFINED' must be decl

workarounds olarak bir çözüm üretmek gerekirse

1-) Local db üzerinden bir view yaratabiliriz.
On local
Create or replace view remote_obje
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE, os.source_size
  FROM dba_objects@db_link o,dba_object_size@db_link os
   WHERE o.OWNER=os.owner
       and o.OBJECT_NAME = os.name
       and o.OBJECT_TYPE = os.type
       and o.object_name = upper(p_OBJECT_NAME);

2-)Ya da remote db üzerinde bu view yaratıp sorgumuzu yapabiliriz. 
on destination

Create or replace view remote_obje 
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE, os.source_size
   FROM dba_objects o, dba_object_size os
   WHERE o.OWNER=os.owner
       and o.OBJECT_NAME = os.name
       and o.OBJECT_TYPE = os.type
       and o.object_name = upper(p_OBJECT_NAME);