CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Donny
Donny

Reputation: 559

how to dynamically copy a table with LONG datatype in dynamic sql?

I'm about to learn pl/sql and currently I'm not understanding whats going wrong with my code. What I'm trying to do is to dynamically copy(backup) a specific table. So easy thing: I already created a backupTable, because I will use that quite often actually. So the first try was following:

EXECUTE IMMEDIATE 'INSERT INTO '||sSchema_||'.backupTable
  SELECT * FROM '||sSchema_||'.table'

This doesnt work as one of the columns contains LONG datatype 

Exception ORA-00997: illegal use of LONG datatype

So the next step was trying to pack the thing into a loop and fetch each row individually:

--Initialized as
TYPE cur_typ IS REF CURSOR;
cCursor cur_typ;
rRecord table%rowtype;
--Make sure cursor is closed
IF cCursor%ISOPEN THEN
  CLOSE cCursor;
END IF;
--Run the copying
OPEN cCursor FOR 'SELECT * FROM '||sSchema_||'.table';
LOOP
  FETCH cCursor INTO rRecord;
  EXIT WHEN cCursor%NOTFOUND;
  EXECUTE IMMEDIATE 'INSERT INTO '||sSchema_||'.updateTable 'VALUES rRecord';
END LOOP;
CLOSE cCursor;

Which is not being executed due to:

ORA-03001: unimplemented feature

After that I tried to use different other ways to write that loop e.g.

  EXECUTE IMMEDIATE 'INSERT INTO '||sSchema_||'.updateTable 'VALUES :1' USING rRecord;

All with the same result: unimplemented feature.

So here comes the question: How do I create a dynamic copy of tables containg LONG datatype? Does anyone has any idea?

Thanks a lot in advance

donny

Upvotes: 4

Views: 14917

Answers (1)

the_slk
the_slk

Reputation: 2182

The target table should be using a LOB (CLOB or BLOB) type.

The LONG RAW datatype is provided for backward compatibility with existing applications. For new applications, use the BLOB and BFILE datatypes for large amounts of binary data.

Oracle also recommends that you convert existing LONG RAW columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG RAW functionality has been static for several releases.

Source: Oracle Database Concepts

CREATE TABLE a_table
(
    long_col LONG
);


CREATE TABLE a_backupTable
(
    clob_col VARCHAR2(4000)
);


INSERT INTO a_table VALUES ('a');
-- 1 rows inserted.

DECLARE
    l_cur   SYS_REFCURSOR;
    l_long  LONG;
BEGIN
    OPEN l_cur FOR SELECT long_col FROM a_table;
    LOOP
        FETCH l_cur INTO l_long;
        EXIT WHEN l_cur%NOTFOUND;

        INSERT INTO a_backupTable VALUES(l_long);
    END LOOP;
    CLOSE l_cur;

    COMMIT;
END;
-- anonymous block completed

SELECT * FROM a_backupTable;
-- a

Upvotes: 2

Related Questions