Hi Tom
thanks for getting back to me on this
the os file that would be created would be very large from the table of 6,699,818 records
the OS file size created was only 314kb before it errored out
if i take out the order by
exec dbms_output.put_line( dump_csv (' select * from dtree ', '%', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree.txt'));
it errors out after dumping only 5 records = 5kb os file
I then ran a test on another table of 109,721,230 records called dauditnew
it created an OS file size of 1,860,475kb (1.8gb) with no problems before i killed it
exec dbms_output.put_line( dump_csv(' select * from dauditnew ', '%', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dauditnew.txt'));
desc dauditnew
Name Null? Type
----------------------------------------- -------- ----------------------
EVENTID NOT NULL NUMBER(19)
AUDITID NOT NULL NUMBER(10)
AUDITSTR NOT NULL VARCHAR2(32 CHAR)
AUDITDATE NOT NULL DATE
DATAID NUMBER(10)
SUBTYPE NUMBER(10)
USERID NUMBER(10)
PERFORMERID NOT NULL NUMBER(10)
VALUEKEY VARCHAR2(255 CHAR)
VALUE1 VARCHAR2(4000 CHAR)
VALUE2 VARCHAR2(4000 CHAR)
APPLICATIONID VARCHAR2(255 CHAR)
*****************
test I ran on dtree table
i thought that it may be my terminator "%" because looking at the EXTENDEDDATA col which is clob i saw "%" in the data so I changed the terminator to use a "$" sign
that dd not work, so i added all the columns except the EXTENDEDDATA column
test10 shows that it worked, but was that the issue ?
apparently not as in the next tests i removed 5 columns at a time leaving in the EXTENDEDDATA col
removing 19 columns and leaving in the EXTENDEDDATA col worked
so I am not sure at this point why it errors out with all columns and the EXTENDEDDATA column
why it works with all columns except EXTENDEDDATA or a combination of some 19 columns and the EXTENDEDDATA col
--test 10 (all columns except for EXTENDEDDATA (clob) column
SQL> exec dbms_output.put_line( dump_csv(' select OWNERID,PARENTID,DATAID,NAME,ORIGINOWNERID,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree10.txt'));
PL/SQL procedure successfully completed.
--test20 (added EXTENDEDDATA col (clob) did not work
SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,OWNERID,PARENTID,DATAID,NAME,ORIGINOWNERID,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree20.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,OWNERID,PARENTID,DATAID,NAME,ORIGINOWNERID,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree20.txt')); END;
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1
--test30 (left EXTENDEDDATA in and removed next five columns) did not work
SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree30.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,ORIGINDATAID,USERID,GROUPID,UPERMISSIONS,GPERMISSIONS,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree30.txt')); END;
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1
--test80 (left EXTENDEDDATA in and removed next five columns) did not work
SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree80.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,WPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree80.txt')); END;
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1
--test90 (left EXTENDEDDATA in and removed next five columns) did not work
SQL>
exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree90.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,GPERMISSIONSWPERMISSIONS,SPERMISSIONS,ACLCOUNT,PERMID,DATATYPE,CREATEDBY,CREATEDATE,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree90.txt')); END;
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1
--test100 (left EXTENDEDDATA in and removed next five columns) DID WORK !
SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree100.txt'));
PL/SQL procedure successfully completed.
--test110 (left EXTENDEDDATA in and added two columns) did not work
SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree110.txt'));
BEGIN dbms_output.put_line( dump_csv(' select EXTENDEDDATA,MODIFYDATE,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree110.txt')); END;
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 691
ORA-06512: at "LLTEST.DUMP_CSV", line 49
ORA-06512: at line 1
--test120 (left EXTENDEDDATA in and remove one column) DID WORK !
SQL> exec dbms_output.put_line( dump_csv(' select EXTENDEDDATA,MAXVERS,RESERVED,RESERVEDBY,RESERVEDDATE,VERSIONNUM,DCOMMENT,DCATEGORY,SUBTYPE,EXATT1,EXATT2,ORDERING,MAJOR,MINOR,RELEASEREF,CHILDCOUNT,ASSIGNEDTO,DATEASSIGNED,DATEEFFECTIVE,DATEEXPIRATION,DATEDUE,DATESTARTED,DATECOMPLETED,STATUS,PRIORITY,GIF,CATALOG,CACHEEXPIRATION from dtree where dataid=2059 ', '$', 'E:\Oracle_SQL_Loader\Table_Exports', 'table_dtree120.txt'));
PL/SQL procedure successfully completed.
Thanks
Jim