Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00997: illegal use of LONG datatype (Oracle9i)
ORA-00997: illegal use of LONG datatype [message #357541] Wed, 05 November 2008 17:04 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
SQL> create table bkp_plan_table
  2  as
  3  select * from plan_table;
select * from plan_table
       *
ERROR at line 3:
ORA-00997: illegal use of LONG datatype


We cant copy data because of long data type it has. can I make a back up of the table?


Regards
Oli
Re: ORA-00997: illegal use of LONG datatype [message #357614 is a reply to message #357541] Thu, 06 November 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't in SQL this is one of the reasons you have switch to CLOB.

Regards
Michel
Re: ORA-00997: illegal use of LONG datatype [message #357814 is a reply to message #357541] Thu, 06 November 2008 14:05 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can use ctas with to_lob on the long column to create a table with the same columns and data, except that the long column would be a clob column. You can also use dbms_metadata.get_ddl to create an empty table with columns of the original dataytpe, including long. Apparently, you can insert from a clob to a long, so you can then insert from the table containing the clob to the table containing the long, if you want to retain the same data structure. You can automate this using a procedure and looping through all_tab_columns, as demonstrated below, so that you don't have to write out the code for each table that you want to do this with.

-- plan_table:
SCOTT@orcl_11g> create table PLAN_TABLE (
  2  	     statement_id	varchar2(30),
  3  	     plan_id		number,
  4  	     timestamp		date,
  5  	     remarks		varchar2(4000),
  6  	     operation		varchar2(30),
  7  	     options		varchar2(255),
  8  	     object_node	varchar2(128),
  9  	     object_owner	varchar2(30),
 10  	     object_name	varchar2(30),
 11  	     object_alias	varchar2(65),
 12  	     object_instance	numeric,
 13  	     object_type	varchar2(30),
 14  	     optimizer		varchar2(255),
 15  	     search_columns	number,
 16  	     id 		numeric,
 17  	     parent_id		numeric,
 18  	     depth		numeric,
 19  	     position		numeric,
 20  	     cost		numeric,
 21  	     cardinality	numeric,
 22  	     bytes		numeric,
 23  	     other_tag		varchar2(255),
 24  	     partition_start	varchar2(255),
 25  	     partition_stop	varchar2(255),
 26  	     partition_id	numeric,
 27  	     other		long,
 28  	     distribution	varchar2(30),
 29  	     cpu_cost		numeric,
 30  	     io_cost		numeric,
 31  	     temp_space 	numeric,
 32  	     access_predicates	varchar2(4000),
 33  	     filter_predicates	varchar2(4000),
 34  	     projection 	varchar2(4000),
 35  	     time		numeric,
 36  	     qblock_name	varchar2(30),
 37  	     other_xml		clob
 38  )
 39  /

Table created.

SCOTT@orcl_11g> EXPLAIN PLAN FOR SELECT * FROM DUAL
  2  /

Explained.

SCOTT@orcl_11g> UPDATE plan_table SET other = 'testing' || ROWNUM
  2  /

2 rows updated.

SCOTT@orcl_11g> SELECT COUNT(*) FROM plan_table
  2  /

  COUNT(*)
----------
         2

SCOTT@orcl_11g> SELECT other FROM plan_table
  2  /

OTHER
--------------------------------------------------------------------------------
testing1
testing2

SCOTT@orcl_11g> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.


-- procedure to copy table with long column:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE copy_table_with_long
  2    (p_old_tab IN VARCHAR2,
  3  	p_new_tab IN VARCHAR2)
  4  AS
  5    v_sql VARCHAR2(32767);
  6  BEGIN
  7    -- create temp_tab and data with clob column using ctas from old table and to_lob:
  8    v_sql := 'CREATE TABLE temp_tab AS SELECT ';
  9    FOR r IN
 10  	 (SELECT DECODE
 11  		   (data_type,
 12  		    'LONG', 'TO_LOB (' || column_name || ') ' || column_name,
 13  		    column_name) || ','
 14  		   AS column_name
 15  	  FROM	 all_tab_columns
 16  	  WHERE  UPPER (table_name) = UPPER (p_old_tab)
 17  	  ORDER  BY column_id)
 18    LOOP
 19  	 v_sql := v_sql || r.column_name;
 20    END LOOP;
 21    v_sql := RTRIM (v_sql, ',') || ' FROM ' || p_old_tab;
 22    EXECUTE IMMEDIATE v_sql;
 23    -- create empty new table with long column using syntax from dbms_metadata.get_ddl:
 24    SELECT REPLACE (DBMS_METADATA.GET_DDL ('TABLE', UPPER (p_old_tab)), p_old_tab, p_new_tab)
 25    INTO   v_sql
 26    FROM   DUAL;
 27    EXECUTE IMMEDIATE v_sql;
 28    -- insert data from temp_tab with clob column to new table with long column:
 29    v_sql := 'INSERT INTO ' || p_new_tab || ' SELECT * FROM temp_tab';
 30    EXECUTE IMMEDIATE v_sql;
 31    -- drop temp_tab:
 32    EXECUTE IMMEDIATE 'DROP TABLE temp_tab';
 33  END;
 34  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.


-- execution:
SCOTT@orcl_11g> EXEC copy_table_with_long ('PLAN_TABLE', 'BKP_PLAN_TABLE')

PL/SQL procedure successfully completed.


-- resulting bkp_plan_table:
SCOTT@orcl_11g> DESC bkp_plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 PLAN_ID                                            NUMBER
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(4000)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_ALIAS                                       VARCHAR2(65)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(30)
 OTHER_XML                                          CLOB

SCOTT@orcl_11g> SELECT COUNT (*) FROM bkp_plan_table
  2  /

  COUNT(*)
----------
         2

SCOTT@orcl_11g> SELECT other FROM bkp_plan_table
  2  /

OTHER
--------------------------------------------------------------------------------
testing1
testing2

SCOTT@orcl_11g> 

Previous Topic: script to create synonym for all table of schema
Next Topic: REGEXP_SUBSTR
Goto Forum:
  


Current Time: Sat Dec 10 14:42:37 CST 2016

Total time taken to generate the page: 0.23800 seconds