Exporting row from a Long column with many rows [message #72933] |
Wed, 17 December 2003 23:32 |
leena
Messages: 8 Registered: September 2000
|
Junior Member |
|
|
I have a problem .pls help me .I dont know solve it.
I have a table with 1 Long column & many rows in that .
I want each row in a separate file programmatically. ....
Using exp mag/mag tables=l1 query="where deptno=30" wud be useful with a table having less number of rows.
In my table there are more rows and want it programmatically to be exported in a file.
Pls help me with ur valueable suggestions.
Thanks a lot.
|
|
|
Re: Exporting row from a Long column with many rows [message #72934 is a reply to message #72933] |
Thu, 18 December 2003 02:43 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
here is a workaround
bash-2.03$ sqlplus mag/mag
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Dec 18 07:34:21 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
mag@mutation_mutation > desc l1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NUMBER
DNAME LONG
mag@mutation_mutation > select * from l1;
DEPTNO DNAME
---------- --------------------------------------------------------------------------------
20 RESEARCH
30 SALES
40 OPERATIONS
----------------------------------------------------------------------
lets create the script, that will do the export row by row
assuming deptno is the primary key, running the sql to generate the script, spool the output
make sure u have a newfile in FILE= parameter so that export will dump every row in a new filename
----------------------------------------------------------------------
mag@mutation_mutation > set head off
mag@mutation_mutation > set feed off
mag@mutation_mutation > spool expscript.scr
mag@mutation_mutation > select 'exp mag/mag tables=l1 query="where deptno='||deptno||'" file=file'||deptno||'.dmp' from l1;
exp mag/mag tables=l1 query="where deptno=20" file=file20.dmp
exp mag/mag tables=l1 query="where deptno=30" file=file30.dmp
exp mag/mag tables=l1 query="where deptno=40" file=file40.dmp
mag@mutation_mutation > spool off
----------------------------------------------------------------------
check the contents of the spooled file
and
remove unwanted entries
----------------------------------------------------------------------
mag@mutation_mutation > !cat expscript.scr
mag@mutation_mutation > select 'exp mag/mag tables=l1 query="where deptno='||deptno||'" file=file'||deptno||'.dmp' from l1;
exp mag/mag tables=l1 query="where deptno=20" file=file20.dmp
exp mag/mag tables=l1 query="where deptno=30" file=file30.dmp
exp mag/mag tables=l1 query="where deptno=40" file=file40.dmp
mag@mutation_mutation > spool off
[b]-- after removing unwanted entries[/b]
mag@mutation_mutation > !cat expscript.scr
exp mag/mag tables=l1 query="where deptno=20" file=file20.dmp
exp mag/mag tables=l1 query="where deptno=30" file=file30.dmp
exp mag/mag tables=l1 query="where deptno=40" file=file40.dmp
mag@mutation_mutation > exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
----------------------------------------------------------------------
run the batch script and check the creation of files
----------------------------------------------------------------------
bash-2.03$ chmod +x expscript.scr
bash-2.03$ expscript.scr
Export: Release 9.2.0.1.0 - Production on Thu Dec 18 07:40:04 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table L1 1 rows exported
Export terminated successfully without warnings.
Export: Release 9.2.0.1.0 - Production on Thu Dec 18 07:40:04 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table L1 1 rows exported
Export terminated successfully without warnings.
Export: Release 9.2.0.1.0 - Production on Thu Dec 18 07:40:05 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table L1 1 rows exported
Export terminated successfully without warnings.
bash-2.03$ ls -lt *.dmp
-rw-r--r-- 1 oracle dba 2048 Dec 18 07:40 file30.dmp
-rw-r--r-- 1 oracle dba 2048 Dec 18 07:40 file40.dmp
-rw-r--r-- 1 oracle dba 2048 Dec 18 07:40 file20.dmp
|
|
|
|
|