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
"Bob Beauchemin" <(E-Mail Removed)> wrote in message
news:OQHrDXwVCHA.2136@tkmsftngp08...
> 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
> "Terry Blankers" <test> wrote in message
> news:uUF3T4uVCHA.2328@tkmsftngp09...
> > 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
> >
> > "Bob Beauchemin" <(E-Mail Removed)> wrote in message
> > news:#BXf1vuVCHA.2336@tkmsftngp08...
> > > 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
> > >
> > >
> > > "Terry Blankers" <(E-Mail Removed)> wrote
in message
> > > news:#fF7ZzsVCHA.2304@tkmsftngp10...
> > > > 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;
> > > > /
> > > >
Similar Threads
