update : 28-06-2017

DBA Script : example_lob_demonstration.sql

-- | FILE : example_lob_demonstration.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : example_lob_demonstration.sql |
-- | CLASS : Examples |
-- | PURPOSE : Example script that demonstrates how to manipulate LOBs using |
-- | SQL. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
Prompt
Prompt Dropping all testing LOB tables...
Prompt ----------------------------------
DROP TABLE test_lobs;
DROP DIRECTORY tmp_dir;
DROP TABLE long_data;
Prompt Create TEST_LOB table...
Prompt ------------------------
CREATE TABLE test_lobs (
c1 NUMBER
, c2 CLOB
, c3 BFILE
, c4 BLOB
)
LOB (c2) STORE AS (ENABLE STORAGE IN ROW)
LOB (c4) STORE AS (DISABLE STORAGE IN ROW)
/
Prompt Inserting row with NO LOB locators...
Prompt -------------------------------------
INSERT INTO test_lobs
VALUES (1, null, null, null)
/
Prompt Inserting row with LOB locators (locators created but point to nothing) ...
Prompt ---------------------------------------------------------------------------
INSERT INTO test_lobs
VALUES (2, empty_clob(), BFILENAME(null,null), empty_blob())
/
Prompt +------------------------------------------------------------------------+
Prompt | It is possible to insert data directly up to 4K. |
Prompt | Even though you are only really accessing the locator, the data is |
Prompt | stored as appropriate behind the scenes. When inserting directly into |
Prompt | a BLOB either the string must be hex as an implicit HEXTORAW will be |
Prompt | done or you can call UTL_RAW.CAST_TO_RAW('the string') to convert it |
Prompt | for you. Note '48656C6C6F' = 'Hello'. |
Prompt +------------------------------------------------------------------------+
Prompt
INSERT INTO test_lobs
VALUES ( 3
, 'Some data for record 3.'
, BFILENAME(null,null)
, '48656C6C6F'||UTL_RAW.CAST_TO_RAW(' there!'))
/
Prompt +---------------------------------------------------------------------------+
Prompt | Now it is time to select back the data. If you were to try to SELECT |
Prompt | all three columns from the test_lobs table, SQL*Plus would give you an |
Prompt | error: |
Prompt | SQL> SELECT * FROM test_lobs; |
Prompt | SQL> Column or attribute type can not be displayed by SQL*Plus |
Prompt | |
Prompt | SQL*Plus cannot convert the data behind the locator to hex for the BLOB |
Prompt | column (c3) nor can it interpret a locator for a BFILE (even when null). |
Prompt | In order for this query to run successfully, you would need to enter: |
Prompt | |
Prompt | column c2 format a60 wrap |
Prompt | |
Prompt | and ONLY select columns c1 and c2: |
Prompt +---------------------------------------------------------------------------+
Prompt
COLUMN c2 FORMAT a60 WRAP
Prompt Query CLOB records...
Prompt ---------------------
SELECT c1, c2 FROM test_lobs;
Prompt +---------------------------------------------------------------------------+
Prompt | In the above query, we are really fetching only the LOB locator. SQL*Plus |
Prompt | will also then fetch the corresponding data. If we use a 3GL or PL/SQL we |
Prompt | can insert data from a character string variable but not select it into |
Prompt | one. For example: |
Prompt +---------------------------------------------------------------------------+
Prompt
DECLARE
c_lob VARCHAR2(10);
BEGIN
c_lob := 'Record 4.';
INSERT INTO test_lobs
VALUES (4,c_lob,BFILENAME(null,null), EMPTY_BLOB());
END;
/
DECLARE
c_lob VARCHAR2(10);
BEGIN
SELECT c2 INTO c_lob
FROM test_lobs
WHERE c1 = 4;
END;
/
Prompt +-------------------------------------------------------------+
Prompt | NEW IN 8i |
Prompt | ========= |
Prompt | From version 8.1 it is now possible to convert data stored |
Prompt | in longs and long raws to CLOBs and BLOBs respectively. |
Prompt | This is done using the TO_LOB function. |
Prompt +-------------------------------------------------------------+
Prompt
Prompt Create LONG_DATA table...
Prompt -------------------------
CREATE TABLE long_data (
c1 NUMBER
, c2 LONG
)
/
INSERT INTO long_data
VALUES (1, 'This is some long data to be migrated to a CLOB')
/
Prompt TO_LOB may be used in CREATE TABLE AS SELECT or INSERT...SELECT statements
Prompt ---------------------------------------------------------------------------
INSERT INTO test_lobs
SELECT 5, TO_LOB(c2), null, null
FROM long_data
/
Prompt Query CLOB records...
Prompt ---------------------
SELECT c1, c2
FROM test_lobs
WHERE c1 = 5;
ROLLBACK
/
Prompt Creating text file /tmp/rec2.txt...
Prompt -----------------------------------
!echo "This is some data for record 2's BFILE column. The data is\nstored in a file called \"rec2.txt\". The file is placed in \n/tmp.\nThe file comprises a total of 4 lines of text." > /tmp/rec2.txt
Prompt Creating text file /tmp/rec3.txt...
Prompt -----------------------------------
!echo "This is some data for record 3's BFILE column. The data is\nstored in a file called \"rec3.txt\". The file is placed in\n/tmp. The file comprises a total of 5 lines of text and\nwill be used to demonstrate the functionality of the \nDBMS_LOB package." > /tmp/rec3.txt
Prompt First create the ALIAS for the directory /tmp
Prompt ---------------------------------------------
CREATE DIRECTORY tmp_dir AS '/tmp'
/
Prompt +------------------------------------------------------------+
Prompt | Now update the records to associate the BFILE column with |
Prompt | the two files created above. |
Prompt +------------------------------------------------------------+
Prompt
UPDATE test_lobs
SET c3 = BFILENAME('TMP_DIR','rec2.txt')
WHERE c1 = 2
/
UPDATE test_lobs
SET c3 = BFILENAME('TMP_DIR','rec3.txt')
WHERE c1 = 3
/
commit;
Prompt +---------------------------------------------------------------------+
Prompt | Note the files associated with these columns are READ-ONLY through |
Prompt | Oracle. |
Prompt | They must be maintained via the operating system itself. To access |
Prompt | the BFILE columns you must use the DBMS_LOB package or OCI. |
Prompt | |
Prompt | Getting lengths of the LOB data. Notice the zero lengths where |
Prompt | "empty" locators were specified. |
Prompt +---------------------------------------------------------------------+
COLUMN len_c2 FORMAT 9999
COLUMN len_c3 FORMAT 9999
COLUMN len_c4 FORMAT 9999
SELECT
c1
, DBMS_LOB.GETLENGTH(c2) len_c2
, DBMS_LOB.GETLENGTH(c3) len_c3
, DBMS_LOB.GETLENGTH(c4) len_c4
FROM test_lobs
/
Prompt +---------------------------------------------------------------------------------+
Prompt | Using SUBSTR/INSTR - both may be used on all 3 types (CLOB, BLOB and --BFILE) |
Prompt | however for BFILEs the file must first have been opened - hence the functions |
Prompt | may only be used within PL/SQL in this case. |
Prompt | For SUBSTR the parameters are LOB, amount, offset - the opposite to the |
Prompt | standard substr function; for INSTR they are LOB, string, offset, occurence, |
Prompt | the latter 2 defaulting to 1 if omitted. So the following does a substr from |
Prompt | offset 3 in the CLOB for 9 characters and returns the first occurence of the |
Prompt | binary string representing "ello" in the BLOB. |
Prompt +---------------------------------------------------------------------------------+
COLUMN sub_c2 FORMAT a10
COLUMN ins_c4 FORMAT 99
SELECT
c1
, DBMS_LOB.SUBSTR(c2,9,3) sub_c2
, DBMS_LOB.INSTR(c4,UTL_RAW.CAST_TO_RAW('ello'),1,1) ins_c4
FROM test_lobs
/
Prompt +----------------------------------------------------------------+
Prompt | The following PL/SQL block demonstrates some of the DBMS_LOB |
Prompt | functionality. Note the use of "set long 1000" to prevent the |
Prompt | output data from being truncated. |
Prompt +----------------------------------------------------------------+
SET SERVEROUTPUT ON
SET LONG 1000
DECLARE
b_lob BLOB;
c_lob CLOB;
c_lob2 CLOB;
bf BFILE;
buf varchar2(100) :=
'This is some text to put into a CLOB column in the' ||
chr(10) ||
'database. The data spans 2 lines.';
n number;
fn varchar2(50); --Filename
fd varchar2(50); --Directory alias
--Procedure to print out the LOB value from c_lob, one line
--at a time..
PROCEDURE print_clob IS
offset number;
len number;
o_buf varchar2(200);
amount number; --}
f_amt number := 0; --}To hold the amount of data
f_amt2 number; --}to be read or that has been
amt2 number := -1; --}read
BEGIN
len := DBMS_LOB.GETLENGTH(c_lob);
offset := 1;
WHILE len > 0 loop
amount := DBMS_LOB.INSTR(c_lob,chr(10),offset,1);
--Amount returned is the count from the start of the file,
--not from the offset.
IF amount = 0 THEN
--No more linefeeds so need to read remaining data.
amount := len;
amt2 := amount;
ELSE
f_amt2 := amount; --Store position of next LF
amount := amount - f_amt; --Calc position from last LF
f_amt := f_amt2; --Store position for next time
amt2 := amount - 1; --Read up to but not the LF
END IF;
IF amt2 != 0 THEN
--If there is a linefeed as the first character then ignore.
DBMS_LOB.READ(c_lob,amt2,offset,o_buf);
dbms_output.put_line(o_buf);
END IF;
len := len - amount;
offset := offset+amount;
END LOOP;
END;
BEGIN
--For record 1 we did not initialise the locators so do so now.
--Note the RETURNING clause will retrieve the new lob locators so
--we do not need to perform an extra select. The update also
--ensures the corresponding row is locked.
UPDATE test_lobs SET c2 = EMPTY_CLOB(), c4 = EMPTY_BLOB()
WHERE c1 = 1 RETURNING c2, c4 INTO c_lob, b_lob;
--Also select the CLOB locator for record 2.
SELECT c2 INTO c_lob2 FROM test_lobs where c1 = 3;
--Write the above buffer into the CLOB column. Offset is 1, amount
--is the size of the buffer.
DBMS_LOB.WRITE(c_lob,length(buf),1,buf);
--See what we've got - a line at a time.
print_clob;
--Add some more data to the above column and row. First commit what
--we have. Note when we commit, under 8.0, our LOB locators we
--previously held in c_lob, b_lob and c_lob2 will be lost and so must be
--reselected. **NEW 8i**: under 8.1 LOB locators may span transactions
--for read purposes, thus we no longer need to reselect c_lob2.
commit;
--We must lock the row we are going to update through DBMS_LOB.
SELECT c2 INTO c_lob FROM test_lobs WHERE c1 = 1 FOR UPDATE;
--**NEW 8i**: no longer need this select:
--select c2 into c_lob2 from test_lobs where c1 = 3;
--First append a linefeed then some data from another CLOB.
--Under 8.0 this was a two step process, first you had to get the
--the length of the LOB and secondly write the data using an offset
--of the length plus one. **NEW 8i**: with 8.1 you have a WRITEAPPEND
--function that does the two steps in a single call.
--**NEW 8i**: no longer need to get the length:
--n := DBMS_LOB.GETLENGTH(c_lob)+1;
--DBMS_LOB.WRITE(c_lob,1,n,chr(10)); -- 1 char from offset n
DBMS_LOB.WRITEAPPEND(c_lob,1,chr(10)); -- **NEW 8i**
DBMS_LOB.APPEND(c_lob,c_lob2);
dbms_output.put_line(chr(10));
print_clob;
--Compare c_lob2 with the third line of c_lob - they should be
--the same - in which case remove it. Note the TRIM function takes
--the size at which you wish the LOB to end up, NOT how much you
--want to remove.
n := DBMS_LOB.GETLENGTH(c_lob) - DBMS_LOB.GETLENGTH(c_lob2);
IF DBMS_LOB.COMPARE(c_lob,c_lob2,DBMS_LOB.GETLENGTH(c_lob2),n+1,1) = 0 THEN
DBMS_LOB.TRIM(c_lob,n-1);
END IF;
dbms_output.put_line(chr(10));
print_clob;
--Remove the data from the column completely, ie use ERASE to
--remove all bytes from offset 1. Note unlike TRIM, ERASE does not
--cause the length of the LOB to be shortened - all bytes are simply
--set to zero. Thus GETLENGTH will return 0 after TRIM'ing all bytes
--but the original length after ERASE'ing.
n := DBMS_LOB.GETLENGTH(c_lob);
DBMS_LOB.ERASE(c_lob,n,1);
--Add data from c_lob2 plus a trailing linefeed.
DBMS_LOB.COPY(c_lob,c_lob2,DBMS_LOB.GETLENGTH(c_lob2),1,1);
--**NEW 8i**: could simply use WRITEAPPEND here.
n := DBMS_LOB.GETLENGTH(c_lob2)+1;
DBMS_LOB.WRITE(c_lob,1,n,chr(10)); -- 1 char from offset n
--Now append the column with data read from one of the BFILE
--columns.
select c3 into bf from test_lobs where c1 = 3;
--First get and output the file details.
DBMS_LOB.FILEGETNAME(bf,fd,fn);
dbms_output.put_line(chr(10));
dbms_output.put_line('Appending data from file '||fn||
' in directory aliased by '||fd||':');
dbms_output.put_line(chr(10));
--Open the file to read from it - first checking that it does in
--fact still exist in the O/S and that it is not already open.
IF DBMS_LOB.FILEEXISTS(bf) = 1 and
DBMS_LOB.FILEISOPEN(bf) = 0 THEN
DBMS_LOB.FILEOPEN(bf);
END IF;
DBMS_LOB.LOADFROMFILE(c_lob,bf,DBMS_LOB.GETLENGTH(bf),n+1,1);
DBMS_LOB.FILECLOSE(bf); -- could use DBMS_LOB.FILECLOSEALL;
print_clob;
commit;
END;
/
COMMIT;
SELECT c1, c2
FROM test_lobs
/
Prompt +------------------------------------------------------------------------+
Prompt | An important thing to note when using LOB locators within DBMS_LOB |
Prompt | and PL/SQL is that a given locator always gives a read consistent |
Prompt | image from when it was selected. You will see any changes that you |
Prompt | make to the LOB using that locator and DBMS_LOB, but not those made, |
Prompt | even in the same transaction, through other LOB locators pointing to |
Prompt | the same LOB values or made via SQL directly. For example: |
Prompt +------------------------------------------------------------------------+
DECLARE
c_lob CLOB;
BEGIN
SELECT c2
INTO c_lob
FROM test_lobs
WHERE c1 = 1;
DBMS_OUTPUT.PUT_LINE('Before update length of c2 is '||
DBMS_LOB.GETLENGTH(c_lob));
UPDATE TEST_LOBS
SET c2 = 'This is a string.' where c1 = 1;
DBMS_OUTPUT.PUT_LINE('After update length of c2 is '||
DBMS_LOB.GETLENGTH(c_lob));
SELECT c2
INTO c_lob
FROM test_lobs
WHERE c1 = 1;
DBMS_OUTPUT.PUT_LINE('After reselecting locator length of c2 is '||
DBMS_LOB.GETLENGTH(c_lob));
ROLLBACK;
END;
/
COMMIT;
Prompt +--------------------------------------------------------------------------+
Prompt | NEW IN 8i |
Prompt | ========= |
Prompt | The following PL/SQL blocks demonstrate the remaining new DBMS_LOB |
Prompt | functionality introduced in version 8.1. |
Prompt | |
Prompt | Temporary LOBs |
Prompt | ============== |
Prompt | In version 8.1 it is now possible to create temporary LOBs. These are |
Prompt | LOB locators that point to LOB values held in the user's temporary |
Prompt | tablespace. Temporary LOBs are automatically initialised upon creation |
Prompt | and exist for the duration specified in the create command or until |
Prompt | explicitly freed by the user. The duration of a temporary LOB may be |
Prompt | be session or call. At the end of the given duration the temporary |
Prompt | LOB is automatically deleted. Temporary LOBs can be used in the |
Prompt | same way as normal internal LOBs through the DBMS_LOB package (note |
Prompt | there is no temporary version of a BFILE), however being only part of |
Prompt | the temporary tablespace they are not permanently stored in the database |
Prompt | and they cause no rollback or undo information to be generated. |
Prompt | Temporary LOBs may be cached though. Because versioning (ie keeping |
Prompt | copies of pages prior to updates) is not performed for temporary LOBs, |
Prompt | if a temporary LOB locator is copied and then used to update the LOB |
Prompt | value, the whole LOB value must be copied in order to maintain a read |
Prompt | consistent image via both locators. For this reason it is recommended |
Prompt | that whenever LOB locators are passed as IN OUT or OUT parameters to |
Prompt | procedures, functions or methods, NOCOPY is specified so they are |
Prompt | passed by reference. |
Prompt | |
Prompt | The following example uses a temporary LOB to reverse one of the LOB |
Prompt | values in the table and then inserts the reversed LOB as a new row. |
Prompt +--------------------------------------------------------------------------+
DECLARE
c_lob CLOB; --permanent LOB locator
t_lob CLOB; --temporary LOB locator
buf varchar2(32000); --}this example assumes the LOB is
buf2 varchar2(32000); --}less than 32K.
chunk number;
len number;
offset number;
amount number;
BEGIN
SELECT c2 INTO c_lob FROM test_lobs WHERE c1 = 1;
--Create a temporary LOB. The parameters to CREATETEMPORARY are
--locator, use caching or not and duration. Set no caching and a
--duration of call since the temporary LOB is not required outside
--of this PL/SQL block.
DBMS_LOB.CREATETEMPORARY(t_lob,FALSE,DBMS_LOB.CALL); --**NEW 8i**
--**NEW 8i**: Use GETCHUNKSIZE to get the amount of space used in a LOB
--chunk for storing the LOB value. Using this amount for reads and
--writes of the LOB will improve performance.
chunk := DBMS_LOB.GETCHUNKSIZE(c_lob);
DBMS_OUTPUT.PUT_LINE('Chunksize of column c2 is '||chunk);
DBMS_OUTPUT.PUT_LINE('Chunksize of temporary LOB is '||
DBMS_LOB.GETCHUNKSIZE(t_lob)); --for info only
len := DBMS_LOB.GETLENGTH(c_lob);
offset := 1;
buf := null;
WHILE offset < len loop
IF len - (offset-1) > chunk then
amount := chunk;
ELSE
amount := len - (offset-1);
END IF;
buf2 := null;
DBMS_LOB.READ(c_lob,amount,offset,buf2);
buf := buf||buf2;
offset := offset + amount;
END LOOP;
--Reverse the read data and write it to the temporary LOB.
buf2 := null;
FOR i IN reverse 1..len LOOP
buf2 := buf2||substr(buf,i,1);
END LOOP;
--Write the whole lot in one go. Note, if this was a large
--amount of data then ideally it should be written using the
--available chunksize of the temporary LOB.
DBMS_LOB.WRITEAPPEND(t_lob,len,buf2); --**NEW 8i**
--Now insert a new row into the table setting the CLOB column to
--the value of the temporary LOB. This can be done in one of
--two ways:
--(i) A new row can be inserted with an empty locator, the locator
-- retrieved and the LOB value copied with DBMS_LOB.COPY.
--(ii) A new row can be inserted passing the temporary LOB locator
-- as a bind variable to the insert.
--
--Using the second method:
INSERT INTO test_lobs VALUES (5,t_lob,null,null) RETURNING c2 INTO c_lob;
--Free the temporary LOB explicitly.
IF DBMS_LOB.ISTEMPORARY(t_lob) = 1 THEN
DBMS_LOB.FREETEMPORARY(t_lob);
END IF;
DBMS_OUTPUT.PUT_LINE('Length of CLOB inserted into record 5 is '||
DBMS_LOB.GETLENGTH(c_lob));
COMMIT;
END;
/
Prompt Query CLOB records...
Prompt ---------------------
SELECT c1, c2
FROM test_lobs
WHERE c1 = 5
/
Prompt +---------------------------------------------------------------------------+
Prompt | OPEN AND CLOSE OPERATIONS |
Prompt | ========================= |
Prompt | Under version 8.0 the only concept of opening and closing a LOB applies |
Prompt | to BFILEs and the opening and closing of the physical O/S files they |
Prompt | represent. **NEW 8i**: With 8.1 it is now possible to open and close |
Prompt | any type of LOB. The new calls introduced for this functionality are |
Prompt | DBMS_LOB.OPEN, DBMS_LOB.CLOSE and DBMS_LOB.ISOPEN. When the given |
Prompt | locator is a BFILE, these three routines behave as DBMS_LOB.FILEOPEN, |
Prompt | DBMS_LOB.FILECLOSE and DBMS_LOB.FILEISOPEN. When applied to internal |
Prompt | LOBs they have the effect of batching up any writes such that triggers |
Prompt | on an extensible index will not fire until the DBMS_LOB.CLOSE is called. |
Prompt | When a LOB is opened it is with a mode of either read-only or read/write. |
Prompt | Setting this mode to read-only, prevents any writes from being performed |
Prompt | on the LOB in the current transaction until the LOB is closed. Note it |
Prompt | is an error to attempt to open a BFILE for read/write. The concept of |
Prompt | openness itself applies to a LOB rather than a locator, hence a LOB may |
Prompt | only be opened once within a transaction and closed only when open. |
Prompt | Attempting to do otherwise will result in an error. |
Prompt | |
Prompt | NOTE: |
Prompt | ===== |
Prompt | The following code segment will give: |
Prompt | |
Prompt | ORA-22294: cannot update a LOB opened in read-only mode |
Prompt | Closing LOB via locator 2 |
Prompt +---------------------------------------------------------------------------+
DECLARE
c_lob1 CLOB;
c_lob2 CLOB;
BEGIN
-- Select without locking the LOB.
SELECT c2 INTO c_lob1 FROM test_lobs WHERE c1 = 2;
c_lob2 := c_lob1;
-- Open the LOB as read-only using locator 1.
DBMS_LOB.OPEN(c_lob1,DBMS_LOB.LOB_READONLY); --**NEW 8i**
--Writes are not permitted. The following gives an error:
BEGIN
DBMS_LOB.WRITEAPPEND(c_lob1,5,'Hello'); --**NEW 8i**
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
-- Commit and rollback are allowed because no transaction is started.
-- The LOB will still be open afterwards.
ROLLBACK;
-- Close - can use either locator.
IF DBMS_LOB.ISOPEN(c_lob2) = 1 THEN --**NEW 8i**
DBMS_OUTPUT.PUT_LINE('Closing LOB via locator 2');
DBMS_LOB.CLOSE(c_lob2); --**NEW 8i**
END IF;
IF DBMS_LOB.ISOPEN(c_lob1) = 1 THEN --**NEW 8i**
DBMS_OUTPUT.PUT_LINE('Closing LOB via locator 1');
DBMS_LOB.CLOSE(c_lob1); --**NEW 8i**
END IF;
-- To open for read/write the record in the database must be locked.
SELECT c2 INTO c_lob1 FROM test_lobs WHERE c1 = 2 FOR UPDATE;
DBMS_LOB.OPEN(c_lob1,DBMS_LOB.LOB_READWRITE); --**NEW 8i**
DBMS_LOB.WRITEAPPEND(c_lob1,5,'Hello'); --**NEW 8i**
DBMS_LOB.WRITEAPPEND(c_lob1,7,' there.'); --**NEW 8i**
-- The LOB MUST be closed before committing or rolling back.
DBMS_LOB.CLOSE(c_lob1); --**NEW 8i**
COMMIT;
END;
/
Prompt Query CLOB records...
Prompt ---------------------
SELECT c2
FROM test_lobs
WHERE c1 = 2
/

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.