?
Solved

Problem inserting large text-field into a table

Posted on 2003-02-20
7
Medium Priority
?
3,445 Views
Last Modified: 2010-05-18
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
Comment
Question by:Batalf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Expert Comment

by:clawed
ID: 7986732
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
 
LVL 32

Author Comment

by:Batalf
ID: 7986762
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
 
LVL 1

Accepted Solution

by:
belem earned 400 total points
ID: 7986874
Hi Batalf,

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

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



0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 32

Author Comment

by:Batalf
ID: 7987002
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
 
LVL 6

Expert Comment

by:Jankovsky
ID: 7987881
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
 
LVL 1

Expert Comment

by:belem
ID: 7988659
<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
 
LVL 32

Author Comment

by:Batalf
ID: 7989019
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question