Posts Tagged ‘PL/SQL’
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;
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)); } } }