Re: QUESTION: Corrupt data block work around

Neil Greene <neil_at_kynug.org> writes:
|> For those of you following along in this little game. I have verified
|> that accessing all of the records in the rulings table through the
|> nasrisid index sucessfully looks at all of the records in this table.
|> Now, the problem is to sucessfully copy the data from this table using
|> this index to a newly created table. The only problem is with the
|> "rulingtext" field which is of type long. It is my understanding that
|> creating a new table with this field will not be successful and that I
|> will either have to dump the data to disk, or use pro*c to access this
|> field. If I am not mistaken, sql*plus is going to limit the line length
|> to 500 characters, which may fall far below my 2000 character limit of a
|> long datatype. Is anyone still listening?? :-)
|>
|> Any takers for a solution?
|> --
|> Neil Greene
|> President, Kentucky NeXT User Group, Inc.
|> Email: neil_at_kynug.org [NeXTMail]

Since you are on a Unix based machine, the SQL*Plus COPY command may do the trick (assuming you want to copy the table into another table in the same database rather than dump it to a file).

set long 2000
set maxdata 32767
set arraysize 1

copy from user/pass_at_P: -
create newtable using -
select * from oldtable -
where ...;

[The dashes at the end of the line are significant] If you do want to dump the long to a flat file and have a tool like SQL*Loader load it back in, you might be better off using Pro*C. SQL*Plus will allow you to dump longs to a file by doing something like

set long 2000
column mylongcolumn format a80 wrap

But I do not happen to know how to get SQL*Plus to dump such data in an easy format that SQL*Loader can read. I believe there is a Pro*C program called unload.pc floating around out there somewhere that could make life easier for you. I don't happen to have a copy of it unfortunately.

Hope this helps,
Roderick Manalac
Oracle Corporation
[DISCLAIMER: offer not valid where prohibited by law] Received on Tue May 18 1993 - 03:26:06 CEST