STEPS FOLLOWED FOR CALLING THE PRO*C EXECUTABLES FROM D2K

Step 1: Set Up an Environment for Calling External Procedures

Entry in tnsnames.ora

extproc_connection_data =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = kptcl))
(CONNECT_DATA = (SID = extproc))
)
Entry in listener.ora

#
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= kptcl))
(ADDRESS= (PROTOCOL= IPC)(KEY= PNPKEY))
(ADDRESS= (PROTOCOL= TCP)(Host= kptccc)(Port= 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= kptccc.)
(ORACLE_HOME= /home/oracle8/app/oracle/product/8.0.5)
(SID_NAME = kptcl)
)
(SID_DESC =
(SID_NAME = extproc)
(ORACLE_HOME = /home/oracle8/app/oracle/product/8.0.5)
(PROGRAM = extproc) )


Step 2: Create Shared library
C FILE


/*
* NAME
* csslib19.c - C function to show example usage of
* Oracle8 external procedure.
*
* OVERVIEW
* This program contains the function "RunOSCmd"
* called from the Oracle stored procedure
* "osutil.RunOsCmd". For more Information on
* Service Routines see PL/SQL Users Guide Oracle-8
* on Page 10-14
*/

#include
#include
#include
#include
#include

#ifndef OCI_ORACLE
#include
#endif

/*
* RunOsCmd - This function runs the OS command
* pointed to by "cmd". The output of this command
* is returned via the cmdo ptr.
*/
void RunOsCmd(ctx, cmd, cmd_ind, cmd_len, cmdo, cmdo_ind, cmdo_len)
OCIExtProcContext *ctx; /* Oracle ConText Pointer */
char *cmd; /* Pointer to OS command */
short cmd_ind; /* Null Indicator for OS command */
int cmd_len; /* Length indicator variable */
char *cmdo; /* Pointer to OS command output */
short *cmdo_ind; /* Null Indicator for OS command */
int *cmdo_len; /* Length indicator variable */
{
char cmdbuf[BUFSIZ]; /* buffer to hold command output */
FILE *fptr; /* pipe pointer for OS command */
int PLSQLBSZ = 32767; /* Max PL/SQL output buffer size */

*cmdo = '\0'; /* initialize output variables */
*cmdo_len = 0;

if (cmd_ind == OCI_IND_NULL) {
strcpy(cmdo,"Command entered cannot be NULL...");
*cmdo_ind = (short)OCI_IND_NOTNULL;
*cmdo_len = 30;
} else {
if ((fptr = popen(cmd, "r")) != NULL) { /* Execute command */
while (fgets(cmdbuf, BUFSIZ, fptr) != NULL) {
strcat(cmdo, cmdbuf); /* Build output string */
*cmdo_len = *cmdo_len + strlen(cmdbuf);
}
if (*cmdo == NULL) {
*cmdo_ind = (short)OCI_IND_NULL;
} else {
*cmdo_ind = (short)OCI_IND_NOTNULL;
if (*cmdo_len > PLSQLBSZ) { /* Trunc output to max buf sz */
*cmdo_len = PLSQLBSZ;
cmdo[PLSQLBSZ - 1] = '\0';
}
}
pclose(fptr); /* Close the pipe */
} else {
printf("\nUnable to open FPTR\n"); /* Some OS error occurred */
*cmdo_ind = (short)OCI_IND_NULL;
*cmdo_len = 0;
}
}
} /* End of RunOsCmd */



Using this C file create the

The Shared library was created as follows:
Create the .c file
Compile the CSSSLB19.pc to get the CSSSBLB19.c
Create the .o file-->
cc -I/oracle8/home/app/oracle/product/8.0.5/rdbms/demo
-I/oracle8/home/app/oracle/product/8.0.5/plsql/public
-I/oracle8/home/app/oracle/product/8.0.5/network/public CSSSLB19.c
Create the .so file as follows-->
make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_with_context
SHARED_LIBNAME=CSSSLB19.so OBJS="CSSSLB19.o"



Step 4: Register the shared library CSSSLB19.so

The shared library is registered in the database as follows

create or replace library oscalllib is
'/user/bfsl/source/lib/CSSSLB19.so';



Step 5: Create External Procedure(osutil)

Run the following script which will create a package called osutil
CREATE OR REPLACE PACKAGE osutil IS

PROCEDURE RunOsCmdReg(cmdin IN VARCHAR2, cmdoutput OUT VARCHAR2);
PROCEDURE RunOsCmd(acmd IN VARCHAR2);
-- Purpose: Wrapper used to run the external
-- procedure, "RunOsCmd".The dbms_output package is
-- used to print the results.
-- Input Arguments:
-- acmd - an OS command

end osutil;
/
show errors

CREATE OR REPLACE PACKAGE BODY osutil IS

PROCEDURE RunOsCmdReg(cmdin IN VARCHAR2, cmdoutput OUT VARCHAR2)
IS EXTERNAL
NAME "RunOsCmd"
LIBRARY OSCALLLIB
WITH CONTEXT
PARAMETERS (CONTEXT,
cmdin STRING,
cmdin INDICATOR SHORT,
cmdin LENGTH INT,
cmdoutput STRING,
cmdoutput INDICATOR SHORT,
cmdoutput LENGTH INT);

PROCEDURE RunOsCmd(acmd IN VARCHAR2) IS

cmd_out VARCHAR2(32767); -- output of OS command
i INTEGER; -- counter variabl
j INTEGER; -- counter variable
len INTEGER; -- length of command output
cnl CHAR(1) := chr(10); -- "newline" character

BEGIN

dbms_output.enable(32767); -- enable a large output buffer
RunOsCmdReg(acmd, cmd_out); -- call the external procedure

IF (cmd_out IS NULL) THEN
dbms_output.put_line('ERROR, bad command or no output returned...');
ELSE -- output one line at a time
len := length(cmd_out);
i := 1; -- start at first char
j := instr(cmd_out,cnl,i,1); -- find first newline char
LOOP
dbms_output.put_line(substr(cmd_out,i,j-i)); -- print the line
IF ( j = len ) THEN
EXIT;
END IF;
i := j + 1; -- start after last newline
j := instr(cmd_out,cnl,i,1); -- find the next newline
END LOOP;
END IF;

end RunOsCmd;

END osutil;
/

Now when I Run


Sql > execute osutil.runoscmd('pwd');

output is ‘/home/oracle8’

But when is run

Sql > execute osutil.runoscmd('ls');

It is returning
'ERROR, bad command or no output returned...'

can u help out in this ………………………………………..