How to use the LOB field to access the operating system binary file in the SQL * PLUS of Oracle 8i
Department of Engineering Control Information, Guangdong Nuclear Power Co., Ltd. (518124) Huang Fu
Oracle 8i database system features more perfect than previous versions, especially in BLOB, Clob, NClob, BFile these LOBs (large objects) types to replace LONG, longraw types. The BLOB field is 4G (4,294,967,295) bytes, and no longer like the longraw, only one field is LONGRAW (up to 2G). BLOB, Clob, NClob is internal BLOB (data usually stores in the database), bfile is external LOB (stored only points to the external operating system file), the user can use the PL / SQL package DBMS_LOB to process LOB data, but Unfortunately, the DBMS_LOB package can only write binary operating system files into the BLOB field, but it is impossible to retrieve binary operating system files in the BLOB field to the operating system, and it is estimated that there will be improved in the future. This article will explain the Word files in the SQL * Plus to remove the Word file in detail in detail, for each peer reference.
The software environment of the experiment is as follows: Windows 2000 Advanced Server, Oracle 8.1.7, VC 6.0 SP5 Hardware Environment is as follows: Dual PIII866 CPU, 768M Memory
Under the INTERNAL, apply to the Scott User as follows: SQL> Grant Create Any Directory TO Scott; SQL> GRANT CREATE ANY LIBRARY TO SCOTT; Under Scott Under this user:
SQL> create table bfile_tab (bfile_column BFILE); SQL> create table utl_lob_test (blob_column BLOB); SQL> create or replace directory utllobdir as 'C: / DDS / EXTPROC'; SQL> set serveroutput on
Then the following statement is sent to the Word file COM.DOC under the C: / DDS / ExtProc directory into the BLOB_COLUMN field in the UTL_LOB_TEST table.
DECLARE A_BLOB BLOB; A_BFILE BFILE: = BFileName ('UtllobDir', 'com.doc'); - Used to point to external operating systems
File begin insert into bfile_tab values (a_bfile) returning bfile_column into a_bfile; insert into utl_lob_test values (empty_blob ()) returning blob_column into a_blob; dbms_lob.fileopen (a_bfile); dbms_lob.loadfromfile (a_blob, a_bfile, dbms_lob.getlength (a_bfile)) DBMS_LOB.FILECLOSE (A_BFILE); commit; end; / sql> show ErroRs This time you can use the DBMS_LOB package with this procedure to detect if the Word file has been stored in the blob field. Such as: SQL> SELECT DBMS_LOB.GETLENGTH (blob_column) from UTL_LOB_TEST; the results are as follows: dbms_lob.getlength (blob_column) --------------------------- ---- 83968 Description This Word file has been stored in the blob field. The following will explain how to remove the Word file to the operating system: Oracle8.1.7 can only implement this task with Pro * C and OCI, so the Oracle server side must support Pro * C and external library, oracle8.1.7 database default Install to support Pro * C and external procedure, users can check the two files of listener.ora and tnsnames.ora. listener.ora contains the following statement: SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D: / oracle / ora81) (PROGRAM = extproc)) (SID_DESC = (GLOBAL_DBNAME = hft) (ORACLE_HOME = D: / oracle / ora81) (SID_NAME = hft))) tnsnames.ora contains the following statement: EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))) (CONNECT_DATA = (SID = PLSExtProc (Presentation = RO)))))
The following file is LOB2FILE.C, and the specific role is to pour the binaries in the BLOB into the operating system.
/ * begin of lob2file.c * / # include
NULL) {fprintf (logfilep, "error: unable to open logfile% s / n", logfile; fprintf (logfilep, "error: errno =% d / n", errno);}}} / * * Retrieve the environment , service context, and error handles * / if (! (errnum = OCIExtProcGetEnv (ctxt, & envhp, & svchp, & errhp)) = OCIEXTPROC_SUCCESS) {if (logging && logfilep = NULL!) {fprintf (logfilep, "error: Call to OCIExtProcGetEnv FAILED / N "); FPRINTF (logfilep," error: OciextProcGetenv returned% D / N ",
Errnum); fclose (logfilep); return -1;}} / * * verify what the user Has output a name for the output file * / if (pind == -1 || Plen == 0) {char * errmsg = "Pathname is null or empty string"; if (logging && logfilep = nULL!) {fprintf (logfilep, "Error:% s / n", errmsg); fclose (logfilep);} errnum = 20001; OCIExtProcRaiseExcpWithMsg (ctxt, errnum (Text *) errmsg,
Strlen (errmsg)); return -1;} else / * use the provided name * / {strncpy (lobfile, plent, plen); lobfile [Plen] = '/ 0';} / * * verify That User Has Provided A Valid LOCATOR * / IF (Lbind == -1) {char * errmsg = "LOB LOCATOR is NULL"; if (logging && logfilep! = null) {fprintf (logfilep, "error:% s / n", errmsg ); Fclose (logfilep);} errnum = 20002; OciextProcraiseexcpwithmsg (ctxt, errnum, (text *) errmsg,
Strlen (errmsg)); RETURN-1;} if (logging && logfilep! = null) fprintf (logfilep, "opening os file in write mode / n"); / * Open the output file for Writing * / IF ((( LobFilep = fopen (Lobfile, "WB")))! = null) {DVOID * chun; ub4 cksz = 0, Totsz = 0; if (logging && logfilep! = null) fprintf (logfilep, "getting total size for LOB / N "); If (CTXT, Errhp, Ocilobgetlength (SVCHP, Errhp, A_LOB, & TOTSZ))! = 0) Return -1; / * * for 8.0.x the OciloggetChunksize Will Not Have Been Called. * In this case, Reset the chunk size to 1k. * / if (cksz == 0) CKSZ = default_chunk_size; if (logging && logfilep! = null) fprintf (logfilep, "allocating% d bytes of memory for lob chunks / n", (int) CKSZ); / * * Dynamically Allocate Enough Memory to Hold A Single Chunk * / if ((Chunk = OciefProcalloccallMemory)! = NULL ) {Int CNT = 1; UB4 AMT = CKSZ, OFFSET = 1; / * r = t * more data remains. * / While (OFFSET <(int) Totsz) {ix ( Logging && logfilep! = null) fprintf (logfilep, "reading chun% d starting at% d for max% d
Bytes / N ", CNT, (int) offset, (int) AMT); errnum = Ocilobread (SVCHP, Errhp, A_LOB, & AMT, OFFSET, CHUNK, CKSZ, (DVOID *) 0 (SB4 (*) (DVOID * , DVOID *, UB4, UB1) 0, (UB2) 0, (UB1) SQLCS_IMPLICIT; IF (CHECKERR (CTXT, Errhp, Errnum)! = 0) Return -1; if (Logging && logfilep! = null) FPRINTF (logfilep, "successfully read chunk contraining% D Bytes / N", (int) AMT); if (logging && logfilep! = null) fprintf (logfilep, "Writing% D bytes of chunk% D to File% S / N" , (Int) AMT, CNT, LobFile; IF (FWRIT ((Void *) Chunk, (SIZE_T) 1, (SIZE_T) AMT, LOBFILEP) == AMT) {IF (Logging && logfilep! = Null) FPRINTF (logfilep , "SuccessFully wrote% D bytes to file
% S / N ", (int) AMT, LOBFILE);} else {char * errmsg =" Write to OS file failed "; if (logging && logfilep! = null) {fprintf (logfilep," error:% S / N ", errmsg); fprintf (logfilep," error: errno =% d / n ", errno);} errnum = 20003; OciextProcraiseExcpwithmsg (ctxt, errnum, (text *) errmsg, strlen (errmsg)); return -1; } CNT ; offset = Amt;} if (logfilep! = Null) fclose (logfilep); fclosep; Return 0;} else {if (logging && logfilep! = Null) {fprintf (logfilep, "error: unable TO Allocate Memory / N "); fclose (logfilep);} return -1;}} else {char * errmsg =" unable to open file "; if (logging && logfilep! = null ) {FPRINTF (logfilep, "error:% s% s / n", errmsg, lobfile; fprintf (logfilep, "error: errno =% d / n", errno; fclose (logfilep);} errnum = 20003; OciextProcraiseexcpwithmsg (CTXT, Errnum, (Text *) errmsg, strlen (errmsg)); Return -1;}}
int checkerr (OCIExtProcContext * ctxt, OCIError * errhp, sword status) {sword errnum = 0; text errbuf [512]; switch (status) {case OCI_SUCCESS_WITH_INFO: errnum = 20004; strcpy ((char *) errbuf, "Error: OCI_SUCCESS_WITH_INFO "); break; case OCI_NO_DATA: errnum = 20005; strcpy ((char *) errbuf," Error: OCI_NO_DATA "); break; case OCI_NEED_DATA: errnum = 20006; strcpy ((char *) errbuf," Error: OCI_NEED_DATA ") ; break; case OCI_INVALID_HANDLE: errnum = 20007; strcpy ((char *) errbuf, "Error: OCI_INVALID_HANDLE"); break; case OCI_STILL_EXECUTING: errnum = 20008; strcpy ((char *) errbuf, "Error: OCI_STILL_EXECUTING"); break Case OCI_Continue: errnum = 20009; STRNUM = 20009; STRCPY ((char *) Errbuf, "error: OCI_Continue"); Break; Case OCI_ERROR: (VOID) OCIERORGET ((DVOID *) Errhp, (UB4) 1, (Text *) NULL, (SB4 *) & errnum, (Text *) ERR BUF, (UB4) SIZEOF (Errbuf), OCI_HTYPE_ERROR); Break; default: Break;} if (errnum! = 0) {if (logging && logfilep! = null) {fprintf (logfilep, "error:% D% s / N ", errnum, errbuf; fclose (logfilep);} (void) OciextProcraiseexcpwithmsg (ctxt, errnum, errbuf, strlen (errbuf));} return errnum;} / * end of file lob2file.c * /
Put the file lob2file.c in the D: / Oracle / ORA81 / PLSQL / DEMO directory: and then perform the following in DOS
The translation statement compiles the file into a lob2file.dll file, and the make.bat file contains the following:
@echo offcl -id: / oracle / ora81 / oci / include -d_dll -d_mt / ld -zi lob2file.c / link d: /oacle/ora81/oci/lib/msvc/oci.lib msvcrt.lib / NOD: LIBCMT / DLL / EXPORT: LOB2FILE / EXPORT: Checkerr enters the D: / ORACLE / ORA81 / PLSQL / DEMO directory (DOS state) Perform make if you can compile LOB2FILE.D to LOB2FILE.DLL files.
Then use Scott to SQL * Plus, execute SQL> Create or Replace Library UTLOBLIB AS 'D: / Oracle/ora81/plsqler/demo/lob2file.dll'/sql> Grant Execute ON Utlloblib to Public Execute
Then performs the following codes: create or replace package utl_lob is procedure SetLogging (which BOOLEAN, a_log VARCHAR2); procedure UnloadToFile (a_lob BLOB, a_file VARCHAR2, status OUT NUMBER); end utl_lob; / show errorscreate or replace package body utl_lob is logSetting BOOLEAN : = FALSE; logFileName VARCHAR2 (512): = NULL; procedure SetLogging (which BOOLEAN, a_log VARCHAR2) is begin logSetting: = which; if (logSetting = TRUE) then logFileName: = a_log; else logFileName: = NULL; end if; END;
Function Lobtofile (A_LOB BLOB, A_FILE VARCHAR2, A_LOG VARCHAR2, LOGGING
BOOLEAN) return BINARY_INTEGER as external name "lob2file" library utlloblib LANGUAGE C with context parameters (a_lob OCILOBLOCATOR, a_lob INDICATOR SHORT, a_file STRING, a_file INDICATOR SHORT, a_file LENGTH INT, a_log STRING, a_log INDICATOR SHORT, a_log LENGTH INT, logging INT, CONTEXT, RETURN); procedure UnloadToFile (a_lob BLOB, a_file VARCHAR2, status OUT NUMBER) is begin status: = LobToFile (a_lob, a_file, logFileName, logSetting); end; end utl_lob; / show errorsgrant execute on utl_lob to public;
This code creates package utl_lob, and UTL_LOB calls library utlloblib, our test program calls Procedure setLogging and UnloadTofile in package utl_lob. Under Scott users, you can put out the previously saved com.doc to C: /DDS/ExtProc/test.doc,
However, the directory of C: / DDS / EXTPROC must exist. After the script is executed, build two files Test.log and Test.doc
Test.log records the detailed information, Test.doc is a replica of com.doc, takes out 82K size files.
It took 4 seconds.
- The following test script set serveroutput ondeclare a_blob BLOB; status NUMBER; begin select blob_column into a_blob from utl_lob_test; utl_lob.SetLogging (TRUE, 'C: /DDS/EXTPROC/test.log'); utl_lob.UnloadToFile (a_blob, ' C: /dds/extProc/test.doc ', status; dbms_output.put_line (' exit status = '|| status); END; / Everyone is slightly changed to the above test script, forming a parametric Procedure Supply Program Call
Have.
