Thursday, July 17, 2008

Create and Run a Sample External Procedure Program on Linux

Today I need to write external procedure:

I have used metalink Note:312564.1

I use Oracle 11g on linux x86_64:

I assumed ORACLE_HOME is /oracle/11g PATH.

1. I need ensure extproc be work, So I modified listener.ora and tnsnames.ora files in $ORACLE_HOME/network/admin PATH

listener.ora =>
---------------------------------------------------------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME=PLSExtProc)
(ORACLE_HOME=/oracle/11g)
(PROGRAM=extproc)
(ENVS="EXTPROC_DLLS=ANY")
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
)

---------------------------------------------------------------

and then reload listener + check

$ lsnrctl reload

LSNRCTL for Linux: Version 11.1.0.6.0

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))

The command completed successfully

$ lsnrctl services

Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER

tnsnames.ora =>

---------------------------------------------------------------

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL = IPC)( KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
)
)

---------------------------------------------------------------

Check KEY (EXTPROC1521) and SID (PLSExtProc) on tnsnames.ora file with listener.ora file

And then test extproc configure:

$ tnsping EXTPROC_CONNECTION_DATA

TNS Ping Utility for Linux: Version 11.1.0.6.0

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL = IPC)( KEY = EXTPROC1521))) (CONNECT_DATA = (SID = PLSExtProc)))
OK (0 msec)


2. created file with C code (shell.c)

#include < stdio.h >
#include < stdlib.h >
#include < string.h >

void sh(char *command)
{
int num;
num = system(command);
}

3. compiled code and generated shared object

Anyway I checked extproc version:

$ $ORACLE_HOME/bin/extproc

Oracle Corporation

Heterogeneous Agent Release 11.1.0.6.0 - 64bit Production

Using the table below,


Operating System
extproc bit version
1st: Compile the shell.c program
2nd: Generate the shared object
Solaris SPARC
extproc (64 bit)
gcc -G -c -m64 shell.c
ld -r -o shell.so shell.o
Solaris SPARC*
extproc32 (32 bit)
gcc -G -c shell.c
ld -r -o shell.so shell.o
Linux RH AS3 32 bit
extproc (32 bit)
gcc -c shell.c
ld -shared -o shell.so shell.o
Linux RH AS4 x86-64
extproc (64 bit)
gcc -fPIC -c shell.c
ld -shared -o shell.so shell.o
Linux RH AS4 x86-64*
extproc32 (32 bit)
gcc -m32 -c shell.c
ld -shared -melf_i386 -o shell.so shell.o
AIX 5L
extproc (64 bit)
gcc -maix64 -c shell.c
gcc -maix64 -shared -o shell.so shell.o
AIX 5L*
extproc32 (32 bit)
gcc -c shell.c
gcc -shared -o shell.so shell.o
HPUX 11.11**
extproc32 (32 bit)
gcc -c shell.c
gcc -shared -o shell.sl shell.o
HPUX 11.11
extproc (64 bit)
/usr/local/pa64/bin/gcc -c shell.c
/usr/local/pa64/bin/gcc -shared -o shell.sl shell.o









and then compiled:

This case used Linux RH AS4 x86-64, extproc (64 bit)

$ gcc -fPIC -c shell.c

$ ld -shared -o shell.so shell.o

$ chmod 755 shell.so

$ cp shell.so $ORACLE_HOME/lib

4. created library and procedure

$ sqlplus / as sysdba

SQL> CREATE or replace LIBRARY shell_lib is '$ORACLE_HOME/lib/shell.so'

/

Library created.

SQL> CREATE OR REPLACE PROCEDURE shell(command IN char)
AS EXTERNAL
NAME "sh"
LIBRARY shell_lib
LANGUAGE C
PARAMETERS (command string);

/

Procedure created.

5. Test

SQL> exec shell('ls');

PL/SQL procedure successfully completed.

Didn't see anything, So

SQL> exec shell('ls > /tmp/test01');

PL/SQL procedure successfully completed.

$ cat /tmp/test01

shell.c
shell.o
shell.so

extproc is functional is work.


Enjoy!

8 comments:

nn said...

Hi,

I am able to execute the shell external procedure and in turn getting the following success statement.

SQL> exec shell('ls > /home/ntt/outtst1');

PL/SQL procedure successfully completed.

But the output file is not at all generated. Can you please let me know what can be the reason behind this. The output file is not generated and I can't see any output by executing the shell C procedure.

Please provide me some suggestions on this problem. I am stuck on this for about 2 complete days.

HunterX said...

I think that is problem of permission on linux/unix.

Please try run again:

SQL> exec shell('ls > /tmp/outtst1');

If It's OK.

Please test below "shell cmd" from "oracle" user:

$ touch /home/ntt/test.out

I think you'll find error about permission:

Anyway If you need to write file at /home/ntt/ PATH by External Procedure ("oracle" user in OS)

You must use "chmod" command to change /home/ntt/ PATH FOR Other user can write.

nn said...

Hey,

Thanks a lot for your quick response. I kind of understood what you were saying regarding the permissions.

But I did not get what a oracle user is and how is it different from super user or from any other general unix user(who is not a super user).

And also what chmod do we need to use to allow others towrite to that location.

HunterX said...

try to find out more about linux/unix administrator.

"oracle" user is not super user (root), it's a simple user.

But Oracle software need "oracle" user to install and need oinstall... dba group as well.

So oracle user will read/write somewhere, he's privilege.

I need to know, who is owner in "/home/ntt" path.

and you use "oracle" user to start oracle instance, right?

when you use "oracle" user, oracle instance will write anywhere, that oracle user has privilege.

ref: http://www.linuxcommand.org/lts0070.php

Example:

$ id
uid=500(oracle) gid=500(dba) groups=500(dba),501(oinstall)

$ touch /home/admin/test.txt
touch: cannot touch `/home/admin/test.txt': Permission denied

$ ls -la /home/

drwx------ 4 admin admin 4096 Jun 18 09:02 admin

So:

$ su -

# chmod 777 /home/admin/

$ ls -la /home/

drwxrwxrwx 4 admin admin 4096 Jun 18 09:05 admin

$ touch /home/admin/test.txt

can write:

>>>

You can check Processes, who is owner (oracle instance):

$ ps -aef | grep $ORACLE_SID
UID PID PPID C STIME TTY TIME CMD

oracle 16046 1 0 May29 ? 00:41:08 ora_smon_db

nn said...

hey,

Thanks a lot for the information provided. Now I can understand what you are saying.

thank you, once again for your quick response...

nn said...

Hi,

I am executing the following 'ld' command : even though all the dependent *.so files are present in the lib directory(current directory), it still gives me warning saying that so and so file is needed and is not found :

~/app/oracle/product/10.2.0/server/lib$ ld -o custagg.so ./l*.so ../../../../../../../../libstdc++.s* custagg.o -lc
ld: warning: libnnz10.so, needed by ./libclntsh.so, not found (try using -rpath or -rpath-link)
ld: warning: libhasgen10.so, needed by ./libclsra10.so, not found (try using -rpath or -rpath-link)
ld: warning: libhsbase.so, needed by ./libhsnav.so, not found (try using -rpath or -rpath-link)
ld: warning: libocrb10.so, needed by ./libocr10.so, not found (try using -rpath or -rpath-link)
ld: warning: libocrutl10.so, needed by ./libocr10.so, not found (try using -rpath or -rpath-link)
ld: warning: libskgxn2.so, needed by ./libsrvmhas10.so, not found (try using -rpath or -rpath-link)
ld: warning: libons.so, needed by ./libsrvmhas10.so, not found (try using -rpath or -rpath-link)
ld: warning: libdbcfg10.so, needed by ./libsrvmhas10.so, not found (try using -rpath or -rpath-link)
ld: warning: cannot find entry symbol _start; defaulting to 0000000008048c40
./libagtsh.so: undefined reference to `homtscb_ShutdownCallback'

when i do ls -a
I can see all the above warning files in lib directory :

. libagtsh.so.1.0 libcxa.so.5 libirc.a libocci.so libons.so libskgxp10.so libuini10.so
.. libclntsh.so libdbcfg10.so libjox10.so libocci.so.10.1 liborasdkbase.so libskgxpd.so libunwind.so.5
custagg.o libclntsh.so.10.1 libemmas10.so libldapjclnt10.so libocijdbc10.so liborasdkbase.so.10.2 libskgxpu.so libxdb.so
facility.lis libclsra10.so libhasgen10.so libnjni10.so libocr10.so liborasdk.so libsqlplus.so shell.so
hsdb_odbc.so libcorejava.so libheteroxa10.so libnjssl10.so libocrb10.so liborasdk.so.10.2 libsqora.so.10.1 sysliblist
hsdb_ora.so libcoresh10.so libhsbase.so libnnz10.so libocrutl10.so libqsmashr.so libsrvm10.so
lclasses12.zip libcprts.so.5 libhsnav.so libnque10.so libodm10.so libskgxn2.so libsrvmhas10.so
libagtsh.so libcxa.so.3 libimf.so libntcpaio10.so libodmd10.so libskgxns.so libsrvmocr10.so



Can you please provide me some solution on how to get this to work
and also did not understand the last two errors.

Please reply to this message.

Thanks
-nn

HunterX said...

OK.
I'm not sure.
Anyway try to su to "root" user, after that add $ORACLE_HOME/lib in /etc/ld.so.conf

and then run "ldconfig" command:

Or when you are "oracle" user:

Please set shell "LD_LIBRARY_PATH" Environment =>

export LD_LIBRARY_PATH=/usr/lib:/lib:$ORACLE_HOME/lib

and then run "ld" again.


Good luck

nn said...

Hi,

Thank you for replying to my message.
I have set that path earlier itself.

Do you have any idea why I am getting this error :

/server/lib/libagtsh.so: undefined symbol: homtscb_ShutdownCallback

this is some how causing other error :ORA-06521: PL/SQL: Error mapping function
ORA-06522: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/custagg.so:
undefined symbol: ODCIAggregateInitialize

And this ODCIAggregateInitialize is part of Oracle data catridge interface(ODCI).. which is what I want to use for my user defined aggregate.

Do you have any idea of OCI(oracle call interface) and also ODCI. And I am not sure what shared libraries do i need to use those OCI and ODCI.

Please let me know if you have any knowledge of those two.

Thanks in Advance,
-nn