Re: Extproc listener configuration

Doug

Have a look at the Developer's Guide, I think it's in there. Or do a search on technet looking for extproc. I came up with 45 hits by doing that.

HTH. Pete

Doug Cowles wrote:

> 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
>>
>

--
Regards

Pete

Received on Fri Jul 23 1999 - 10:53:22 CDT