Re: Extproc listener configuration

Thanks.. By the way, I did see a sample extproc.c file out there in /rdbms.
Where can I get more documentation on trying this stuff out? I tried the

Oracle 8 docs that came with the client CD, but they didn't really say much.
Will a UNIX .a style library serve the same purpose? Is metalink a good resource?
There must be some documentation on this somewhere or their wouldn't be a
sample file out there, no?

• Dc.

Pete Sharman wrote:

> Doug
>
> I only have 8.1 installed, but for that the make.bat file is simply:
>
> REM USAGE: just type MAKE
> cl -I. /LD -Zi extern.c /link msvcrt.lib /nod:libcmt /DLL
>
> Couldn't find the other .bat file Yass mentioned at all.
>
> HTH.
>
> Pete
>
> Doug Cowles wrote:
>
>> Can you help me out with those bat files? I don't see them on my
>> system.
>> - Dc.
>>
>> Yass Khogaly wrote:
>>
>> > PURPOSE
>> > =======
>> > This article explains about how to retrive the BLOB/CLOB/BFILE
>> > contents and write in to a file. PL/SQL program will retrive the
>> > BLOB/CLOB/BFILE contents and pass into the extrenal procedure.
>> > The external procedure will write the contents to a file.
>> >
>> > Description
>> > ===========
>> >
>> > Create a file extern.c file in the directory
>> > c:\orant\rdbms80\extproc
>> > directory.
>> >
>> > Before this take a backup of the file extproc.c file in this
>> > directory.
>> >
>> >
>> > The extern.c file :
>> > ===================
>> > #include <oci.h>
>> >
>> > #define NullValue -1
>> > #include<stdio.h>
>> > #include<string.h>
>> >
>> > long __declspec(dllexport) OutputString(context ,
>> > path , path_ind ,
>> > message , message_ind,
>> > filemode , filemode_ind
>> > ,
>> > len , len_ind )
>> >
>> > char *path;
>> > char *message;
>> > char *filemode;
>> > int len;
>> > OCIExtProcContext *context;
>> > short path_ind;
>> > short message_ind;
>> > short filemode_ind;
>> > short len_ind;
>> >
>> > {
>> >
>> > FILE *file_handle;
>> > int i ;
>> > char str[3];
>> > int value;
>> >
>> > /* Check whther any parameter passing is null */
>> >
>> > if (path_ind == OCI_IND_NULL || message_ind == OCI_IND_NULL ||
>> >
>> > filemode_ind == OCI_IND_NULL || len_ind == OCI_IND_NULL )
>> > {
>> > text *initial_msg = (text *)"One of the Parameter Has a
>> > Null Value
>> > !!! ";
>> > text *error_msg;
>> >
>> > /* Allocate space for the error message text, and set it up.
>> > We do not have to free this memory - PL/SQL will do that
>> > automatically. */
>> > error_msg = OCIExtProcAllocCallMemory(context,
>> > strlen(path) + strlen(initial_msg) + 1);
>> > strcpy((char *)error_msg, (char *)initial_msg);
>> > /*strcat((char *)error_msg, path); */
>> >
>> > OCIExtProcRaiseExcpWithMsg(context, 20001, error_msg, 0);
>> >
>> >
>> > /* OCIExtProcRaiseExcp(context, 6502); */
>> > return 0;
>> > }
>> >
>> >
>> > /* Open the file for writing. */
>> >
>> > file_handle = fopen(path, filemode);
>> >
>> > /* Check for success. If not, raise an error. */
>> >
>> > if (!file_handle) {
>> > text *initial_msg = (text *)"Cannot Create file ";
>> > text *error_msg ;
>> >
>> > /* Allocate space for the error message text, and set it up.
>> > We do not have to free this memory - PL/SQL will do that
>> > automatically. */
>> > error_msg = OCIExtProcAllocCallMemory(context,
>> > strlen(path) + strlen(initial_msg) + 1);
>> > strcpy((char *)error_msg, (char *)initial_msg);
>> > strcat((char *)error_msg, path);
>> >
>> >
>> > OCIExtProcRaiseExcpWithMsg(context, 20001, error_msg, 0);
>> > return 0;
>> > }
>> >
>> > i = 0;
>> >
>> > while (i < len)
>> > {
>> >
>> > /* Read the hexadecimal value(1). */
>> > str[0] = message[i];
>> >
>> > i++;
>> >
>> > /* Read the hexadecimal value(2). */
>> > str[1] = message[i];
>> >
>> > /* Convert the first byte to the binary value. */
>> > if (str[0] > 64 && str[0] < 71)
>> > str[0] = str[0] - 55;
>> > else
>> > str[0] = str[0] - 48;
>> >
>> > /* Convert the second byte to the binary value. */
>> > if (str[1] > 64 && str[1] < 71)
>> > str[1] = str[1] - 55;
>> > else
>> > str[1] = str[1] - 48;
>> >
>> > /* Convert the hex value to binary (first & second
>> > byte). */
>> > value = str[0] * 16 + str[1];
>> >
>> > /* Write the binary data to the binary file. */
>> > fprintf(file_handle,"%c",value);
>> >
>> > i++;
>> >
>> > }
>> >
>> > /* Output the string followed by a newline. */
>> >
>> > /* fwrite(message,len,1,file_handle); */
>> >
>> > /* Close the file. */
>> > fclose(file_handle);
>> > }
>> >
>> > Use the make.bat available in the c:\orant\rdbms80\extproc
>> > directory.
>> > You need to run vcvars32.bat file before running this batch file.
>> >
>> > This will create a dll file.
>> >
>> > I am attching the dll file for your convienence.
>> >
>> > Now you have to configure the tnsnames.ora and the listener.ora
>> > files.
>> >
>> > tnsnames.ora should contain the following entries along with your
>> > usual
>> > entries.
>> >
>> > extproc_connection_data.world =
>> > (DESCRIPTION =
>> > (ADDRESS =
>> > (PROTOCOL = IPC)
>> > (KEY = ORCL)
>> > )
>> > (CONNECT_DATA = (SID = extproc)
>> > )
>> > )
>> >
>> >
>> > The listener.ora should contain the following entries.
>> >
>> > # P:\ORANT\NET80\ADMIN\LISTENER.ORA Configuration
>> > File:p:\orant\net80\admin\listener.ora
>> > # Generated by Oracle Net8 Assistant
>> >
>> > LISTENER8 =
>> > (ADDRESS = (PROTOCOL = TCP)(HOST = winnt_nsc)(PORT = 1521))
>> >
>> > SID_LIST_LISTENER8=
>> > (SID_LIST =
>> > (SID_DESC =
>> > (GLOBAL_DBNAME = winnt_nsc)
>> > (SID_NAME = ORCL)
>> > )
>> > (SID_DESC =
>> > (SID_NAME = extproc)
>> > (PROGRAM = extproc)
>> > )
>> > )
>> >
>> > Login from sqlplus and issue the following statements.
>> >
>> > create library externProcedures as
>> > 'C:\orant\RDBMS80\EXTPROC\extern.dll';
>> >
>> >
>> > Create or replace PROCEDURE OutputString(
>> > p_Path IN VARCHAR2,
>> > p_Message IN VARCHAR2,
>> > p_mode in VARCHAR2,
>> > p_NumLines IN BINARY_INTEGER) AS EXTERNAL
>> > LIBRARY externProcedures
>> > NAME "OutputString"
>> > With context
>> > PARAMETERS (CONTEXT,
>> > p_Path STRING,
>> > p_path INDICATOR,
>> > p_Message STRING,
>> > p_message INDICATOR,
>> > p_mode STRING,
>> > p_mode INDICATOR,
>> > p_NumLines INT,
>> > p_numlines INDICATOR);
>> >
>> >
>> > This is the pl/sql block used to write the contents of the blob
>> > into a
>> > file.
>> > set the serveroutput on before running.
>> >
>> > SQL> desc lob_tab;
>> > Name Null? Type
>> > ------------------------------- -------- ----
>> > C1 NUMBER
>> > C2 BLOB
>> >
>> > lob_tab is the table which contains the blob data.
>> >
>> > declare
>> > i1 blob;
>> > len number;
>> > my_vr raw(10000);
>> > i2 number;
>> > i3 number := 10000;
>> > begin
>> > -- get the blob locator
>> > SELECT c2 INTO i1 FROM lob_tab WHERE c1 = 2;
>> > -- find the length of the blob column
>> > len := DBMS_LOB.GETLENGTH(i1);
>> > dbms_output.put_line('Length of the Column : ' ||
>> > to_char(len));
>> > -- Read 10000 bytes at a time
>> > i2 := 1;
>> > if len < 10000 then
>> > -- If the col length is < 10000
>> > DBMS_LOB.READ(i1,len,i2,my_vr);
>> >
>> > outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'wb',2*len);
>> > -- You have to convert the data to rawtohex format. Directly
>> > sending the
>> > buffer
>> > -- data will not work
>> > -- That is the reason why we are sending the length as the double
>> > the size
>> > of the data read
>> > dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
>> > else
>> > -- If the col length is > 10000
>> > DBMS_LOB.READ(i1,i3,i2,my_vr);
>> >
>> > outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'wb',2*i3);
>> > dbms_output.put_line('Read ' || to_char(i3) || ' Bytes ');
>> > end if;
>> > i2 := i2 + 10000;
>> > while (i2 < len ) loop
>> > -- loop till entire data is fetched
>> > DBMS_LOB.READ(i1,i3,i2,my_vr);
>> > dbms_output.put_line('Read ' || to_char(i3+i2-1) || ' Bytes
>> > ');
>> >
>> > outputstring('p:\bfiles\ravi.bmp',rawtohex(my_vr),'ab',2*i3);
>> > i2 := i2 + 10000 ;
>> > end loop;
>> > end;
>> >
>> >
>> > Regards
>> >
>> >
>> > "The Views expressed here are my own and not necessarily those of
>> > Oracle
>> > Corporation"
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > Doug Cowles <dcowles_at_bigfoot.com> wrote in message
>> > news:37937918.99E1370B_at_bigfoot.com...I'm trying to set up
>> > a database so that external procedures can be used.
>> > The following is straight out of the Oracle
>> > Documentation.
>> >
>> >
>> > -
>> > --------------------------------------------------------------------------------------------------------
>> >
>> > For environments where the configuration files have been
>> > overwritten, edit the LISTENER.ORA and TNSNAMES.ORA files
>> > as
>> > follows:
>> >
>> > 1.Add a system identifier (SID) name and a program
>> > name for EXTPROC in the server's LISTENER.ORA file
>> > (entries are
>> > shown in boldface text):
>> >
>> > LISTENER =
>> > (ADDRESS_LIST =
>> > (ADDRESS =
>> > (PROTOCOL = IPC)
>> > (KEY = INVENTORY)
>> > )
>> > (ADDRESS =
>> > (PROTOCOL = IPC)
>> > (KEY = ORCL)
>> > )
>> > (ADDRESS =
>> > (PROTOCOL = IPC)
>> > (KEY = extproc)
>> > )
>> > (ADDRESS =
>> > (PROTOCOL = TCP)
>> > (Host = INVENTORY)
>> > (PORT = 1521)
>> > )
>> > )
>> > STARTUP_WAIT_TIME_LISTENER = 0
>> > CONNECT_TIMEOUT_LISTENER = 10
>> > TRACE_LEVEL_LISTENER = ADMIN
>> > SID_LIST_LISTENER =
>> > (SID_LIST =
>> > (SID_DESC =
>> > (SID_NAME = ORCL)
>> > )
>> > (SID_DESC =
>> > (SID_NAME = extproc)
>> > (PROGRAM = extproc)
>> > )
>> > )
>> >
>> > 2.Add a service name entry for EXTPROC in the server's
>> > TNSNAMES.ORA file (entries are shown in boldface text):
>> >
>> > INVENTORYDB.WORLD =
>> > (DESCRIPTION =
>> > (ADDRESS =
>> > (PROTOCOL =
>> > TCP)
>> > (Host =
>> > INVENTORY)
>> > (PORT = 1521)
>> >
>> > )
>> > (CONNECT_DATA
>> > = (SID = ORCL)) )
>> > extproc_connection_data.world =
>> > (DESCRIPTION =
>> > (ADDRESS =
>> > (PROTOCOL = IPC)
>> > (KEY = ORCL)
>> > )
>> > (CONNECT_DATA = (SID=extproc))
>> > )
>> >
>> > Please note that the value for KEY must match the KEY
>> > value specified in the LISTENER.ORA file. In order to
>> > support a
>> > multiple Oracle home environment, the Oracle Installer
>> > automatically creates unique keys for the external
>> > procedures in different
>> > Oracle homes.
>> > -------------
>> > -------------------------------------------------------------------------------------
>> >
>> > Correct me if I'm wrong but it says that the value for
>> > KEY in tnsnames.ora must match
>> > the value for KEY in listener.ora. In their example, the
>> > KEY in tnsnames.ora is
>> > ORCL, and in listener.ora it's extproc, no? Do they
>> > really mean the SID? Or is
>> > this just a bad example on how to do it? (are these
>> > things case sensitive by the way).
>> >
>> > - Thanks,
>> >
>> > Dc.
>> >
> --
> Regards
>
> Pete
>
Received on Thu Jul 22 1999 - 21:03:06 CDT