MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘clob’ Category

Oracle and Java Tutorial

without comments

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading and writing large text files to an Oracle database. The example provides:

  • A FileIO.jar library that lets you enter Oracle connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
  • A ojdbc6.jar file, which is Oracle’s library for JDBC communication with the Oracle Databases.

The steps to compiling and testing this code are qualified below:

  1. Download and install the Java Software Development Kit (JSDK) for Java 6.
  2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
  3. Download and position the ojdbc6.jar and FileIO.jar files in the JavaTest directory.
  4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\ojdbc6.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

  1. Copy the WriteReadCLOB.java code from the bottom of this posting and also put it into the JavaTest directory.
  2. Compile the WriteReadCLOB.java source code with the javac utility, as shown below:
javac WriteReadCLOB.java

After you compile it, you should run it as follows:

java WriteReadCLOB
  1. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
  2. When it runs, you’ll see the following tabbed JOptionPane.

You need to enter the following values before clicking the OK button:

  • Host: The hostname of your machine.
  • Port: The port that the Oracle Listener is running on (the default value is 1521).
  • Database: The Oracle TNS Alias, which is orcl for the full database sample database or xe for the Oracle Database 10g Express Edition.
  • UserID: The user (schema) name where you’ve created an ITEM table.
  • Password: The password for the user’s account.

In the JFileChooser, select a file to upload to the database.

You should see what you uploaded displayed in a JFrame.

Written by maclochlainn

August 1st, 2012 at 12:25 pm

A couple DBMS_SQL limits

without comments

While developing a dynamic SQL example in Oracle 11g that builds a query based on available display columns, I found two interesting error messages. Now instead of noting it for the umpteenth time, I’m documenting it for everybody. The error messages are generated when this DBMS_SQL package’s statement is a SELECT statement, and is executed with either a BLOB, BFILE or CFILE column in the list of returned columns.

26
STATUS := dbms_sql.execute(stmt);

BLOB data type

You get the following error when a column in the query has a BLOB data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got BLOB
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

BFILE or CFILE data type

You get the following error when a column in the query has a BFILE or CFILE data type. If you alter the query to exclude the column, no error occurs.

BEGIN test('DEMO'); END;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got FILE
ORA-06512: at "SYS.DBMS_SQL", line 1575
ORA-06512: at "STUDENT.TEST", line 26
ORA-06512: at line 1

It’s never a joy to debug the DBMS_SQL package, at least it’s never a joy for me. I hope this helps somebody sort out an issue more quickly.

Written by maclochlainn

June 12th, 2010 at 11:43 pm

Oracle LOB Storage Syntax

with 4 comments

Somebody asked me to post some examples of Large Object syntax. I checked Chapter 9 of the Oracle Database SecureFiles and Large Object Developer’s Guide, and I agree an example or two would be helpful. I’ve provided CREATE and ALTER statements.

Here’s a sample CREATE TABLE statement for a CLOB, BLOB, and BFILE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE TABLE item
( item_id             NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_barcode        VARCHAR2(20)  CONSTRAINT nn_item_1  NOT NULL
, item_type           NUMBER        CONSTRAINT nn_item_2  NOT NULL
, item_title          VARCHAR2(60)  CONSTRAINT nn_item_3  NOT NULL
, item_subtitle       VARCHAR2(60) 
, item_desc           CLOB          CONSTRAINT nn_item_4  NOT NULL
, item_blob           BLOB
, item_photo          BFILE
, item_rating         VARCHAR2(8)   CONSTRAINT nn_item_5  NOT NULL
, item_rating_agency  VARCHAR2(4)   CONSTRAINT nn_item_6  NOT NULL
, item_release_date   DATE          CONSTRAINT nn_item_7  NOT NULL
, created_by          NUMBER        CONSTRAINT nn_item_8  NOT NULL
, creation_date       DATE          CONSTRAINT nn_item_9  NOT NULL
, last_updated_by     NUMBER        CONSTRAINT nn_item_10 NOT NULL
, last_update_date    DATE          CONSTRAINT nn_item_11 NOT NULL
, CONSTRAINT fk_item_1                      FOREIGN KEY(item_type)
  REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_item_2                      FOREIGN KEY(created_by)
  REFERENCES system_user(system_user_id)
, CONSTRAINT fk_item_3                      FOREIGN KEY(last_updated_by)
  REFERENCES system_user(system_user_id))
  LOB (item_desc) STORE AS BASICFILE item_desc
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645))  
, LOB (item_blob) STORE AS item_blob
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645));

If you attempt to make the CHUNK size greater than 32,768, it will raise an ORA-22851: invalid CHUNK LOB storage option value error.

Here’s a sample ALTER TABLE statement that adds a new large object column to a table. You can’t modify an existing large object column. You raise an ORA-22859: invalid modification of columns error when you try it. If you need to make modifications, check this post form last year on re-organizing LOB indexes.

1
2
3
4
5
6
7
8
ALTER TABLE item ADD (another CLOB)
LOB (another) STORE AS BASICFILE item_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT 1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));

This all makes sense, but hold on there’s a potential problem when you add a large object column to a table. I’m not sure it’s a bug but I’ll probably open a TAR on it later in the week (arghhhh! I once worked in Oracle Support. :-)). Anyway, here’s what I found:

If you drop and recreate the table, you can encounter an ORA-01430 error. It appears that the large object is really hanging out in the catalog. As soon as you try to re-add it, you get the error.

If you describe the table after recreating it, you’ll see the following:

SQL> DESCRIBE item
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ITEM_ID                                   NOT NULL NUMBER
 ITEM_BARCODE                              NOT NULL VARCHAR2(20)
 ITEM_TYPE                                 NOT NULL NUMBER
 ITEM_TITLE                                NOT NULL VARCHAR2(60)
 ITEM_SUBTITLE                                      VARCHAR2(60)
 ITEM_DESC                                 NOT NULL CLOB
 ITEM_BLOB                                          BLOB
 ITEM_PHOTO                                         BINARY FILE LOB
 ITEM_RATING                               NOT NULL VARCHAR2(8)
 ITEM_RATING_AGENCY                        NOT NULL VARCHAR2(4)
 ITEM_RELEASE_DATE                         NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER
 LAST_UPDATE_DATE                          NOT NULL DATE

Also, if you run the following query with the DBMS_METADATA package, the column doesn’t exist in the table defintion:

SELECT dbms_metadata.get_ddl('TABLE','ITEM') FROM dual;

If try to run the ALTER statement to add the column that doesn’t appear to exist, you’ll get the following message in Oracle 11.1.0.7.0:

ALTER TABLE item ADD (another CLOB)
                      *
ERROR at line 1:
ORA-01430: COLUMN being added already EXISTS IN TABLE

If you rerun the DBMS_METADATA.GET_DDL query, it’ll be there in the table definition. Also, in its non-existence but existent state (ghost-state), there’s nothing in the DBA_LOB, or DBA_SEGMENT views. You can find a query to check large object segments and indexes here. My questions is where has it gone, why is it there, and did I do something wrong because this looks like basic functionality.

If you know the mystery or the existence of a bug, please post a comment.

Written by maclochlainn

July 17th, 2009 at 12:40 am

Posted in clob,Oracle,sql

Matching LOB Indexes and Segments

with 7 comments

I enjoyed Tom Kyte’s example of how you find matching a LOB Index to Segment (Expert Oracle Database Architecture, pp. 542). I’ve noticed variations of it posted in various locations. While it works well for sample scheme that have only one LOB, the following works for any number of LOBs in any schema. This simplifies working with system- and user-defined segment names. The first CASE statement ensures that joins between user-named segment names are possible. The second CASE statement ensures two things: (a) Joins between system generated segment names don’t throw an error when matching unrelated system generated return values found in the DBA_SEGEMENTS view; and (b) Joins between named segments are possible and don’t throw an error.

COL owner         FORMAT A5  HEADING "Owner"
COL TABLE_NAME    FORMAT A5  HEADING "Table|Name"
COL column_name   FORMAT A10 HEADING "Column|Name"
COL segment_name  FORMAT A26 HEADING "Segment Name"
COL segment_type  FORMAT A10 HEADING "Segment|Type"
COL bytes                    HEADING "Segment|Bytes"
 
SELECT   l.owner
,        l.table_name
,        l.column_name
,        s.segment_name
,        s.segment_type
,        s.bytes
FROM     dba_lobs l
,        dba_segments s
WHERE    REGEXP_SUBSTR(l.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
  END) =
REGEXP_SUBSTR(s.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
  END)
AND      l.table_name = UPPER('&table_name')
AND      l.owner = UPPER('&owner')
ORDER BY l.column_name, s.segment_name;

It produces the following type of output:

Table  Column                               Segment      Segment
Name   Name       Segment Name              Type           Bytes
------ ---------- ------------------------- ---------- ---------
PLSQL  ITEM_BLOB  SYS_IL0000074435C00007$$  LOBINDEX      65,536
PLSQL  ITEM_BLOB  SYS_LOB0000074435C00007$$ LOBSEGMENT 2,097,152
PLSQL  ITEM_DESC  SYS_IL0000074435C00006$$  LOBINDEX      65,536
PLSQL  ITEM_DESC  SYS_LOB0000074435C00006$$ LOBSEGMENT   720,896

This should help you monitor growth of LOBs in your database. The maximum CHUNK size is 32.768. If you elect a value greater than that you raise an ORA-22851 error.

Written by maclochlainn

April 17th, 2008 at 6:16 pm