TASK: We have Oracle 10g database with NLS_CHARACTERSET: WE8ISO8859P1, NLS_NCHAR_CHARACTERSET: UTF8. Some table columns need to have Unicode encoding to enable russian, chinese and other languages in client interface thru the php web pages. To enable this functionality without database conversion to UTF8 or UTF16, it would be reasonable to implement just a NVARCAR2, NCLOB columns where it i necessary.
PROBLEM according to "Underground php manual"
(page 127) "Oracle's NCHAR, NVARCHAR2, and NCLOB types are not supported in the OCI8 extension" . And there is also no RAW variable support.
SOLUTION: to implement this functionality I was using Oracle feature that character data converted to Binary data in Oracle RAW type field is not a true Binary data but rather Hexadecimal presentation of binary data. In that way RAW type value can be added to VARCHAR2 type field.
If you need data column length less or equal than 4000 bytes then use NVARCHAR2. If you need more then use NCLOB. If you want to return column in single SQL Select statement for example in sys_refcursor, then column limit is about 500 bytes. This is because table and SQL statement have column limit to 4000 characters and chinese symbols can use 4 bytes plus hexadecimal presentation in raw will use 2 bytes - totally 4000/(4x2)=500. (see code example below PROCEDURE GET_TEMPLATE - out_cursor)
1. to pass data from Oracle PL/SQL to-> php I will - convert NVARCHAR2 data to RAW (Hexadecimal equivalent), and in php this value covert from binary to UTF8. The funny part is that in sys_refcursor - if we pass binary data in SELECT statement then PHP automatically convert it to UTF8 value.The same thing is with BLOB value - php automatically converts it to text without extra convertion.To pass NCLOB - convert it to BLOB and in PHP it will convert to UTF8 text without HEX2STR function.
2. Data from PHP to-> to Oracle - in php will convert UTF8 value to binary, assign it to Oracle procedure VARCHAR2 input parameter, in Oracle convert this parameter like binary to NVARCHAR2.
For NCLOB - pass to Oracle like BLOB and in Oracle convert BLOB to NCLOB.
In Oracle we have table EMAIL_TEMPLATE
CREATE TABLE EMAIL_TEMPLATE
(
TEMPLATE_ID INTEGER NOT NULL,
TITLE NVARCHAR2(200) NOT NULL,
USER_ID INTEGER NOT NULL,
TEMPLATE NCLOB,
COMMON INTEGER,
CREATION_DATE DATE DEFAULT sysdate,
UPDATED_DATE DATE,
CONSTRAINT EMAIL_TEMPLATE_PK PRIMARY KEY (TEMPLATE_ID)
);
Code example:
1. Oracle to-> PHP
in Oracle
PROCEDURE GET_TEMPLATE (
in_template_id INTEGER,
out_cursor OUT sys_refcursor,
out_template OUT NOCOPY BLOB,
out_rc OUT NUMBER,
out_rc_txt OUT VARCHAR2
)
IS
cGet_Clob NCLOB;
len PLS_INTEGER;
n PLS_INTEGER := 1;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
warning INTEGER;
lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
eMyError EXCEPTION;
BEGIN
out_rc := 0;
out_rc_txt := 'OK';
OPEN out_cursor FOR
SELECT TEMPLATE_ID, UTL_RAW.cast_to_raw (title) title, USER_ID, COMMON, CREATION_DATE,
UPDATED_DATE
FROM EMAIL_TEMPLATE
WHERE template_id = in_template_id;
DBMS_LOB.createtemporary (out_template, TRUE);
BEGIN
SELECT TEMPLATE
INTO cGet_Clob
FROM EMAIL_TEMPLATE
WHERE template_id = in_template_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
out_rc_txt := 'NOT found EMAIL_TEMPLATE where template_id=' || TO_CHAR (in_TEMPLATE_ID);
RAISE eMyError;
END;
IF cGet_Clob IS NULL OR NVL (DBMS_LOB.getlength (cGet_Clob), 0) = 0
THEN
out_rc_txt := 'Template is empty for TEMPLATE_ID=' || TO_CHAR (in_TEMPLATE_ID);
ELSE
DBMS_LOB.convertToBlob (dest_lob => out_template,
src_clob => cGet_Clob,
amount => DBMS_LOB.LOBMAXSIZE,
dest_offset => dest_offset,
src_offset => src_offset,
blob_csid => DBMS_LOB.DEFAULT_CSID,
lang_context => lang_context,
warning => warning
);
END IF;
EXCEPTION
WHEN eMyError
THEN
out_rc := -1;
WHEN OTHERS
THEN
out_rc := -1;
out_rc_txt := SQLERRM || CHR (10) || DBMS_UTILITY.format_error_backtrace;
END GET_TEMPLATE;
in php: just get the row from cursor or explicit conversion from bin to utf use:iconv( 'utf-16', 'utf-8', $ename ). In this example not necessary because PHP automagically converts Hex to UTF8. Just put in Header:
working example:
$s = oci_parse($c, "begin EMAIL_TEMPLATE_PKG.GET_TEMPLATE(107, :cursor, :template, :rc, :rc_txt); end;");
$cursor = oci_new_cursor($c);
$clob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ":cursor", $cursor, -1, OCI_B_CURSOR);
oci_bind_by_name($s, ':template', $clob, -1, OCI_B_BLOB);
oci_bind_by_name($s, ":rc", $result['rc'], 65000);
oci_bind_by_name($s, ":rc_txt", $result['rc_txt'], 65000);
$rs = oci_execute($s, OCI_DEFAULT);
oci_close($c);
$data = $clob->read($clob->size());
2. PHP to-> Oracle
in php
$myv = 'DATA for NCLOB';
$title = 'TEST data for NVARCHAR2';
$s = oci_parse($c, "begin EMAIL_TEMPLATE_PKG.ADD_TEMPLATE('$title', '151', :clob_TEMPLATE, '0', :id, :rc, :rc_txt); end;");
$lob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ':clob_TEMPLATE', $lob, -1, OCI_B_BLOB);
oci_bind_by_name($s, ":id", $result['id'], 65000);
oci_bind_by_name($s, ":rc", $result['rc'], 65000);
oci_bind_by_name($s, ":rc_txt", $result['rc_txt'], 65000);
$lob->writeTemporary($myv, OCI_TEMP_BLOB);
$rs = oci_execute($s, OCI_DEFAULT); // use OCI_DEFAULT so $lob->save() works
oci_close($c);
in Oracle
PROCEDURE ADD_TEMPLATE (
in_TITLE VARCHAR2,
in_USER_ID INTEGER,
in_TEMPLATE BLOB,
in_COMMON INTEGER,
out_TEMPLATE_ID OUT INTEGER,
out_rc OUT NUMBER,
out_rc_txt OUT VARCHAR2
)
IS
vTITLE NVARCHAR2 (400) := UTL_RAW.cast_to_nvarchar2 (in_TITLE);
n PLS_INTEGER := 1;
cLocator NCLOB;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
warning INTEGER;
lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
BEGIN
out_rc := 0;
out_rc_txt := 'OK';
INSERT INTO EMAIL_TEMPLATE
(TEMPLATE_ID, TITLE, USER_ID, TEMPLATE, COMMON
)
VALUES (SQN_EMAIL_TEMPLATE.NEXTVAL, vTITLE, in_USER_ID, EMPTY_CLOB (), in_COMMON
)
RETURNING TEMPLATE_ID, TEMPLATE
INTO out_TEMPLATE_ID, cLocator;
IF in_TEMPLATE IS NULL OR NVL (DBMS_LOB.getlength (in_TEMPLATE), 0) = 0
THEN
out_rc_txt := 'Input template BLOB is empty for TEMPLATE_ID=' || TO_CHAR (out_TEMPLATE_ID);
ELSE
DBMS_LOB.convertToClob (dest_lob => cLocator,
src_blob => in_TEMPLATE,
amount => DBMS_LOB.LOBMAXSIZE,
dest_offset => dest_offset,
src_offset => src_offset,
blob_csid => DBMS_LOB.DEFAULT_CSID,
lang_context => lang_context,
warning => warning
);
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
out_rc := -1;
out_rc_txt := SQLERRM || CHR (10) || DBMS_UTILITY.format_error_backtrace;
ROLLBACK;
END ADD_TEMPLATE;
Good comment from forum about AL16UTF16 charset:
This solution works if the database NCHAR character set is UTF8. Basically it casts the data type to RAW/BLOB without touching the data.
To make it also work for database with AL16UTF16 NCHAR character set, you will need to convert the data between NCHAR character set and UTF-8 which is the encoding of the data in PHP side:
[From Oracle to PHP]
- for NVARCHAR2 columns:
select utl_raw.cast_to_raw(convert(nvarchar2_column,'AL32UTF8'))
from table_name ...
- for NCLOB columns
dbms_lob.converttoblob(...,
blob_csid=>nls_charset_id('AL32UTF8'),
...);
[From PHP to Oracle]
- for NVARCHAR2 columns:
v_nchar_cs varchar2(30) := 'AMERICAN_AMERICA.'||nls_charset_name(nls_charset_id('NCHAR_CS'));
v_php_cs varchar2(30) := 'AMERICAN_AMERICA.AL32UTF8';
insert into table_name values
(...,
utl_raw.cast_to_nvarchar2(
utl_raw.convert(raw_containing_utf8_data,
v_nchar_cs,
v_php_cs)),
...);
- for NCLOB columns
dbms_lob.convertoclob(...,
blob_csid=>nls_charset_id('AL32UTF8'),
...);
Reference/Links:
where I get ideas for this solution:http://forums.oracle.com/forums/thread.jspa?messageID=3877710
Database Globalization Support Guide - Supporting Multilingual Databases with Unicode
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#CNCPT012
Nav komentāru:
Ierakstīt komentāru