Ok, this is easily solved. What I'll do however is show you the steps I went thru to diagnose this.

Step 1, after building the example, I tested it with something "non-oracle" -- to eliminate that from the mix. I have a program "touch.exe" which when executed simply updates the timestamp on a file in the OS and if the file does not exist, it creates hence.

Hence I ran "exec shell( c:\bin\touch c:\temp\test.dat" );"

Sure enough, the file test.dat appeared -- proving the extproc itself functions.

Then I tried export -- sure enough, that DID NOT work.

Step 2, lets capture the output. In order to diagnose this (and in order to be able to verify the export actually WORKED) we need to capture the output. Using SYSTEM, the easiest way is:


tkyte@TKYTE816> host erase \temp\exp.log
tkyte@TKYTE816> begin
2 shell( 'c:\oracle\bin\exp userid=scott/tiger ' ||
3 ' file=c:\temp\test.dmp tables=emp 2> \temp\exp.log' );
4 end;
5 /

PL/SQL procedure successfully completed.


that would not only run our command but it would also redirect stderr to a file we can look at. When I ran that, I saw clearly what the issue was:


tkyte@TKYTE816> host type \temp\exp.log

Export: Release 8.1.6.0.0 - Production on Sat Nov 17 13:02:35 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


EXP-00056: ORACLE error 12560 encountered
ORA-12560: TNS:protocol adapter error
EXP-00000: Export terminated unsuccessfully

So, it is simply not able to connect (it in fact was running exp -- you just had no way to see the output from exp and now you do). So, once we have that -- we can go the next step. Well, I would really like to see the environment cause 12560 is almost always an environment issue. At this point, I stop running exp and just run SET to see whats the environment looks like:

tkyte@TKYTE816> host erase \temp\exp.log

tkyte@TKYTE816>
tkyte@TKYTE816> begin
2 shell( 'command /c set > \temp\exp.log' );
3 end;
4 /

PL/SQL procedure successfully completed.

tkyte@TKYTE816> host type \temp\exp.log
COMSPEC=C:\WINNT\SYSTEM32\COMMAND.COM
ORACLE_SID=extproc
ALLUSERSPROFILE=C:\DOCUME~1\ALLUSE~1
COMMONPROGRAMFILES=C:\PROGRA~1\COMMON~1
COMPUTERNAME=TKYTE-DELL
JSERV=C:\oracle9i/Apache/Jserv/conf
NUMBER_OF_PROCESSORS=1
OLAP_HOME=C:\oracle9i\olap
OS=Windows_NT
OS2LIBPATH=C:\WINNT\system32\os2\dll;
PATH=C:\oracle\bin;C:\oracle9i\bin;C:\orant\bin;C:\oracle9i/bin;C:\oracle9i\Apache\Perl\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 8 Stepping 3, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0803
PROGRAMFILES=C:\PROGRA~1
PROMPT=$P$G
SYSTEMDRIVE=C:
SYSTEMROOT=C:\WINNT
TEMP=C:\WINNT\TEMP
TMP=C:\WINNT\TEMP
USERPROFILE=C:\DOCUME~1\DEFAUL~1
WV_GATEWAY_CFG=C:\oracle9i\Apache\modplsql\cfg\wdbsvr.app

Well, there is the problem in this case -- we are using the EXTPROC service, so the ORACLE_SID is in fact "extproc".

At this point, we have a couple of choices, two of which I have below - one we can use Net8:

tkyte@TKYTE816> host erase \temp\exp.log

tkyte@TKYTE816>
tkyte@TKYTE816> begin
2 shell( 'c:\oracle\bin\exp userid=scott/tiger@tkyte816 ' ||
3 ' file=c:\temp\test.dmp tables=emp 2> \temp\exp.log' );
4 end;
5 /

PL/SQL procedure successfully completed.

tkyte@TKYTE816> host type \temp\exp.log

Export: Release 8.1.6.0.0 - Production on Sat Nov 17 13:05:36 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.



The other is to run more than one command:

tkyte@TKYTE816> host erase \temp\exp.log

tkyte@TKYTE816>
tkyte@TKYTE816> begin
2 shell( 'set ORACLE_SID=tkyte816 && ' ||
3 'c:\oracle\bin\exp userid=scott/tiger@tkyte816 ' ||
4 ' file=c:\temp\test.dmp tables=emp 2> \temp\exp.log' );
5 end;
6 /

PL/SQL procedure successfully completed.

tkyte@TKYTE816> host type \temp\exp.log

Export: Release 8.1.6.0.0 - Production on Sat Nov 17 13:08:45 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
Export terminated successfully without warnings.



And there you go (you can get the ORACLE_SID you need from v$instance:

tkyte@TKYTE816> select instance_name from v$instance;

INSTANCE_NAME
----------------
tkyte816

if you would like to make this generic.


Hopefully, this will show you how to debug these sorts of issues in the future. you should note that you should use UTL_FILE or a bfile to open, read and verify the success of your command! You could use dbms_lob.loadfromfile as well to get it into a database table for future reference.