Pass Unicode To an Oracle Stored Procedure (Article)

Category:
ASP, HTML and XML
Type:
Snippets
Difficulty:
Intermediate

Author: David Nishimoto

Version Compatibility: Visual Basic 6, ASP

More information:
This article explains how to pass unicode code points to an oracle stored procedure. The stored procedure uses the REF CURSOR type to return a resultset back to an ADO recordset. See also http://www.listensoftware.com/searching_unicode.html.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Setting up the Oracle Data Source Name:8.1.7.2 Oracle Driver
  • NLS_LANG must be change to AMERICAN_AMERICA.UTF8 in the Windows registry.
  • The 8.1.7.2 Oracle ODBC Driver must be download from Oracle and installed on the Server Machine. You must signup as a member of the Oracle Technology Network (OTN) to download the ODBC driver.
  • From the ODBC Driver tabs, select the Workaround item, check "force SQL_WCHAR Support" If you don't check this box the ODBC driver will return garabage.
  • Setup a ODBC Data Source Name (DSN) referencing the 8.1.7.2 Oracle driver.
Oracle unicode_test table
Column Description
unicode_number This field is a varable length string with maximum length of 20 characters. The purpose of this field is to store the 2-byte number associated with the Unicode code point.
code_point This fields is a variable length string with a maximum length of 200 character. The purpose of this field is to store the code point sequences.
create table unicode_test
(
	unicode_number not null varchar2(20),
	code_point varchar2(200)
);
  • With the release of Microsoft Data Access Component (MDAC) 2.5, ADO can be used to return an Oracle cursor through the use of the REF CURSOR type.
  • The both the Oracle 8.1.7.2 ODBC driver and Microsoft OLEDB provider for Oracle support the REF CURSOR type.
  • The unicode_pkg has one procedure, getCodePoint which receives unicode code point criteria and returns a Oracle cursor. The connection.execute method does not work with some Unicode code point within the SQL statement. The solution is to use a ADO command object and bind the search criteria fields as a NULL delimited Unicode strings. This will be explained below.

create or replace package unicode_pkg as type lookup_cur IS REF CURSOR; PROCEDURE getCodePoint(p_code_point in varchar2, p_cursor OUT lookup_cur); end unicode_pkg; / create or replace package body unicode_lookup_pkg as procedure getCodePoint(p_code_point in varchar2, p_cursor OUT lookup_cur) IS BEGIN if p_code_point='ALL' then OPEN p_cursor for select * from unicode_test; else OPEN p_cursor for select * from unicode_test where code_point like p_code_point; end if; end getCodePoint; end unicode_lookup_pkg; /

  • From Unicode.html the user enters a Unicode code point that has been stored in the database and presses search.
  • The search occurs in unicode_display.asp
	<script language=javascript>
	function submitPage()
	{
		document.frmMain.action='unicode_display.asp';
		document.frmMain.method='POST';
		document.frmMain.submit();
	}
	</script>

	<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8">

	<FORM NAME=frmMain>
	<pre>
	Unicode Code Point:<INPUT TYPE="text" NAME="code_point" VALUE="ALL">
	</pre>
	<br>
	<INPUT TYPE="button" VALUE="Search Unicode" onClick="submitPage();">
	</FORM>
Active Server Page: unicode_display.asp
  • The unicode_pkg has one procedure, getCodePoint which receives unicode code point criteria and returns a Oracle cursor. The connection.execute method does not work with some Unicode code point within the SQL statement. The solution is to use a ADO command object and bind the search criteria fields as a NULL delimited Unicode strings.
cmd.CreateParameter _ ("@p_code_point",adVarWChar,adParamInput,200,sCode_Point) This command parameter call @p_code_point binds an Unicode Null delimited string as an input parameter for the unicode_pkg.getCodePoint Procedure. This allows the user to submit Unicode code points to be search in the Database.
<%@Language=VBScript%>
<!--#INCLUDE FILE="adovbs.inc"-->
<%Response.Buffer = True

session.CodePage=65001 'UTF-8 Codepage supported only in iis5.0
Server.ScriptTimeout=12000

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "your_8.1.7.2_dsn", "user_name", "password"

dim lCount, sCode_Point

set rstUnicode=nothing

		sCode_Point=Request("code_point")
	
		set cmd=Server.CreateObject("ADODB.Command")
		cmd.ActiveConnection=cnn
		cmd.CommandText="unicode_pkg.getLocality"
		cmd.CommandType=adCmdStoredProc
		cmd.Parameters.append _
   cmd.CreateParameter("@p_code_point",adVarWChar,adParamInput,200,sCode_Point)

		Set rstUnicode = cmd.Execute


%>
<HTML>
<HEAD>
	<LINK REL="stylesheet" HREF="rnd_unicode.css">
	<TITLE>TR Unicode Results</TITLE>
	<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8">
</HEAD>

<BODY style="font-family: arial unicode ms">
	<SPAN class="custH1">Unicode  </SPAN><BR>
	
	<FORM NAME="frmMain" METHOD="post">

	<%
	if not rstUnicode is nothing then
		lCount=0
		do while not rstUnicode.EOF%>
		<%
		lCount=lCount+1
		
		'if lCount > 100 then
		'	exit do
		'end if
		%>
		<TABLE BORDER=1 width=500>
		<TR>
			<td width=25%><SMALL><%=lCount%></SMALL></TD>
			<TD width=50%>
				<%=rstUnicode("code_point")%>
			</TD>
		</TR>
		</TABLE>
			<%rstUnicode.movenext%>
		<%
		response.flush
		loop%>
	<%end if%>
	
	

	</FORM>


<%
if not rstUnicode is nothing then
	rstUnicode.close
end if
cnn.close
Set cnn = Nothing%>

</BODY>
</HTML>