Hi,

I have to transfer data of ALL_VIEWS from one database to another database using DBLink. Both are Oracle databases and versions are also same i.e. Oracle 18c. There are few views for which the text column length is more than 32767 which can not be transferred using native dynamic sql. Hence i am using DBMS_SQL to achieve the same.

I am trying to extract the long column value from ALL_VIEW@<DB_LINK> using DBMS_SQL.COLUMN_VALUE_LONG and inserting the results in CLOB column. Below is the PLSQL block i am using. (Please replace the db_link in with working DB Link)

CREATE TABLE san_ora03101_test
( resource_name VARCHAR2 (100),
  view_owner    VARCHAR2(100),
  view_name   VARCHAR2(100),
  text      CLOB
);
/

DECLARE

    s_sql      CLOB:= q'[SELECT *
                          FROM (
                                SELECT v.view_name resource_name
                                     , v.owner        view_owner
                                     , v.view_name
                                     , v.text text
                                  FROM all_views@REPORTING_SYSTEM v)]'; 
    cur        BINARY_INTEGER;
    s_res_name VARCHAR2(100);
    s_owner    VARCHAR2(100);
    s_vname    VARCHAR2(100);
    c_text     CLOB;
    n_res      NUMBER;
BEGIN      

        cur:=DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cur, s_sql, DBMS_SQL.NATIVE);
        DBMS_SQL.DEFINE_COLUMN(cur, 1, s_res_name, 100);
        DBMS_SQL.DEFINE_COLUMN(cur, 2, s_owner, 100);
        DBMS_SQL.DEFINE_COLUMN(cur, 3, s_vname, 100);
        DBMS_SQL.DEFINE_COLUMN_LONG(cur, 4);

        n_res:=DBMS_SQL.EXECUTE(cur);
        WHILE DBMS_SQL.FETCH_ROWS(cur) > 0 LOOP
            DBMS_SQL.COLUMN_VALUE(cur, 1, s_res_name);
            DBMS_SQL.COLUMN_VALUE(cur, 2, s_owner);
            DBMS_SQL.COLUMN_VALUE(cur, 3, s_vname);
            DECLARE
                c_tmp   CLOB;
                n_pos   INTEGER:=0;
                s_tmp   VARCHAR2(32767);
                n_tpos  INTEGER;
                n_piece_len NUMBER:= 32767;
            BEGIN
                LOOP
                    dbms_output.put_line(' n_pos: '||n_pos||',s_tmp: '||s_tmp||',n_tpos: '||n_tpos||',cur: '||cur||', s_res_name: '||s_res_name||',s_owner: '||s_owner||',s_vname: '||s_vname);
                    
          DBMS_SQL.COLUMN_VALUE_LONG(cur, 4, n_piece_len, n_pos, s_tmp, n_tpos);
                        
                    c_tmp:=c_tmp||s_tmp;
                    n_pos:=n_pos+n_piece_len;
                    EXIT WHEN n_tpos < n_piece_len;
                END LOOP;
                c_text:=c_tmp;
                
            EXCEPTION
                WHEN OTHERS THEN
                  dbms_output.put_line(' ERR_STACK: '||dbms_utility.format_error_stack||', SQL_TRACE: '||dbms_utility.format_error_backtrace);
                RAISE;
            END;
            
        -- INSERT RECORDS
        INSERT
          INTO san_ora03101_test
             (
               resource_name
             , view_owner
             , view_name
             , text
             )
        VALUES
             (
               s_res_name
             , s_owner
             , s_vname
             , c_text
             );

        END LOOP;
        DBMS_SQL.CLOSE_CURSOR(cur);
        COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(cur) THEN
            DBMS_SQL.CLOSE_CURSOR(cur);
        END IF;
        RAISE;
END;
/

Above block is failing with "ORA-03101: invalid input data for" while trying to fetch LONG column value using DBMS_SQL.COLUMN_VALUE_LONG().

ORA-03101: invalid <input data for
ORA-06512: at line 79
ORA-06512: at line 51
ORA-02063: preceding line from REPORTING_SYSTEM
ORA-06512: at "SYS.DBMS_SQL", line 2066
ORA-06512: at line 40

Oracle Database Version is : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0. However, this statement work fine in 11g environments. If we remove the db_link then also this would work fine.

SQL> create table local_all_views
  2  ( owner varchar2(30), view_name varchar2(128), text_length int, text long);

Table created.

SQL>
SQL> set long 1000000
SQL> copy from scott/tiger@remote_db -
>   insert local_all_views (owner, view_name, text_length, text ) -
>   using select owner, view_name, text_length, text from all_views;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 1000000. (long is 1000000)
   7249 rows selected from scott@remote_db.
   7249 rows inserted into LOCAL_ALL_VIEWS.
   7249 rows committed into LOCAL_ALL_VIEWS at DEFAULT HOST connection.

SQL>
SQL> alter table local_all_views modify text clob;

Table altered.

SQL> desc local_all_views
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 VIEW_NAME                                          VARCHAR2(128)
 TEXT_LENGTH                                        NUMBER(38)
 TEXT                                               CLOB