fishScript.com Script con annotazioni in italiano e inglese

PLSQL

Home >PLSQL > Gestire LOB objects

Obiettivi:

Caricare file su campo BLOB di tabella;
Query per visualizzare contenuto BLOB;
Esportare su filesystem contenuto BLOB in tabella;

Da Oracle 10g,l'oggetto directory è utilizzato per gestire file, pertanto creiamo una directory, prima sul file system del server Oracle, da registrare successivamente nel database:

[oracle@serverX1 ~]$ mkdir /tmp/dir_documents

Per questo esempio, copiamo alcuni file di dimensioni ragionevoli nella cartella di varie tipologie e creiamo anche al volo un piccolo file di testo, per una successiva verifica da query del caricamento in BLOB.

[oracle@serverX1 dir_documents]$ echo "Hello From the FishScript.com to all tecs around the world" > shortText.txt

Pertanto sulla directory /tmp/dir_documents ci sono adesso alcuni file di esempio da caricare sul database:

[oracle@serverX1 dir_documents]$ ll -latr
total 108
-rw-r----- 1 oracle oinstall 94379 May  5 11:25 alert_CUSTOMER01.log
-rw-r--r-- 1 oracle oinstall   174 May  5 14:31 foto01.gif
-rw-r--r-- 1 oracle oinstall    62 May  5 15:07 shortText.txt

Adesso, accediamo nel database, creiamo tabella, directory e procedura di esempio.
Per semplificare, viene utilizzato lo schema SYSTEM, in altri casi ricordarsi di assegnare eventualmente privilegi di lettura/scrittura sulla directory allo schema utilizzato

[oracle@serverX1 ]$ sqlplus system/manager

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 5 18:07:01 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- creare la directory DIR_DOCUMENTS
CREATE DIRECTORY DIR_DOCUMENTS as '/tmp/dir_documents';

Directory created.
-- creiamo anche la tabella DOCUMENTS, sempre buona pratica allocare colonne di tipo blob in tablespace dedicata.

CREATE TABLE DOCUMENTS( FILE_NAME VARCHAR2(32), BDATA BLOB) TABLESPACE USERS LOB (BDATA) STORE AS (TABLESPACE LOBDATA);

Table created.

Adesso creare la procedura PRO_FILE_TO_BLOB, che deve caricare in tabella il file posizionato nella passato come parametro dalla directory definita. I punti principali del codice sono:

colonna file_name --> il nome del file passato in procedura
colonna BDATA instanziata comunque con la funzione empty_blob
il file viene aperto,letto e caricato ciclando a secondo della dimensione

Ecco la procedura:

CREATE OR REPLACE  PROCEDURE PRO_FILE_TO_BLOB(VFILENAME IN VARCHAR2) AS
L_BLOB BLOB;
BLOB_DEST BLOB;
L_BFILE BFILE;

BEGIN

INSERT INTO DOCUMENTS (FILE_NAME,BDATA) VALUES (VFILENAME,EMPTY_BLOB);
SELECT BDATA INTO BLOB_DEST FROM DOCUMENTS WHERE FILE_NAME = VFILENAME;
L_BFILE := BFILENAME ('DIR_DOCUMENTS', VFILENAME);
DBMS_LOB.OPEN(L_BFILE,DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(BLOB_DEST,DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(BLOB_DEST, L_BFILE,DBMS_LOB.LOBMAXSIZE,1,1);
DBMS_LOB.CLOSE(BLOB_DEST);
DBMS_LOB.CLOSE(L_BFILE);
COMMIT;
END;
/
Procedure created.

-- per eseguire la procedura insere i nomi dei file da caricare:

exec PRO_FILE_TO_BLOB('shortText.txt'); :

SQL>exec PRO_FILE_TO_BLOB('shortText.txt');

PL/SQL procedure successfully completed.

SQL>exec PRO_FILE_TO_BLOB('alert_CUSTOMER01.log');

PL/SQL procedure successfully completed.

SQL>exec PRO_FILE_TO_BLOB('foto01.gif');

PL/SQL procedure successfully completed.

Adesso verifichiamo se le righe sono state caricate:

SQL> col file_name format a20;
SQL> select file_name from documents;
FILE_NAME
--------------------
shortText.txt
alert_CUSTOMER01.log
foto01.gif

Adesso verifichiamo anche con una rapida query se il file più semplice è stato caricato, non aspettarsi di visualizzare risultati utili via query per i file nei formati binary (gif, doc, etc):

SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR(BDATA,25000, 1)) BDATA FROM SYS.DOCUMENTS WHERE FILE_NAME ='shortText.txt';

BDATA
-------------------------------------------------------------
Hello From the FishScript.com to all \n tecs around the world

Adesso, invertiamo l'operazione creando una procedura che esporti il contenuto di colonna oracle BLOB su file.
Creiamo una directory di destinazione su fylesystem

[oracle@serverX1 tmp]$ mkdir /tmp/bdata_on_filesystem

Connettiamoci nuovamente al database, pre registrare la directory di destinazione, e la procedura di esportazione.

[oracle@serverX1 ] sqlplus system/manager  (...)

SQL> CREATE OR REPLACE DIRECTORY BLOB_TO_FILESYSTEM AS '/tmp/bdata';

Directory created.

-- quindi creiamo la procedura PRO_BLOB_TO_FILE

CREATE OR REPLACE PROCEDURE PRO_BLOB_TO_FILE(VFILENAME IN VARCHAR2) AS
  VBLOB         BLOB;
  VBLOB_LENGTH    INTEGER;
  VOUT_FILE       UTL_FILE.FILE_TYPE;
  V_BUFFER       RAW(32767);
  VCHUNK_SIZE     BINARY_INTEGER := 32767;
  BLOB_POSITION  INTEGER := 1;
BEGIN

SELECT BDATA INTO VBLOB FROM DOCUMENTS WHERE FILE_NAME = VFILENAME ;
VBLOB_LENGTH:=DBMS_LOB.GETLENGTH(VBLOB);
VOUT_FILE := UTL_FILE.FOPEN ('BLOB_TO_FILESYSTEM',VFILENAME, 'WB', VCHUNK_SIZE);
  -- WRITE THE BLOB TO FILE IN CHUNKS 
  WHILE BLOB_POSITION <= VBLOB_LENGTH LOOP
   	 IF BLOB_POSITION + VCHUNK_SIZE - 1 > VBLOB_LENGTH THEN
      VCHUNK_SIZE := VBLOB_LENGTH - BLOB_POSITION + 1;
    END IF;
    DBMS_LOB.READ(VBLOB, VCHUNK_SIZE, BLOB_POSITION, V_BUFFER);
    UTL_FILE.PUT_RAW(VOUT_FILE, V_BUFFER, TRUE);
    BLOB_POSITION := BLOB_POSITION + VCHUNK_SIZE;
  END LOOP;
  -- CLOSE THE FILE HANDLE
  UTL_FILE.FCLOSE (VOUT_FILE);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (' PARAMETRI NON CORRETTI, CONTROLLA OGGETTO E PRIVILEGI DIRECTORY, NOME DEL FILE , TABELLA');
END;
/

Eseguire la procedura e verificare che il contenuto del BLOB sia stato exportato su filesystem:

SQLPLUS> exec PRO_BLOB_TO_FILE('foto01.gif');

Segue anche un esempio di blocco anonimo per esportare su filesystem i contenuti della colonna blob:

Esegue ciclo sulla tabella contenente i BLOB
Legge e scrive, in chunk i contenuto del BLOB su file
Nella directory Oracle vengono salvati i file denominati con il corrispondente campo in tabella

DECLARE
  VBLOB         BLOB;
  VBLOB_LENGTH    INTEGER;
  VOUT_FILE       UTL_FILE.FILE_TYPE;
  V_BUFFER       RAW(32767);
  VCHUNK_SIZE     BINARY_INTEGER := 32767;
  BLOB_POSITION  INTEGER := 1;
BEGIN

FOR R IN (   SELECT   FILE_NAME, BDATA FROM DOCUMENTS ) LOOP
VBLOB_LENGTH:=DBMS_LOB.GETLENGTH(R.BDATA);
VOUT_FILE := UTL_FILE.FOPEN ('BLOB_TO_FILESYSTEM',R.FILE_NAME, 'WB', VCHUNK_SIZE);

  WHILE BLOB_POSITION <= VBLOB_LENGTH LOOP
   	 IF BLOB_POSITION + VCHUNK_SIZE - 1 > VBLOB_LENGTH THEN
      VCHUNK_SIZE := VBLOB_LENGTH - BLOB_POSITION + 1;
    END IF;
    DBMS_LOB.READ(R.BDATA, VCHUNK_SIZE, BLOB_POSITION, V_BUFFER);
    UTL_FILE.PUT_RAW(VOUT_FILE, V_BUFFER, TRUE);
    BLOB_POSITION := BLOB_POSITION + VCHUNK_SIZE;
  END LOOP;
  -- CLOSE THE FILE HANDLE
  UTL_FILE.FCLOSE (VOUT_FILE);
BLOB_POSITION:=1;
END LOOP;
END;
/

1-dbms_lob inserting a file in a blob type | 2-dbms_lob updating blob type |

Warning: include(ads/text468x15.html): failed to open stream: No such file or directory in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Warning: include(): Failed opening 'ads/text468x15.html' for inclusion (include_path='.;C:\php\pear') in D:\inetpub\webs\fishscriptcom\documents\view_document.php on line 131

Tutorial

Flashback database to timestamp ripristinare il database ad una data determinata [Oracle]

Auditing for wrong sql How to intercept wrong dml sql statement [Oracle]

Using data pump in interactive mode [Oracle]

Alter session set current_schema How to query other user tables without specify the schema name [Oracle]

Procedura conteggio righe tutte le tabelle di uno schema esempio utilizzo NDS [PLSQL]

Inserire righe in tabelle utilizzando %rowtype scrivere codice pl/sql compatto [PLSQL]

Generazione di numeri casuali dbms_random.value [PLSQL]

Eseguire comando truncate di tutte le tabelle di uno schema atraverso dizionario dati e istruzione sql dinamica [PLSQL]

Dropping a database [Oracle]

Create new user basic sql statements [Oracle]

Viste note e appunti [Oracle]

NOT EXISTS clause basic example [Oracle]

Creazione di un database Oracle 10g da linea di comando esempio su sistemi Windows Server / XP: comandi e script [Oracle]

Clean up audit tables remove auditing logs [Oracle]

Nomenclatura oracle tipologie oggetti catalogo [Oracle]
Script

dbms_output.put_line Stampare spazi bianchi [Oracle]

Opzione DEFAULT per il valore di una colonna [Oracle]

Cercare le colonne in uno schema utilizzare le tabelle del dizionario dati di Oracle [Oracle]

Esempio script dos per eseguire export di schema automatizzare procedure di export e rinominare il file in base alla data corrente [Oracle]

Dato il nome di una vista ricava informazioni su tipo e attributi della colonna di riferimenti tabelle dizionario dati [Oracle]

Redo log ricollocare/trasferire i file di Redo [Oracle]

Scrivere su un file esempio con UTL_FILE package [PLSQL]

Leggere un file di testo [PLSQL]

Esempio funzione MOD in un loop ... [PLSQL]

Record sintassi di base: definizione, dichiarazione, assegnazione e stampa [PLSQL]

Errori intercettare codice e descrizione errori sql [PLSQL]

Esempio inserimento righe all'interno di un loop [PLSQL]

Cursore implicito SQL esempio con SQL%ROWCOUNT [PLSQL]

Esempio tipo VARRAY gestione array [PLSQL]

TYPE TABLE utilizzare le collections [PLSQL]
Comandi

Modificare il formato di default [Oracle]

systimestamp [Oracle]

CURRENT_TIMESTAMP [Oracle]

Spostare un indice in un diverso tablespace [Oracle]

analyze [Oracle]

Costruire istruzioni sql dinamiche il carattere speciale & [Oracle]

Exp utility esempio export di singole tabelle [Oracle]

Group by mese di colonna di tipo data [Oracle]

ALTER SEQUENCE Modificare una sequenza [Oracle]

Spazio libero nei tablespace utilizzo tabella DBA_FREE_SPACE [Oracle]

NLS_DATABASE_PARAMETERS ricavare i parametri del database [Oracle]

Lista control files [Oracle]

Spostare una tabella in un diverso tablespace [Oracle]

Creazione di una directory in ambiente oracle [Oracle]

Funzione TRUNC esempio visualizzazione primo giorno del mese corrente [Oracle]
Codici errore

Errore connessione client ORA-12638 Credential retreival failed [Oracle]

impdp / expdp importare in sql metadata del database datapump per esportare ed importare metatadati [Oracle]

ORA-06553: PLS-213,ORA-06553: PLS-213,ORA-06553: PLS-213 Errori in fase di creazione database, occorre eseguire gli script di catalog [ERRORI]

Operazioni sulle date utilizzando il nome dei mesi NLS_DATE_LANGUAGE ORA-01843: mese non valido / not a valid month [Oracle]

Argomenti

PLSQL

Cerca