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
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
ASKER
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.
All I have is SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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('1
Let's imagine that <data> is a string containing more than 4000 characters.
Batalf
Could you give me an example?
How could I be able to perform this query?
insert into tmpTable(ID,data)values('1
Let's imagine that <data> is a string containing more than 4000 characters.
Batalf
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
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
<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('nickna
select f_clob INTO v_clob FROM ascii_table WHERE lfd=zahl FOR UPDATE;
DBMS_LOB.FILEOPEN(v_bfile)
DBMS_LOB.LOADFROMFILE(v_cl
DBMS_LOB.FILECLOSE(v_bfile
END;
4. execute
EXECUTE f2(51,'exampleFilename.sql
----
Regard
belem
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('nickna
select f_clob INTO v_clob FROM ascii_table WHERE lfd=zahl FOR UPDATE;
DBMS_LOB.FILEOPEN(v_bfile)
DBMS_LOB.LOADFROMFILE(v_cl
DBMS_LOB.FILECLOSE(v_bfile
END;
4. execute
EXECUTE f2(51,'exampleFilename.sql
----
Regard
belem
ASKER
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
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
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.