Search
Calendar
November 2017
S M T W T F S
« Sep    
 1234
567891011
12131415161718
19202122232425
2627282930  
Your widget title
Archives

Posts Tagged ‘Oracle’

PostHeaderIcon (long tweet) How to display / modify Oracle XDB port?

Oracle XDB port runs by default on port 8080… which is quite an issue for Java web developpers, because of the collision with default port used by servlet engines such as Tomcat and Jetty.

To display Oracle XDB port, run:

select  DBMS_XDB.GETHTTPPORT from dual;

To change it (for instance to set it on port 9090), run

exec DBMS_XDB.SETHTTPPORT(9090);

PostHeaderIcon Error grabbing Grapes — [unresolved dependency: com.oracle#ojdbc14;10.2.0.4.0: not found]

I have learnt Groovy since the beginning of the week.
I had to import some classes from Oracle JDBC driver. As a newbie, I wrote the following annotation:

@Grab(group = 'com.oracle', module = 'ojdbc14', version = '10.2.0.4.0')

I got the following error:

Error grabbing Grapes -- [unresolved dependency: com.oracle#ojdbc14;10.2.0.4.0: not found]

To sum up, adding a dependency to Oracle driver raises two issues:

  • adding any external dependency
  • adding a dependency to any JDBC driver

Indeed, Grab requires a kind of Maven repository, and you can catch the link between the @Grab annotation below and classic Maven 2 <dependency> tag. As I understood, Grab instructions are closer to Ivy than to Maven system. In my situation, having no “Grab repo” (I don’t know how to call that), I had to download the jars from a Maven repo. To fix it, two operations are needed: hint at Maven (or Ivy) repo, and allow Groovy to access the network.

Regarding the second issue, it is answered by added a @GrabConfig annotation.
Therefore, to fix the original issue:

  • replace
    @Grab(group = 'com.oracle', module = 'ojdbc14', version = '10.2.0.4.0')

    with

    @GrabConfig(systemClassLoader=true)
    @GrabResolver(name='nameOfYourMavenRepo, root='http://url-of-your-maven-repo:port')
    @Grab(group = 'com.oracle', module = 'ojdbc14', version = '10.2.0.4.0')
    
  • at first run, if needed, hint at the proxy to allow Grab to download the Jars, eg:
    groovy -Dhttp.proxyHost=myProxyHost -Dhttp.proxyPort=8080

You may notice the needed JAR will be written in the $USER_HOME/.groovy/grapes folder.

PostHeaderIcon How to export Oracle DB content to DBUnit XML flatfiles?

Case

From an Agile and TDD viewpoint, performing uni tests on DAO is a requirement. Sometimes, instead of using DBUnit datasets “out of the box”, the developper need test on actual data. In the same vein, when a bug appears on production, isolating and reproducing the issue is a smart way to investigate, and, along the way, fix it.
Therefore, how to export actual data from Oracle DB (or even MySQL, Sybase, DB2, etc.) to a DBUnit dataset as a flat XML file?

Here is a Runtime Test I wrote on this subject:

Fix

Spring

Edit the following Spring context file, setting the login, password, etc.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <!-- don't forget to write this, otherwise the application will miss the driver class name, and therfore the test will fail-->
    <bean id="driverClassForName" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean">
        <property name="targetClass" value="java.lang.Class"/>
        <property name="targetMethod" value="forName"/>
        <property name="arguments">
            <list>
                <value>oracle.jdbc.driver.OracleDriver</value>
            </list>
        </property>
    </bean>
    <bean id="connexion" class="org.springframework.beans.factory.config.MethodInvokingFactoryBean"
          depends-on="driverClassForName">
        <property name="targetClass" value="java.sql.DriverManager"/>
        <property name="targetMethod" value="getConnection"/>
        <property name="arguments">
            <list>
                <value>jdbc:oracle:thin:@host:1234:SCHEMA</value>
                <value>myLogin</value>
                <value>myPassword</value>
            </list>
        </property>
    </bean>

    <bean id="databaseConnection" class="org.dbunit.database.DatabaseConnection">
        <constructor-arg ref="connexion"/>
    </bean>
    <bean id="queryDataSet" class="org.dbunit.database.QueryDataSet">
        <constructor-arg ref="databaseConnection"/>
    </bean>
</beans>

The bean driverClassForName does not look to be used ; anyway, if Class.forName("oracle.jdbc.driver.OracleDriver") is not called, then the test will raise an exception.
To ensure driverClassForName is created before the bean connexion, I added a attribute depends-on="driverClassForName". The other beans will be created after connexion, since Spring will deduce the needed order of creation via the explicit dependency tree.

Java

public class Oracle2DBUnitExtractor extends TestCase {
    private QueryDataSet queryDataSet;

    @Before
    public void setUp() throws Exception {
        final ApplicationContext applicationContext;

        applicationContext = new ClassPathXmlApplicationContext(
                "lalou/jonathan/Oracle2DBUnitExtractor-applicationContext.xml");
        assertNotNull(applicationContext);

        queryDataSet = (QueryDataSet) applicationContext.getBean("queryDataSet");

    }

    @Test
    public void testExportTablesInFile() throws DataSetException, IOException {
    // add all the needed tables ; take care to write them in the right order, so that you don't happen to fall on dependencies issues, such as ones related to foreign keys
    
        queryDataSet.addTable("MYTABLE");
        queryDataSet.addTable("MYOTHERTABLE");
        queryDataSet.addTable("YETANOTHERTABLE");

        // Destination XML file into which data needs to be extracted
        FlatXmlDataSet.write(queryDataSet, new FileOutputStream("myProject/src/test/runtime/lalou/jonathan/output-dataset.xml"));

    }
}

PostHeaderIcon How to populate/insert/update a CLOB larger than 4000 or 32767 bytes?

A Short String

I have a table of which one field is a CLOB. Let’s say I have to insert one record with a short text. The following command is allowed:

INSERT INTO jonathan_table VALUES (1, 'hello world!');

A Bigger Text

Error ORA-01704

Now, my text is larger, let’s say 5000 characters. When I launch the same query, I get the following error:

ORA-01704: string literal too long

Indeed, Oracle/SQL*Plus have a limit on CLOB inserts: 4000 bytes.

Workaround

To pass through the limit on canonical SQL, you’ll have to use a PL/SQL procedure. The following command will be successful for any text larger than 4000 bytes, but shorter than 32767:

DECLARE
bigtext1   VARCHAR2 (32767);
BEGIN
bigtext1 := lpad('X', 32000, 'X')
INSERT INTO jonathan_table VALUES (1, bigtext1);
END;

An Even Bigger Text

Errors ORA-06550 and PLS-00103

You guess it: beyond this limit of 32 KB, an error occurs. So the following script:

DECLARE
   bigtext1   VARCHAR2 (42000);
BEGIN
   bigtext1 := lpad('X', 42000, 'X')
   INSERT INTO jonathan_table
        VALUES (1, bigtext1);
END;

raises such an error:

Error at line 1
ORA-06550: line 5, column 4:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

   . ( * % & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset
The symbol ";" was substituted for "INSERT" to continue.

Fix this issue

I searched a lot to find an easy fix to go beyond the limit of 32KB. My point was that with Java for instance there is no limit of 32KB. In the same way, with TOAD I was able to update the record with many mega bytes of text, via the clipboard. After further search, I learnt that the 32KB barrier was a SQL*Plus limitation on actual strings, but the patterns insert into ... select ... from were not affected.
Here is the idea:

  • create a temporary table
  • split the text into blocks shorter than 32KB
  • insert the blocks into the temporary table
  • perform a first insert with a null CLOB
  • update the record using a select on the temporary table (yet you can insert the actual value since previous step)

Here is an example:

DROP TABLE tt_jonathan_table;
CREATE GLOBAL TEMPORARY TABLE tt_jonathan_table
(
    ID NUMBER(10),
    pdlsuffix CLOB
) ON COMMIT PRESERVE ROWS;

TRUNCATE TABLE tt_jonathan_table;

DECLARE
   bigtext1   VARCHAR2 (32767);
   bigtext2   VARCHAR2 (32767);
BEGIN
   bigtext1 := lpad('X', 32000, 'X')
   bigtext2 := lpad('Y', 32000, 'Y')
    INSERT INTO tt_jonathan_table
        VALUES (1, bigtext1);

   INSERT INTO tt_jonathan_table
        VALUES (2, bigtext2);

   INSERT INTO jonathan_table
               (id, myClobField)
        VALUES (jonathan_seq.NEXTVAL, NULL);

   UPDATE jonathan_table
      SET myClobField = (SELECT CONCAT (rls1.myClobField, rls2.myClobField)
                         FROM tt_jonathan_table rls1, tt_jonathan_table rls2
                        WHERE rls1.ID = 1 AND rls2.ID = 2)
    WHERE myClobField is null;
END;
/

TRUNCATE TABLE tt_jonathan_table;

PostHeaderIcon How to Read a BLOB for a Human Being?

Case

I have had to access a BLOB and read its content. By principle, I dislike using binary objects, which do not suit easy tracing and auditing. Anyway, in my case, floats are stored in a BLOB, and I need read them in order to validate my current development.

You have many ways to read the content of the BLOB. I used two: SQL and Java

SQL

Start your TOAD for Oracle ; you can launch queries similar to this:

SELECT UTL_RAW.cast_to_binary_float
             (DBMS_LOB.submyrecord (myrecord.myrecordess,
                                    4,
                                    1 + (myrecordessnameid * 4)
                                   )
             ) AS myrecordessvalue
  FROM mytable myrecord
 WHERE myrecordessid = 123456; 

You can also run a stored procedure, similar to this:

DECLARE
   blobAsVariable  BLOB;
   my_vr           RAW (4);
   blobValue       FLOAT;
   bytelen         NUMBER  := 4;
   v_index         NUMBER  := 5;
   jonathan        RAW (4);
   loopLength      INT;
BEGIN
   SELECT myField
     INTO blobAsVariable
     FROM myTable
    WHERE tableid = (5646546846);

   DBMS_LOB.READ (blobAsVariable, bytelen, 1, jonathan);
   loopLength := UTL_RAW.cast_to_binary_integer (jonathan);

   FOR rec IN 1 .. loopLength
   LOOP
      DBMS_LOB.READ (blobAsVariable, bytelen, v_index, my_vr);
      blobValue := UTL_RAW.cast_to_binary_float (my_vr);
      v_index := v_index + 4;
      DBMS_OUTPUT.put_line (TO_CHAR (blobValue));
   END LOOP;
END;

Java

I am still not sure to be DBA expert. Indeed I am convinced I am more fluent in Java than in PL/SQL 😉

Create a Spring configuration file, let’s say BlobRuntimeTest-applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <!-- $Id: BlobRuntimeTest-applicationContext.xml  $ -->
    <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@myDBserver:1234:MY_SCHEMA"/>
        <property name="username" value="jonathan"/>
        <property name="password" value="lalou"/>
        <property name="initialSize" value="2"/>
        <property name="minIdle" value="2"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

Now create a runtime test:

/**
 * User: Jonathan Lalou
 * Date: Aug 7, 2011
 * Time: 5:22:33 PM
 * $Id: BlobRuntimeTest.java $
 */
public class BlobRuntimeTest extends TestCase {
    private static final Logger LOGGER = Logger.getLogger(BlobRuntimeTest.class);

    private static final String TABLE = "jonathanTable";
    private static final String PK_FIELD = "jonathanTablePK";
    private static final String BLOB_FIELD = "myBlobField";
    private static final int[] PK_VALUES = {123, 456, 789};

    private ApplicationContext applicationContext;
    private JdbcTemplate jdbcTemplate;

    @Before
    public void setUp() throws Exception {
        applicationContext = new ClassPathXmlApplicationContext(
                "lalou/jonathan/the/cownboy/BlobRuntimeTest-applicationContext.xml");
        assertNotNull(applicationContext);
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        assertNotNull(jdbcTemplate);
    }

    @After
    public void tearDown() throws Exception {
    }

    @Test
    public void testGetArray() throws Exception {
        for (int pk_value : PK_VALUES) {
            final Blob blob;
            final byte[] bytes;
            final float[] floats;

            blob = (Blob) jdbcTemplate.queryForObject("select " + BLOB_FIELD + " from " + TABLE + " where " + PK_FIELD + " = " + pk_value, Blob.class);
            assertNotNull(blob);
            bytes = blob.getBytes(1, (int) blob.length());
            // process your blob: unzip, read, concat, add, etc..
            // floats = ....

            LOGGER.info("Blob size: "  + floats.length);
            LOGGER.info(ToStringBuilder.reflectionToString(floats));
        }
    }
}