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  |
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 #357814 is a reply to message #357541] |
Thu, 06 November 2008 14:05  |
 |
Barbara Boehmer
Messages: 9090
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>
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 10:32:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
