• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3453
  • Last Modified:

Problem inserting large text-field into a table

I'm having a problem with an oracle-insert-query.

An example: A table like this:

create table tempTable(
ID number(11) primary key,
data clob);

The problem occures when I'm trying to insert text longer than 4000 characters? What should I do to avoid this problem?

The error-message I get is this one:

ORA-01704: string literal too long

I have tried using both clob and long.

Batalf
0
Batalf
Asked:
Batalf
1 Solution
 
clawedCommented:
I'm not quite clear what you are trying to do.

If you're using a PL-SQL procedure, you should be able to put in up to 32767 at a time.  If that is not enough, you can use DBMS_LOB.APPEND to chunk it in, and DBMS_LOB.SUBSTR to chunk it out.
0
 
BatalfAuthor Commented:
I'm using PHP to communicate with Oracle. In other words: I'm using Oracle as database for a website.

All I have is SQL.
0
 
belemCommented:
Hi Batalf,

using DBMS_LOB PAckage,
first you need an empty LOB
por example:

 INSERT INTO tmpTable(ID, data) VALUES(10,empty_clob());



0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
BatalfAuthor Commented:
I'm not following you.

Could you give me an example?

How could I be able to perform this query?

insert into tmpTable(ID,data)values('10','<data>');

Let's imagine that <data> is a string containing more than 4000 characters.

Batalf
0
 
JankovskyCommented:
Hi,
in SQL Oracle tries use default conxersion from Varchar2 ald limit size (just in sql, not pl/sql) of Varchar2 is 4000.
There is posibility to use stored procedure and call it.

Regards
Bob
0
 
belemCommented:
<data> is your field name.
here an example for insert from a file:

1. create table
CREATE TABLE ascii_table(lfd NUMBER, f_clob CLOB);

2. create directory for insert-files:
CONNECT SYSTEM/MANAGER
GRANT CREATE DIRECTORY TO scott;
CONNECT scott/tiger
CREATE OR REPLACE DIRECTORY nickname AS 'D:\Spool';

3.insert Procedure:
CREATE OR REPLACE PROCEDURE f2(zahl NUMBER, file2name VARCHAR2)
AS
    v_clob  CLOB;
    v_bfile BFILE;

BEGIN

    INSERT INTO ascii_table (lfd, f_clob) VALUES(zahl,empty_clob());
    COMMIT;
    v_bfile:=BFILENAME('nickname',file2name);
    select f_clob INTO v_clob FROM ascii_table WHERE  lfd=zahl FOR UPDATE;
    DBMS_LOB.FILEOPEN(v_bfile);
    DBMS_LOB.LOADFROMFILE(v_clob,v_bfile,DBMS_LOB.GETLENGTH(v_bfile));
    DBMS_LOB.FILECLOSE(v_bfile);

END;

4. execute
EXECUTE f2(51,'exampleFilename.sql');

----

Regard
belem
0
 
BatalfAuthor Commented:
I have done some more research, and it seems that this is a problem with the software I'm using. PHP Pear-DB is a library that is working as a layer between my php-code and Oracle.

The reason for using this layer is to make the job easier for me as a programmer and make it possible in an easy way to switch between different database-platforms. The problem with this library seems to be that it doens't know how to handle LOB.

I will leave this question open a little while longer and ask users from the PHP-area to participate.

Thanks for your help so far!

Batalf
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now