Re: Extproc listener configuration

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.
>
Received on Thu Jul 22 1999 - 07:53:26 CDT