Memory Leak using OO4O in Visual Basic - OO4O and Wizards(Archived)

The following code is for the most part taken from the example that comes with oracle8i client.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase(TestDbName, TestDbUsername & "/" & TestDbPassword, 0&)
OraDatabase.Parameters.Add "BlobData", Null, ORAPARM_OUTPUT
OraDatabase.Parameters("BlobData").serverType = ORATYPE_BLOB
OraSession.BeginTrans
OraDatabase.ExecuteSQL ("BEGIN select blob_data into :BLOBDATA from BLOBTEST_DATA where LOB_ID = 1 for update NOWAIT; END;")
Set WriteBlob = OraDatabase.Parameters("BlobData").Value
FNum = FreeFile
Open FileName For Binary As #FNum
FileSize = LOF(FNum)
ReDim buffer(FileSize)
Get #FNum, , buffer
amount_written = WriteBlob.Write(buffer)
Close FNum
OraSession.CommitTrans
ReDim buffer(0)
Set WriteBlob = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
The application is used to read data from file and write it to a BLOB field in database.
Even though the file is closed, the buffer is reset and the blob, database and session objects are destroyed, the application does not seam to release the memory. After several consecutive tests, the application consumes all memory.
Currently using OO4O version: 8.1.6.3.6
My question is: is there a patch or fix for a this version and/or can anyone recommend any other version of OO4O that does not have this problem?
thank you,
Sergey

I think memory will be realised after you perform command
Oradatabase.Parameters.Remove ("BlobData") 

what if you are using the Fields collection, is there a way to free the memory or does it just apply to the parameters collection?
any help is appreciated! 

Asnwering my own question,
the version of OO4O that solves problem is 3.15.
use oracle client 8.1.7 which has the correct version of oo4o
check the version of oip8.dll file in your ORACLE/ORA81/BIN directory on the client, it should be something like 8.1.7.3.15 or higher. it is also possible to use 8.1.6 client and 3.15 oo4o just make sure you have all the dependencies required by version 3.15 (see oo4o release notes for 3.15 on metalink.oracle.com)
thank you everyone for help,
Sergey

Related

.Net Stored Procedure running very slow!!! :(

Hello,
I had written a simple .Net SP, deployed it and trying to run in SQLPLUS. But it's runnign very slow. Is .Net assemplies runs this much slow. If that is the cases, then what is the use of using these?
Here is the code which I used.
=====================
Public Shared Function AddTwo(ByVal One As Integer, ByVal Two As Integer) As Integer
Return One + Two
End Function
Public Shared Function GetNameFromUserInfo(ByVal Roll As String) As String
Try
Dim SName As String
Dim con As New OracleConnection("context connection=true")
'con.ConnectionString =
con.Open()
Dim Commd As OracleCommand = con.CreateCommand
'Commd.Connection = con
Commd.CommandText = "Select Name From UserInfo Where Roll=:1"
Commd.Parameters.Add(":1", OracleDbType.Varchar2, Roll, ParameterDirection.Input)
Dim rdr As OracleDataReader = Commd.ExecuteReader()
If rdr.HasRows Then
rdr.Read()
SName = rdr.Item("NAME").ToString()
Else
SName = "Record Did not found for Roll number " & Roll
End If
rdr.Close()
Commd.Dispose()
con.Close()
con.Dispose()
Return SName
Catch ex As Exception
Return ex.Message
End Try
End Function
==================================
And when I run # SQL PLUS it's comming with below timminggs..
SQL> select AddTwo(1,1) from dual;
ADDTWO(1,1)
-----------
2
Elapsed: 00:00:04.00
SQL> select GetNameFromUserInfo('101') from dual;
GETNAMEFROMUSERINFO('101')
----------------------------------------------------------------------
Sreedhar
Elapsed: 00:00:07.00
========================================
==========*************************==============
So Can you please let me know how can I increase the run time, is I have to change any settings or this is the speed I will get? Then what is the use of using .Net sp's, if they run this much slow.?
Can you please show some light on it! 
I am running the latest XE and ODT for XE on the same machine. The first time AddTwo is run (and the CLR Host is loaded) it takes .65 sec. After that it is .01 sec or even .00 sec.
Tell me your configuration... what version of Oracle, and what version of ODT. Also, are you going across a network? 
If it is an older version of Oracle, you are going to need to do some tuning of your CLRAgent service as per the release notes -- the defaults are less than ideal:.
Here is mine:
OraClrAgnt.exe agent_sid=CLRExtProc max_dispatchers=2 tcp_dispatchers=0 max_task_threads=6 max_sessions=25 
Probably while doing I might have changed. Mine I have 'OraClrAgnt.exe agent_sid=CLRExtProc max_dispatchers=1 tcp_dispatchers=0 max_task_threads=2 max_sessions=5' Probably it ran slow.
Now I changed the settings as you had. It's working fine :)
Thanks! 
The values you listed there are the defaults we used in the first release of Oracle 10.2. We included in the release notes a warning that those defaults are too slow. Since then (for example with XE), we use better defaults like the ones I listed.
Christian

loss of data while updating records via ODBC

Hello,
i faced some issue while i try to update some data to an Oracle table within a web interface.
All characters with accent like "è", "à", "é"... are ignored.
For example :
- the string "Générale" is recorded into the database like this : "Gnrale"
- the string "Generale" is correctly recorded.
I suppose the issue comes from ODBC, because the same statement on SQL Plus is working fine.
Server web has following configuration :
- Windows Server 2003 SP2
- Oracle client version 10.2.0.1
- MDAC 2.8 SP2
- drivers Microsoft ODBC installed : version 2.576.3959
Thanks for any idea 
I've never seen that behavior. Usually characters the db doesnt recognize either get stored as upside down question marks, or the similar character without the accents. I've never seen where they just get dropped.
With the limited info available I don't have much to offer apart from general suggestions:
1) make sure your database characterset supports the characters you're using. You can find out your db charset via
SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET%';
2) use bind variables rather than literals if you're not already.
3) try using Oracle's ODBC driver instead of Microsoft's.
Corrections/comments welcome
Greg 
Hi Greg,
1) The statement shows :
PARAMETER VALUE
------------------------------ --------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
This Character set is supposed to be the good one; Update statements work fine within a SQL Plus / Sql Developer session.
2) i generated some Oracle trace and it seems that bind variables are used.
3) I don't get your point. How could i use Oracle's ODBC driver instead of Microsoft's ? 
I'm assuming you're using Microsoft's driver as you indicated "Microsoft ODBC installed : version 2.576.3959". To use Oracle's driver instead, you'd select the Oracle ODBC driver when you create the DSN. Usually it will be named something like "Oracle in OraHome1".
I'd recommend you put together a small testcase to reproduce the complaint.
If you're using IIS/ASP, try the following, and/or modify it if it doesn't reproduce the complaint for you.
Greg
<%
' create table onecol (col1 varchar2(4000));
Const adVarChar = 200
Const adParamInput = &H0001
set con = server.createobject("adodb.connection")
con.open "dsn=orcl;uid=scott;pwd=tiger"
set cmd = CreateObject("adodb.command")
cmd.CommandText = "insert into onecol values(?)"
cmd.ActiveConnection = con
Set prmstr = cmd.CreateParameter("",adVarChar, adParamInput, len("Générale"), "Générale")
cmd.Parameters.Append prmstr
cmd.execute
Response.Write "inserted<br/>"
set rs = server.createobject("adodb.recordset")
rs.open "select * from onecol",con
while not rs.eof
  Response.Write rs.fields(0).value & "</br/>"
  rs.movenext
wend
'cleanup ommitted
%>MY OUTPUT
-------------
inserted
Générale

OracleCommand.FetchSize is causing data corruption using 11g and ODP.Net

I am having an issue where when I set the FetchSize on my oraclecommand object, I get invalid data using the Oracle.DataAccess dll version 2.112.1.0. As best I can tell, the corruption happens on the 24,246 record every time. This doesn't happen when I use the 10g dll...only with 11g.
I saw the question and responses from the link below, but I am pretty sure we are up past 11.1.0.7.20. Re: OracleDataAdapter.Fill returns incorrect data for small numbers.
Any thoughts...I don't want to go the route of assuming that self tunning=false will fix it.
OracleCommand = New OracleCommand(pair.Value, OraConnection) 
_OracleCommand.FetchSize = &H1000000 
'_OracleCommand.AddToStatementCache = True 
dataAdapterTEST = New OracleDataAdapter(_OracleCommand) 
 
'Check if Oracle connection is closed 
If _OraConnection.State = ConnectionState.Closed Then 
      _OraConnection.Open() 
End If 
 
'Fill DataTable(s) and name them 
dataAdapterTEST.Fill(_tempDataSet, pair.Key)

Problem: "Multiple-step OLE DB operation generated errors"

When executing a simple stored procedure using ADO 2.6 (Win2000) i receive the following error:
(0x80040e21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
The commands works just fine using Microsofts ODBC for Oracle. Any suggestions?
In advance, thank you for your assistance...
I'm running the following code:
Dim Connection As ADODB.Connection
Dim Command As ADODB.Command
Dim Parameters As ADODB.Parameters
Dim Parameter As ADODB.Parameter
Set Connection = CreateObject("ADODB.Connection")
Connection.ConnectionString = "DSN=**;UID=**;PASSWORD=**;"
Connection.Open
Set Command = CreateObject("ADODB.Command")
Command.ActiveConnection = Connection
Command.CommandType = adCmdStoredProc
Command.CommandText = "Test.Works"
Set Parameters = Command.Parameters
Set Parameter = Command.CreateParameter("inParam1", adVarNumeric, adParamInput, 10, 2)
Parameters.Append Parameter
Set Parameter = Command.CreateParameter("inParam2", adVarNumeric, adParamInput, 10, 4569)
Parameters.Append Parameter
Set Parameter = Command.CreateParameter("outID", adNumeric, adParamOutput)
Parameters.Append Parameter
Command.Execute
RequestID = Parameters("outID").Value 
Problem solved...
If the Oracle Stored procedure uses a numeric parameter value type, the ADO parameter must explicitly set Precision and NumericScale property.
Ex.:
Set Parameter = Command.CreateParameter("inRequestType", adNumeric, adParamInput, 10, 2)
Parameter.Precision = 10
Parameter.NumericScale = 0
Parameters.Append Parameter
If Parameter.Precision and Parameter.NumericScale is left out, the error occurs.
null 
I've just encountered the same problem and this solution works great. I store the parameter information in advance with selecting data from all_arguments view into a disconnected recordset and building the parameters collection based on the calling object passing in the stored procedure name.
However, it appears the precision and scale are necessary for binding parameters to a Select statement of type numeric.
(e.g. Select a, b, c from x where a=?)
This seems to be inconvenient to know or cache every tables field precision and scale ahead of time.
Our code has never required precision and scale using ADO with MS ODBC for Oracle.
Does anyone have any suggestions?
TIA --
Jon Sklut
null 
(Originally posted in Oracle Provider for OLEDB)
My architecture uses a select against the All_Arguments view to cache stored procedure parameters which include data type, precision, scale, in/out, and size into a disconnected recordset. There is one DLL that encapsulates all I/O activity.
When building the ADOCommand object's parameters collection, I first filter the disconnected recordset based on Stored Procedure name I pass in and iterative build the collection and bind the values of each parameter I pass in also.
I was working with the latest Oracle ODBC driver (8.1.7.4) just before testing this OLEDB provider. If you look in the Oracle 8 ODBC discussions and find the topic "Problem: Multiple-Step OLE DB Operations generate errors", this was actually the same issue. The original developers posted his solution which was to specify Precision and Scale for numeric parameters. This worked for ODBC, but not for OLEDB.
Well, I do not know if this is the optimal solution (maybe someone has one though), but when you define a numeric parameter, you can use adDouble (=5) rather than adNumeric (=131) when creating the adoCommand.Parameter. This worked for OLEDB and ODBC.
Please share your thoughts on this.
J Sklut

ADO Performance vs OO4O Performance

I have managed to get OO4O to outperform ADO in returning a resultset. I have not managed to get OO4O to outperform ADO in executing a PLSQL Procedure. I will place my vb code below. Right now my ADO code is performing 8 times faster than my OO4O code.
Chris
Dim dsess As OraSession
Dim dbase As OraDatabase
Dim dParams As OraParameters
Dim p_return As String
'Create the OraSession Object.
Set dsess = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set dbase = dsess.OpenDatabase(tnsString, loginString, 4)
Set dParams = dbase.Parameters
dParams.Add "P_RETURN_TX", P_RETURN_TX, ORAPARM_OUTPUT, 1
dParams.Add "P_START_NO", 1, ORAPARM_INPUT, 2
dParams.Add "P_COUNT_NO", 10, ORAPARM_INPUT, 2
dParams.Add "P_USER_LOGON_TX", USER_LOGON, ORAPARM_INPUT, 1
dParams("P_RETURN_TX").MinimumSize = 10000
dbase.ExecuteSQL ("Begin TOPS_PCK.LIST(:P_RETURN_TX, :P_START_NO, :P_COUNT_NO, :P_USER_LOGON_TX); end;")
p_return = dParams("P_RETURN_TX").Value
dParams.Remove "P_RETURN_TX"
dParams.Remove "P_START_NO"
dParams.Remove "P_COUNT_NO"
dParams.Remove "P_USER_LOGON_TX"
dbase.Close
Set dbase = Nothing
Set dsess = Nothing
getSP = p_return
Does the ratio hold true if you include only the procedure call and eliminate the COM invocation and intialization code (in other words remove the database and session initialization code)?
I imagine the overhead to initialize OO4O might be more.
I tested the connection times for ado and oo4o. ado was 80 times faster to connect to the database than oo4o. That right there would make all the difference. I am using a vb dll from ASP and connect and disconnect on every request.
Perhaps a connection pool would help? Even if you don't need it for other reasons, you could use it to maintain the COM server and really cut down on connection time.
I don't have that much control over my environment. They are using MTS to manage their connections. I am unclear on how this will affect OO4O. I know OO4O is supposed to play nice with MTS but that is the extent of my knowledge.

Categories

Resources