Home » SQL & PL/SQL » SQL & PL/SQL » 32,000 chars + into BLOB (Orcale 11g)
32,000 chars + into BLOB [message #326211] Tue, 10 June 2008 12:41 Go to next message
djbuncle
Messages: 1
Registered: June 2008
Junior Member
Hi.
Just wondering whether anyone has a method to insert text of over 32,000 chars into a field of type BLOB. I can achieve smaller strings by using the below method (as that's as much as a local variable allows):

declare myclob clob default 'Text less than 32,000 chars'
insert into my_table(id,blob_data) values('1',gzip.clob_compress(myclob));


But I'm not sure how to do this for data of more chars. Keeping in mind that it HAS to be stored as a BLOB and compressed.

Thanks,
Dave
Re: 32,000 chars + into BLOB [message #326214 is a reply to message #326211] Tue, 10 June 2008 12:53 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
does anything below appear helpful to your question/problem?
SQL> desc dbms_lob
PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BLOB                    IN    
PROCEDURE APPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        CLOB                    IN    
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
PROCEDURE CLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_1                          BLOB                    IN    
 LOB_2                          BLOB                    IN    
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_1                          CLOB                    IN    
 LOB_2                          CLOB                    IN    
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
FUNCTION COMPARE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_1                         BINARY FILE LOB         IN    
 FILE_2                         BINARY FILE LOB         IN    
 AMOUNT                         NUMBER(38)              IN    
 OFFSET_1                       NUMBER(38)              IN     DEFAULT
 OFFSET_2                       NUMBER(38)              IN     DEFAULT
PROCEDURE CONVERTTOBLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_CLOB                       CLOB                    IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BLOB_CSID                      NUMBER                  IN    
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT   
PROCEDURE CONVERTTOCLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_BLOB                       BLOB                    IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BLOB_CSID                      NUMBER                  IN    
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT   
PROCEDURE COPY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BLOB                    IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE COPY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        CLOB                    IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE CREATETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 CACHE                          BOOLEAN                 IN    
 DUR                            BINARY_INTEGER          IN     DEFAULT
PROCEDURE CREATETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 CACHE                          BOOLEAN                 IN    
 DUR                            BINARY_INTEGER          IN     DEFAULT
PROCEDURE ERASE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE ERASE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE FILECLOSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
PROCEDURE FILECLOSEALL
FUNCTION FILEEXISTS RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
PROCEDURE FILEGETNAME
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
 DIR_ALIAS                      VARCHAR2                OUT   
 FILENAME                       VARCHAR2                OUT   
FUNCTION FILEISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
PROCEDURE FILEOPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN     DEFAULT
PROCEDURE FREETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
PROCEDURE FREETEMPORARY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
FUNCTION GETCHUNKSIZE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
FUNCTION GETCHUNKSIZE RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
FUNCTION GETLENGTH RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
FUNCTION GET_STORAGE_LIMIT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
FUNCTION GET_STORAGE_LIMIT RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
 PATTERN                        RAW                     IN    
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
 PATTERN                        VARCHAR2                IN    
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION INSTR RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
 PATTERN                        RAW                     IN    
 OFFSET                         NUMBER(38)              IN     DEFAULT
 NTH                            NUMBER(38)              IN     DEFAULT
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
FUNCTION ISOPEN RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
FUNCTION ISTEMPORARY RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
FUNCTION ISTEMPORARY RETURNS NUMBER(38)
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
PROCEDURE LOADBLOBFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_BFILE                      BINARY FILE LOB         IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
PROCEDURE LOADCLOBFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_BFILE                      BINARY FILE LOB         IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN/OUT
 SRC_OFFSET                     NUMBER(38)              IN/OUT
 BFILE_CSID                     NUMBER                  IN    
 LANG_CONTEXT                   NUMBER(38)              IN/OUT
 WARNING                        NUMBER(38)              OUT   
PROCEDURE LOADFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       BLOB                    IN/OUT
 SRC_LOB                        BINARY FILE LOB         IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE LOADFROMFILE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DEST_LOB                       CLOB                    IN/OUT
 SRC_LOB                        BINARY FILE LOB         IN    
 AMOUNT                         NUMBER(38)              IN    
 DEST_OFFSET                    NUMBER(38)              IN     DEFAULT
 SRC_OFFSET                     NUMBER(38)              IN     DEFAULT
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN    
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN    
PROCEDURE OPEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN/OUT
 OPEN_MODE                      BINARY_INTEGER          IN     DEFAULT
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN    
 BUFFER                         RAW                     OUT   
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN    
 BUFFER                         VARCHAR2                OUT   
PROCEDURE READ
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
 AMOUNT                         NUMBER(38)              IN/OUT
 OFFSET                         NUMBER(38)              IN    
 BUFFER                         RAW                     OUT   
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN    
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN    
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN    
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
PROCEDURE TRIM
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 NEWLEN                         NUMBER(38)              IN    
PROCEDURE TRIM
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 NEWLEN                         NUMBER(38)              IN    
PROCEDURE WRITE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN    
 OFFSET                         NUMBER(38)              IN    
 BUFFER                         RAW                     IN    
PROCEDURE WRITE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN    
 OFFSET                         NUMBER(38)              IN    
 BUFFER                         VARCHAR2                IN    
PROCEDURE WRITEAPPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN    
 BUFFER                         RAW                     IN    
PROCEDURE WRITEAPPEND
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN/OUT
 AMOUNT                         NUMBER(38)              IN    
 BUFFER                         VARCHAR2                IN    
Previous Topic: Pl/sql query -- grant
Next Topic: Update statment
Goto Forum:
  


Current Time: Tue Dec 06 04:43:50 CST 2016

Total time taken to generate the page: 0.06861 seconds