VB.NET passing CLOB input parameter to Oracle stored function

[A B]

Hello,

I've included a posting from a couple years ago regarding passing a CLOB
from VB.NET to an Oracle stored procedure. The poster seemed to have
success, but when I use Mr. Beauchemin's sample below I get:

"ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored"

Is it possible this worked in an earlier release of .NET but no longer
works? Does anybody have any tips on properly passing the file stream
to a CLOB Oracle parameter? Bob??

Thanks,

AB




--------------


From: Terry Blankers (test)
Subject: Re: Oracle CLOB problems in managed provider - ¿¿¿
View: Complete Thread (5 articles)
Original Format
Newsgroups: microsoft.public.dotnet.framework.adonet
Date: 2002-09-08 15:49:29 PST

Thanks a million, Bob!!! Works like a charm! Sure would be nice to see a
little example like yours up on the MS KB.

Regards,

Terry

Terry Blankers

eBuilder, LLC

http://www.ebuild.net
415.215.2124 : direct
415.242.5680 : main

Here's a real simple one. The stored procedure, insert_clob_po, takes two
parameters POID and POCLOB and uses them to insert a row into the database.
If you translate to VB.NET, don't forget the double backslash in the
filename "c:\\authors.xml" is not required in VB.NET.

Cheers,
Bob Beauchemin
http://staff.develop.com/bobb

void InsertClobFromFile()
{
OracleConnection conn = new OracleConnection(
"data source=foo;user id=scott;password=tiger");
OracleCommand cmd = new OracleCommand(
"insert_clob_po", conn);
FileStream s = File.OpenRead("c:\\authors.xml");
StreamReader r = new StreamReader(s);

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("POID", OracleType.Number);
cmd.Parameters.Add("POCLOB", OracleType.Clob);
cmd.Parameters[0].Value = new OracleNumber(3); // POID = 3
cmd.Parameters[1].ParameterName = "POCLOB";
cmd.Parameters[1].Direction = ParameterDirection.Input;
cmd.Parameters[1].Size = (int)r.BaseStream.Length;
cmd.Parameters[1].Value = r.ReadToEnd();

conn.Open();
int i = cmd.ExecuteNonQuery();
Console.WriteLine("{0} rows inserted", i);
}n message

Thanks for the response, Bob.

From every thread I've read on this newsgroup, I thought that you had to use
the temporary LOB method. I've tried to directly use a OracleType.CLOb
parameter and didn't have any success either. Do you have a piece of sample
code you show me?

Thanks again,

Terry

Hi Terry,

What it sounds like you are seeing a a Unicode to char conversion problem.
By attempting to create the temporary lob, open it read write and use it to
do the insert, you are first getting the CLOB as 2-byte Unicode and trying
to write those Unicode characters in (through the Stream) while "telling"
the stored procedure that the are single byte characters. This is because
the Microsoft provider always handles CLOB and NCLOB as Unicode using
OracleLob.

Save yourself the hassle and call the stored procedure you've written
directly (without the temporary LOB) using a parameter of OracleType.Clob.
If you must use a temporary LOB for inserts you'll have to do the Unicode to
character conversion or define an NCLOB in the database.

Hope this helps,
Bob Beauchemin
http://staff.develop.com/bobb


Hi,
I've read every posting and KB article I can find on CLOBS and am about to
throw in the towel. I'm using ADO.NET and the new MS OracleClient for .NET.
I'm trying to insert a CLOB into Ora9.2 DB using a stored package or
procedure. And then I'm trying to fetch that CLOB back using either a stored
procedure or the OracleDataReader.

I appear to be able to read a text file and then write to the CLOB column
but when I try to view the column in TOAD or SQLPlus Worksheet I only see
the following:

¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿¿ [...and so on...]

When I try to fetch the column value back, I get the same results or an
empty file if I try to write the results to a file.

Any clues what I'm doing wrong? And what's the significance of upside-down
?'s ?

Thanks in advance for any and all help!

Here's my code:

____________________________________________________________________

Sub WriteCLOB

Dim filePath As String
Dim bigData As Byte()

filePath = "c:\winzip.log" 'Add the path to the file you want to
insert

Dim fs As Stream = File.OpenRead(filePath)
Dim tempBuff(fs.Length + 1) As Byte

fs.Read(tempBuff, 0, fs.Length + 1)
fs.Close()

dbConn.ConnectionString() = strConn
dbConn.Open()

Dim tx As OracleTransaction
tx = dbConn.BeginTransaction()

Dim cmd As New OracleCommand()
cmd = dbConn.CreateCommand()

cmd.Transaction = tx

cmd.CommandText = "declare xx clob; begin
dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"
cmd.Parameters.Add(New OracleParameter("tempblob",
OracleType.Clob)).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()

Dim tempLob As OracleLob
tempLob = cmd.Parameters(0).Value
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)
tempLob.Write(tempBuff, 0, tempBuff.Length)
tempLob.EndBatch()


cmd.Parameters.Clear()
cmd.CommandText = "InsertClob.TestClobInsert"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("ClobParam",
OracleType.Clob)).Value = tempLob

cmd.ExecuteNonQuery()

tx.Commit()
dbConn.Close()

End Sub
_____________________________________________________________

Sub ReadCLOB

Dim dbRead As OracleDataReader
dbConn.ConnectionString() = strConn

Dim sql As String = "select xml_string from ws_log where log_key =
25"

Try
Dim cmd As New OracleCommand(sql, dbConn)
dbConn.Open()
dbRead = cmd.ExecuteReader()


Dim tempLob As OracleLob
While dbRead.Read()
If Not dbRead.IsDBNull(0) Then
tempLob = dbRead.GetOracleLob(0)
End If
End While

Dim tempBuff(tempLob.Length) As Byte

tempLob.BeginBatch(OracleLobOpenMode.ReadOnly)
tempLob.Read(tempBuff, 0, tempBuff.Length)
tempLob.EndBatch()

Dim fs As New FileStream("c:\winzip1.log",
FileMode.OpenOrCreate, FileAccess.Write)
fs.Write(tempBuff, 0, fs.Length)
fs.Close()

Catch myex As OracleException
MsgBox(myex.Message)
End Try

dbRead.Close()
dbConn.Close()

End Sub
_____________________________________________________________

Oracle Package:

CREATE OR REPLACE package InsertClob
as
PROCEDURE TestClobInsert (ClobParam in Clob);
end InsertClob;
/
CREATE OR REPLACE package body InsertClob
as
PROCEDURE TestClobInsert (ClobParam in clob)
as
begin

-- INSERT INTO blobtable (myid,blobdata) values(1,BlobParam);

UPDATE WS_LOG
SET XML_STRING = ClobParam
WHERE LOG_KEY = 25;

end;
end InsertClob;
/