===========================================================
Calling Export from Pl/sql using C external proc
===========================================================
作者: jametong(http://jametong.itpub.net)
发表于: 2005.04.27 23:26
分类: oracle refs
出处: http://jametong.itpub.net/post/5042/27944
---------------------------------------------------------------
发表于: 2005.04.27 23:26
分类: oracle refs
出处: http://jametong.itpub.net/post/5042/27944
---------------------------------------------------------------
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
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,and we said...
Is there anything wrong with these steps,
the exp does not create a .dmp file.
Thanks
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
__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');
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.
jametong
发表于:2005.04.27 23:26
::分类:
( oracle refs
)
::阅读:(37200次)
:: 评论
(650)
download album