Calling Export from Pl/sql using C external proc-CSDN博客

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1993409963502

Sudee -- Thanks for the question regarding "Calling Export from Pl/sql using C external proc.", version 8.1.6
originally submitted on 16-Nov-2001 20:55 Eastern US time, last updated 27-Apr-2005 9:31

You Asked

Hi Tom,

Is there anything wrong with these steps,
the exp does not create a .dmp file.

Thanks


  #include 
  #include 
  #include 

  __declspec(dllexport) void sh(char *); 

  void sh( char *cmd ) 
  { 
  int num; 

  num = system(cmd); 
  } 


CREATE LIBRARY shell_lib is 'C:oracleextprocshell.dll'; 


  create or replace procedure shell ( 
  cmd IN char) 
  as external 
  name "sh" 
  library shell_lib 
  language C 
  parameters ( 
  cmd string); 

exec shell('C:oracleora81binexp userid=system/manager 
file=C:oracleextproctest.dmp full=y rows=N');

and we said...

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:bintouch c:temptest.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 tempexp.log
tkyte@TKYTE816> begin
  2      shell( 'c:oraclebinexp userid=scott/tiger ' ||
  3               ' file=c:temptest.dmp tables=emp 2> tempexp.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 tempexp.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 tempexp.log

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

PL/SQL procedure successfully completed.

tkyte@TKYTE816> host type tempexp.log
COMSPEC=C:WINNTSYSTEM32COMMAND.COM
ORACLE_SID=extproc
ALLUSERSPROFILE=C:DOCUME~1ALLUSE~1
COMMONPROGRAMFILES=C:PROGRA~1COMMON~1
COMPUTERNAME=TKYTE-DELL
JSERV=C:oracle9i/Apache/Jserv/conf
NUMBER_OF_PROCESSORS=1
OLAP_HOME=C:oracle9iolap
OS=Windows_NT
OS2LIBPATH=C:WINNTsystem32os2dll;
PATH=C:oraclebin;C:oracle9ibin;C:orantbin;C:oracle9i/bin;C:oracle9iApach
ePerl
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:WINNTTEMP
TMP=C:WINNTTEMP
USERPROFILE=C:DOCUME~1DEFAUL~1
WV_GATEWAY_CFG=C:oracle9iApachemodplsqlcfgwdbsvr.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 tempexp.log

tkyte@TKYTE816>
tkyte@TKYTE816> begin
  2      shell( 'c:oraclebinexp userid=scott/tiger@tkyte816 ' ||
  3               ' file=c:temptest.dmp tables=emp 2> tempexp.log' );
  4  end;
  5  /

PL/SQL procedure successfully completed.

tkyte@TKYTE816> host type tempexp.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 tempexp.log

tkyte@TKYTE816>
tkyte@TKYTE816> begin
  2      shell( 'set ORACLE_SID=tkyte816 && ' ||
  3                   'c:oraclebinexp userid=scott/tiger@tkyte816 ' ||
  4               ' file=c:temptest.dmp tables=emp 2> tempexp.log' );
  5  end;
  6  /

PL/SQL procedure successfully completed.

tkyte@TKYTE816> host type tempexp.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.