I have to store string contains html data into oracle blob field using php.So I am using
utl_raw.cast_to_raw('myString')
But I cant store when myString size exceeds 4000 Bytes. How to solve this?
-
Can you show us some more of your code?pablomatico– pablomatico2015-11-13 06:36:56 +00:00Commented Nov 13, 2015 at 6:36
-
$myString='<table ><tbody><tr><td>Some text</td></tr></tbody></table>'; query="update table_file set result=utl_raw.cast_to_raw('$myString') where slno=1";shahanas sulthan– shahanas sulthan2015-11-13 06:42:42 +00:00Commented Nov 13, 2015 at 6:42
-
And the result field of table_file is a BLOB or a VARCHAR2 field?pablomatico– pablomatico2015-11-13 06:44:46 +00:00Commented Nov 13, 2015 at 6:44
Add a comment
|
2 Answers
I have to store string contains html data into oracle blob field using php
Consider using CLOB rather than BLOB. The HTML can be considered a string literal. A string literal is inherently a VARCHAR2. So you cannot have a string literal longer than 4000 characters. You need to append them each up to 4000 bytes in chunks into a single CLOB to store beyond 4000 bytes.
From documentation,
A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
For example,
SQL> CREATE TABLE t_clob
2 (col CLOB
3 );
Table created.
SQL> INSERT
2 INTO t_clob VALUES
3 (
4 TO_CLOB
5 (RPAD('<table ><tbody><tr><td>Some text</td></tr></tbody></table>', 4000, '*'))
6 ||RPAD('<table ><tbody><tr><td>Some text</td></tr></tbody></table>', 4000, '*')
7 ||RPAD('<table ><tbody><tr><td>Some text</td></tr></tbody></table>', 4000, '*')
8 );
1 row created.
SQL> SELECT LENGTH(col) FROM t_clob;
LENGTH(COL)
-----------
12000
Starting with Oracle 12c, the maximum size of VARCHAR2 is now extended to 32767 bytes. By default the parameter MAX_STRING_SIZE is STANDARD which can hold up to 4000 bytes.
SQL> show parameter MAX_STRING_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
You could alter the parameter value to EXTENDED and increase the maximum value of VARCHAR2 to 32767 bytes.
There are mainly two important steps:
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k
Comments
Blob are used useally for binary data ( image, documents ) if you are using TEXTS use instead CLOB.
BLOBs are binary LOBs. CLOBs are character LOBs.
With CLOB you can store more then 4000 bytes
1 Comment
Lalit Kumar B
"With CLOB you can store more then 4000 bytes" Yes, but you cannot form a string more than 4000 bytes in SQL. You need to do it in chunks.