| problem insert clob with more than 4000 bytes [message #127052] |
Fri, 08 July 2005 04:43  |
sythomas
Messages: 1
Registered: July 2005
|
Junior Member |
|
|
Hi,
I am developing a Visual FoxPro application. I want to insert a string more than 4000 bytes in a CLOB field.
When I tried to insert directly i have an oracle error: ora:01704.
How can I do to insert a clob?
I will really appreciate if someone can help me out.
Regards
Sylvie
|
|
|
|
| Re: problem insert clob with more than 4000 bytes [message #127075 is a reply to message #127052] |
Fri, 08 July 2005 08:12  |
Frank Naude
Messages: 4579
Registered: April 1998
|
Senior Member |
|
|
From the error description it sounds like you will need to use a bind variable:
$ oerr ora 01704
01704, 00000, "string literal too long"
// *Cause: The string literal is longer than 4000 characters.
// *Action: Use a string literal of at most 4000 characters.
// Longer values may only be entered using bind variables.
Best regards.
Frank
|
|
|
|
| Re: problem insert clob with more than 4000 bytes [message #129824 is a reply to message #127052] |
Tue, 26 July 2005 18:23 |
raafat
Messages: 1
Registered: July 2005
Location: Saudi Arabia
|
Junior Member |
|
|
You cannot insert more than 4000 chars directly into a CLOB field. You will need to create a procedure to do the insertion and call it. Plus you cannot insert more than 32000 chars at one time... this is a PL/SQL limitation.
To work around that limitation and if your text is greater than 32000 characters, you will need to send the text in chunks of data with each chunk should not exceed the 32k limit (a loop thing) and store each chunk in the same CLOB field.
It means we need to call the stored procedure many times until the whole string is inserted (loop).
The number of time the stored procedure is called can be calculated as (I don't know about FoxPro so it is in JavaScript): Math.ceil(string length/32000). Example: if we have a string of length 128200 chars then we need to call the stored procedure 5 times.
The first trip to the stored procedure, we insert the new record and initializes the CLOB field and inserts the first 32k chars in it. We can call the first process as "Writing" and we can call the rest of the remaining trips as "Appending" and this means, we add the rest of the chunks to the data previously stored in it.
I added a parameter in the procedure "P_FLAG" to tell it whether we are inserting a new record or updating on an existing record.
Here is an example:
1- Create a table and name it "TEST":
create table TEST(IDX int, TXT CLOB);
2- Create a stored procedure and name it "P_TEST". Notice that the procedure takes 3 parameters. The FLAG parameter is used to tell the procedure whether we are inserting a new record or appending to an existing record. 0 means a new record and any number means we need to append to an existing record:
create or replace procedure P_TEST(P_IDX in number,P_TXT in varchar2,P_FLAG in number) as
g_clob clob;
begin
if P_FLAG=0 then
insert into test values (P_IDX, empty_clob() ) returning TXT into g_clob;
dbms_lob.write( g_clob,length(P_TXT), 1,P_TXT);
else
select TXT into g_clob from test where IDX=P_IDX for update;
dbms_lob.writeappend( g_clob, length(P_TXT), P_TXT);
end if;
end;
3- It is a good idea to test the procedure by inserting something and specially by inserting more than 32k characters in the CLOB field. Use your SQL*Plus and do this test:
Let us insert a new record with IDX=1 and the TXT contains 32000 stars:
(notice the zero at the end. It means we are inserting a new record)
SQL> exec p_test(1, rpad('*',32000,'*'),0);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Le us add more stars to our first record:
(notice the 1 at the end. It means we are appending to an exesting record)
SQL> exec p_test(1, rpad('*',32000,'*'),1);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Let us add a new record with IDX=2 and TXT contains 19500 stars:
SQL> exec p_test(2, rpad('*',19500,'*'),0);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Now let us see the length of each data in our CLOB field:
SQL> select IDX, dbms_lob.getlength(TXT) from TEST order by IDX;
IDX DBMS_LOB.GETLENGTH(TXT)
---------- -----------------------
1 64000
2 19500
Test conclusion:
As you can see that the character length of the record with IDX=1 contains 64000 characters. This means the test has "PASSED".
I hope that was useful.
Regards,
Raafat
|
|
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
