Home » RDBMS Server » Server Utilities » Exporting row from a Long column with many rows
Exporting row from a Long column with many rows [message #72933] Wed, 17 December 2003 23:32 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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

Re: Exporting row from a Long column with many rows [message #73178 is a reply to message #72934] Thu, 26 February 2004 00:30 Go to previous messageGo to next message
Bhavesh
Messages: 7
Registered: July 2001
Junior Member
i got a nice help,and i succeed using this help as it shows the examples.It should also show the possible errors as i faced the error 911--problem in sql etc.
Re: Exporting row from a Long column with many rows [message #73183 is a reply to message #73178] Thu, 26 February 2004 02:50 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post your ERROR messages
Previous Topic: How to find db size, blah blah...
Next Topic: ORA-01801 when making an export
Goto Forum:
  


Current Time: Sat Jun 29 06:01:43 CDT 2024