| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Can a stored procedure return a BLOB
this article explains how to retrieve BLOB/CLOB/BFILE contents and write it
toa file. A PL/SQL program will retrieve the BLOB/CLOB/BFILE contents and
pass it to an external procedure. The external procedure will write the
contents to a file.
"The Views expressed here are my own and not necessarily those of Oracle Corporation"
Description
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;
short path_ind; short message_ind; short filemode_ind; short len_ind;
{
FILE *file_handle;
int i ;
char str[3];
int value;
/* Check whether 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 Parameters 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);
}
3. 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.
4. Configure the tnsnames.ora and the listener.ora files.
The tnsnames.ora should contain the following 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)
)
5. 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
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 serveroutput on before running it.
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
 |
 |