Private Sub AddParamToSQLCmd(ByVal sqlCmd As OleDbCommand, ByVal paramId As String, ByVal sqlType As OleDbType, ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, ByVal paramvalue As
Object)
' Validate Parameter Properties
If sqlCmd Is Nothing Then
Throw New ArgumentNullException("sqlCmd")
End If
If paramId = String.Empty Then
Throw New ArgumentOutOfRangeException("paramId")
End If
' Add Parameter
Dim newSqlParam As New OleDbParameter
If paramSize > 0 Then
newSqlParam.Size = paramSize
End If
If Not (paramvalue Is Nothing) Then
newSqlParam.Value = paramvalue
End If
sqlCmd.Parameters.Add(newSqlParam)
End Sub 'AddParamToSQLCmd
Oracle Table
create table temp_table_sb
as
(id number(10,0) not null,
name nvarchar2(10));
Oracle Package
Create or replace package temp_sb
is
Procedure Test(s in nvarchar2);
end;
create or replace package body temp_sb
is
Procedure Test(s in nvarchar2)
is
nid number :=0;
begin
select max(id) + 1 into nid from temp_table_sb;
dbms_output.put_line('RETURN : ' || nid);
insert into temp_table_sb values (nid, s);
End Test;
End;
In SQL*PLUS
SQL> set serveroutput on
SQL> declare
2 begin
3 temp_sb.test(N'xxx');
4 end;
5 /
RETURN : 12
1) what is the table definition. varchar2 doesnt support unicode, you have to use a diffrent datatype which escapes me at the moment
2) why are you not using one of the managed providers for oracle?
If the answer I provided is useful or informative please check the "answer" button.
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
Well, I defined the field as NVARCHAR2, and used Micosoft provider before too, but still can't find the problem.
But I realised if it will work even i declared them as varchar2, as long as my pc (win 2000) can support Jap characters.. Need to reboot the system before I can save them properly.
Sharkie_bb
Member
10 Points
2 Posts
How to save Jap. Characters using StoredProcedure in VB.Net into Oracle
Nov 27, 2005 01:13 AM|LINK
Hopefully you can help me with this:
I am trying save some japanese characters into oracle using vb.net, but the information saved always turn into junks. The system did not return any errors..
I can insert and view the Jap Chars literally in SQL*Plus..
My Vb.Net Code:
Sub saverecord()
Dim cn As New OleDbConnection ("Provider=OraOLEDB.Oracle.1;Password=Password1;Persist Security
Info=True;User ID=user1;Data Source=DEVDB;PLSQLRSet=1;")
Dim sqlCmd As New OleDbCommand
sqlCmd.Connection = cn
cn.Open()
AddParamToSQLCmd(sqlCmd, "@Type", OleDbType.VarWChar, 255,ParameterDirection.Input, ",í,©,¡,ÜB")
sqlCmd.CommandText = "temp_sb.test"
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.ExecuteNonQuery() 'illegal number
End Sub
Private Sub AddParamToSQLCmd(ByVal sqlCmd As OleDbCommand, ByVal paramId As String, ByVal sqlType As OleDbType, ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, ByVal paramvalue As
Object)
' Validate Parameter Properties
If sqlCmd Is Nothing Then
Throw New ArgumentNullException("sqlCmd")
End If
If paramId = String.Empty Then
Throw New ArgumentOutOfRangeException("paramId")
End If
' Add Parameter
Dim newSqlParam As New OleDbParameter
newSqlParam.ParameterName = paramId
newSqlParam.OleDbType = sqlType
newSqlParam.Direction = paramDirection
If paramSize > 0 Then
newSqlParam.Size = paramSize
End If
If Not (paramvalue Is Nothing) Then
newSqlParam.Value = paramvalue
End If
sqlCmd.Parameters.Add(newSqlParam)
End Sub 'AddParamToSQLCmd
Oracle Table
create table temp_table_sb
as
(id number(10,0) not null,
name nvarchar2(10));
Oracle Package
Create or replace package temp_sb
is
Procedure Test(s in nvarchar2);
end;
create or replace package body temp_sb
is
Procedure Test(s in nvarchar2)
is
nid number :=0;
begin
select max(id) + 1 into nid from temp_table_sb;
dbms_output.put_line('RETURN : ' || nid);
insert into temp_table_sb values (nid, s);
End Test;
End;
In SQL*PLUS
SQL> set serveroutput on
SQL> declare
2 begin
3 temp_sb.test(N'xxx');
4 end;
5 /
RETURN : 12
Thank you & Regards
JeffreyABeck...
All-Star
16423 Points
3329 Posts
Re: How to save Jap. Characters using StoredProcedure in VB.Net into Oracle
Nov 28, 2005 02:15 PM|LINK
2) why are you not using one of the managed providers for oracle?
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
Sharkie_bb
Member
10 Points
2 Posts
Re: How to save Jap. Characters using StoredProcedure in VB.Net into Oracle
Dec 05, 2005 07:43 AM|LINK
Well, I defined the field as NVARCHAR2, and used Micosoft provider before too, but still can't find the problem.
But I realised if it will work even i declared them as varchar2, as long as my pc (win 2000) can support Jap characters.. Need to reboot the system before I can save them properly.
Thanks a millions.