Toad World® Forums

Toad for Oracle 12.12.0.39; Copy table with clob from one database schema to another

Trying to move multiple tables from one database to another. Tables with a CLOB element fail and I get “List Index Our of Bounds (?)” where the ? is column index of the first CLOB datatype. This is very consistent.

Is there something I need to set to ensure clobs are supported? Having no issues with the 90%+ of my tables that do not have clobs.

Thanks

Dwain

Hi Dwain,

I just tested and this is working OK for me. I assume you are starting from Schema Browser, then rt-click and choosing “Copy” -> using “Data” -> “Copy to another Schema”.

quesions:

  1. Does the table exist in both schemas? If not, be sure to check “create destination tables if needed”. If the table already exists, do all columns exist in both tables? (column order should not matter)

  2. If the error has a “Click here” link, please click it, then check “Copy to clipboard”, then click OK, and post the contents of your clipboard here. If it doesn’t, will you send me your table DDL so I can try to reproduce it? I tried with several tables that contain CLOBs and they all worked OK for me.

-John

John,

Thanks for your response. I have tried with the table on the destination server and without the table. I have clicked the “created destination table…”. Tables are identical on both servers.

Here is the table definition for the source:

DROP TABLE TM4.TM_CLOB CASCADE CONSTRAINTS;

CREATE TABLE TM4.TM_CLOB
(
NAME CLOB,
TIMESTAMP TIMESTAMP(6),
IID VARCHAR2(400 BYTE)
)
LOB (NAME) STORE AS BASICFILE (
TABLESPACE APEX_1830734697132410
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE APEX_1830734697132410
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

Here is the destintation definition:

DROP TABLE TM4.TM_CLOB CASCADE CONSTRAINTS;

CREATE TABLE TM4.TM_CLOB
(
NAME CLOB,
TIMESTAMP TIMESTAMP(6),
IID VARCHAR2(400 BYTE)
)
LOB (NAME) STORE AS SECUREFILE (
TABLESPACE APEX_470858224689825977
ENABLE STORAGE IN ROW
CHUNK 8192
NOCACHE
LOGGING
STORAGE (
INITIAL 104K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
))
TABLESPACE APEX_470858224689825977
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;

Here is the error message on the clipboard:

List index out of bounds (0)

Note: The index (or zero in this example) correlates with the location of the first clob in a table. It starts with zero so the index is a zero here as the clob is the first column. In another table where the clob is in the 7th column it says six. The progress message at the bottom of the window “Copy Table Data from …” says “Fetching Rows…” when it fails.

Thanks for your help.

Dwain

Hi Dwain,

Sorry I didn’t reply back yesterday - I never got an email notification about your response! Not sure why. Anyway, I tried this table with Toad 12.12 and for me it copied fine. I’m kinda scratching my head over this one. If I get any ideas, I’ll post again.

-John

If you zip up your user files folder and send it to me, I can try it again with your Toad settings. That may have something to do with it.

The easiest way to find your user files folder is go to Toad Options. Click “General” (about 1/2 way down on the left), then “Open Folder” on the right. That will open a windows explorer window and “User Files” will be one of the sub folders. Zip it up with Toad not running.

You can send it to john.dorlon@quest.com.

Zip File is attached.

BTW, this is a new installation with no configuration settings changed.

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Tuesday, January 16, 2018 8:57 AM
To: toadoracle@toadworld.com
Subject: RE: [Toad for Oracle - Discussion Forum] Toad for Oracle 12.12.0.39; Copy table with clob from one database schema to another

RE: Toad for Oracle 12.12.0.39; Copy table with clob from one database schema to another

Reply by John Dorlon

If you zip up your user files folder and send it to me, I can try it again with your Toad settings. That may have something to do with it.

The easiest way to find your user files folder is go to Toad Options. Click “General” (about 1/2 way down on the left), then “Open Folder” on the right. That will open a windows explorer window and “User Files” will be one of the sub folders. Zip it up with Toad not running.

You can send it to john.dorlon@quest.com.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

Hi Dwain,

You are connecting as sysdba. That is causing my query to USER_TAB_COLS to not return any rows since when you connect as SYSDBA, the USER* views return SYS’s objects and not whatever the login schema is. So just log in as Normal and it should work. This is a bug in Toad and I’ll fix it, but you probably don’t need to be logging in as SYSDBA for something like this.

-John

Who would have thought…. That took care of the problem. Thank you very much.

Dwain Craddock

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]
Sent: Tuesday, January 16, 2018 9:56 AM
To: toadoracle@toadworld.com
Subject: RE: [Toad for Oracle - Discussion Forum] Toad for Oracle 12.12.0.39; Copy table with clob from one database schema to another

RE: Toad for Oracle 12.12.0.39; Copy table with clob from one database schema to another

Reply by John Dorlon

Hi Dwain,

You are connecting as sysdba. That is causing my query to USER_TAB_COLS to not return any rows since when you connect as SYSDBA, the USER* views return SYS’s objects and not whatever the login schema is. So just log in as Normal and it should work. This is a bug in Toad and I’ll fix it, but you probably don’t need to be logging in as SYSDBA for something like this.

-John

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

You’re welcome. Normally we check for connect mode before using the USER_ views, I guess I just missed this one!