Search
Calendar
December 2017
S M T W T F S
« Sep    
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Your widget title
Archives

Posts Tagged ‘sqlplus’

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 SQL*Plus Does Not Update a Stored Procedure

Case

You have a stored procedure that you would like to update. The script is OK with TOAD, but raises an error when launched via SQL*Plus.

Fix

Add a slash (‘/’) or a dot (‘.’) at the end of file.

PostHeaderIcon Copy all tables from a schema to another one

Case:

I need copy the content of all tables from one Oracle schema to another one, specifically: I must copy raw data from a production DB to a developper one.

Context:

  • The DBA do not allow DBLinks
  • Oracle source and destination schemas share the same structure (name of the tables, names and types of columns, etc.

Fix:

Here is a small script I wrote, of course it can be improved:

#!/usr/bin/bash
SRC_LOGIN=XYZ
SRC_PASSWORD=XYZ
SRC_DB=production
DBDEST_LOGIN=XYZ
DEST_PASSWORD=XYZ
DEST_DB=developmentDB

echo &quot;select object_name from user_objects where object_type = 'TABLE' order by object_name;&quot; | \
	sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB | \
	grep -v OBJECT_NAME | grep -v &quot;-&quot; | grep -v &quot;^$&quot; | \
	sed &quot;1,9d&quot; | tac | sed &quot;1,3d&quot; | tac | \
	sort | uniq &amp;gt; allTables.txt

for table in `more allTables.txt`
do
rm -rf $table.sql
echo &quot;exporting $table&quot;
exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables=&quot;$table&quot; direct=y
echo &quot;importing $table&quot;
echo &quot;truncate table $table;&quot; | \
    sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB
imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql \
    buffer=10485867 tables=&quot;$table&quot; \
    fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y
rm -rf $table.sql
done

rm -rf allTables.txt

A little more explanations:

  • echo "select object_name from user_objects where object_type = 'TABLE';" | sqlplus $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB: this allow to retrieve the names of all the tables from the DB
  • grep -v OBJECT_NAME | grep -v "-" | grep -v "^$" : this removes empty lines and useless trace from Oracle SQLPlus
  • sed "1,9d" | tac | sed "1,3d" | tac: removes the 9 first lines and the 3 last ones. tac in Unix allow to reverse the content of a file
  • sort | uniq : sort lines and remove duplicates
  • exp $SRC_LOGIN/$SRC_PASSWORD@$SRC_DB file=$table.sql buffer=10485867 tables="$table" direct=y: exports the content of the table $table
  • echo "truncate table $table;" | sqlplus $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB: truncates destination table. This is useful in case the script must be played more than once, for instance.
  • imp $DEST_LOGIN/$DEST_PASSWORD@$DEST_DB file=$table.sql buffer=10485867 tables="$table" fromuser=$SRC_LOGIN touser=$DEST_LOGIN ignore=y: import the content of the table

Some improvement ideas:

  • handle sequences, triggers, stored procedures, etc.
  • create the complete destination DB owing to source DB, ie do not assume anymore that the DBs share the same structure