Home » Infrastructure » Windows » DBMS_DESCRIBE calls when using MSDAORA / ADO
| DBMS_DESCRIBE calls when using MSDAORA / ADO [message #100583] |
Tue, 25 February 2003 07:18  |
Jon Ireland
Messages: 3
Registered: February 2003
|
Junior Member |
|
|
Oracle trace is logging these extra calls when calling Oracle stored procedures using Microsoft OleDb driver and ADO.
SELECT STATUS
FROM OBJ$
WHERE OBJ# = :b1
SELECT ARGUMENT,OVERLOAD#,POSITION# POSITION,TYPE# TYPE,NVL(DEFAULT#,0) DEFAULT#,NVL(IN_OUT,0) IN_OUT,NVL(LEVEL#,0) LEVEL#,NVL(LENGTH,0) LENGTH,NVL(PRECISION#,0) PRECISION,NVL(SCALE,0) SCALE,NVL(RADIX,0) RADIX
FROM ARGUMENT$
WHERE OBJ# = :b1 AND PROCEDURE$ = :b2
ORDER BY OBJ#,PROCEDURE$,OVERLOAD#,SEQUENCE#
BEGIN SYS.DBMS_DESCRIBE.DESCRIBE_PROCEDURE(:object_name,:res1,:res2,:overload,:position,:level,:argument,:datatype,:default,:in_out,:length,:precision,:scale,:radix,:spare); END
When executing stored procedure from PLSQL Developer we do not get these extra calls.
This leads me to believe it is an ADO / MSDAORA related issue.
All stored procedures are being called thru Visual Basic Com+ objects.
Calling box is has MDAC 2.6 and Oracle Client 8.1.7 installed
Any help on stopping these calls would be greatly appreciated.
Sample Visual Basic Code that I am calling. I have tried several different ways of calling stored procedure but always get the same results.
Dim objCmd As ADODB.Command
Dim objRsTest As ADODB.Recordset
Set objCmd = New ADODB.Command
m_sOracleOLEDBConnectionString = "PROVIDER=MSDAORA;User ID=TestUesr;Password=Test;Data Source=DaSource;Driver={Microsoft ODBC for Oracle};DSN="
With objCmd
.CommandType = adCmdStoredProc
.CommandText = "PKTest.GetTest"
.Parameters.Append .CreateParameter("iParam1", adVarChar, adParamInput, 30, a_sParam1)
.Parameters.Append .CreateParameter("iParam2", adVarChar, adParamInput, 10, a_sParam2)
.ActiveConnection = m_sOracleOLEDBConnectionString
Set objRsTest = .Execute
End With
|
|
|
|
|
|
| Re: DBMS_DESCRIBE calls when using MSDAORA / ADO [message #100606 is a reply to message #100583] |
Wed, 05 March 2003 07:59  |
Jon Ireland
Messages: 3
Registered: February 2003
|
Junior Member |
|
|
I did determine the cause and a fix for the problem.
I used a free tool, which can be installed on the client and will capture SQL with bind variables as it happens:
Statement Trace: http://www.aboves.com/downloads/
The problem is that Microsoft OLE DB provider / ADO does not allow you to create a command output parameter for oracle ref cursors so in the example that I provided this is what happing:
1. OLE DB provider called the stored procedure like this
"begin PKTest.GetTest(:V00001,:V00002); end;"
2. This statement has only two parametures so oracle call errors
"ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GetTest'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored"
3. The OLE Db provider capturers the error and issues A Describe to Oracle
"Time: 05:45:45 Describe [[290937408]] odessp session #
PKTest.GetTest"
4. Which returns the correct parameters list then ole db provider calls the oracle stored procedure again
"begin PKTest.GetTest(IPARAM1=>:V00001, IPARAM2=>:V00002, OLIST=>:R000C000); end;"
This time the ref cursor is returned
I came up with only two solutions for this.
Solution 1.
Use Oracle Provider for OLE DB 8.1.7.3.0 When executing the same code with this provider I did not se the behavior happening. This was not an option for me at this time because this was not an approved dll and it would take some time to get it thru are process so that I could use it. So I had to keep looking for another solution.
Solution 2.
Change these two lines:
.CommandType = adCmdStoredProc
.CommandText = "PKTest.GetTest"
To :
.CommandType = adCmdText
.CommandText = "{call PKTest.GetTest(?,?,{resultset 0, oLIST})}"
This binds all three parameters so oracle will not error.
Hope This helps!
Log out put from Statement Trace:
**** start log for OraTest *************************
Time: 05:45:45 Execute [[290948880]] oexec session #1
begin PKTest.GetTest(:V00001,:V00002); end;
Time: 05:45:45 Error [[0]] oerhms session #2
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GetTest'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Time: 05:45:45 Describe [[290937408]] odessp session #
PKTest.GetTest
Time: 05:45:45 Execute [[290948880]] oexec session #1
begin PKTest.GetTest(IPARAM1=>:V00001, IPARAM2=>:V00002, OLIST=>:R000C000); end;
Time: 05:45:45 Fetch [[290960072]] ofen session #
Query next 1 rows. 1 rows fetched.
**** end log for OraTest *************************
|
|
|
|
| Re: DBMS_DESCRIBE calls when using MSDAORA / ADO [message #100611 is a reply to message #100606] |
Thu, 06 March 2003 08:47 |
Klaus Schulz
Messages: 2
Registered: March 2003
|
Junior Member |
|
|
|
Hi Jon. Unfortunately, we're not using ref cursors or returning anything from the stored procedure. The procedure accepts to integers as input parameters and that's it, and yet we're seeing a call to dbms_describe.describe_procedure when we execute our stored procedure. I'll try changing the call to use ODBC syntax as you suggested and see if that does the trick. I appreciate the input.
|
|
|
|
| Re: DBMS_DESCRIBE calls when using MSDAORA / ADO [message #100613 is a reply to message #100606] |
Thu, 06 March 2003 09:19 |
Jon Ireland
Messages: 3
Registered: February 2003
|
Junior Member |
|
|
Doing a Parameters.Refresh will always cause describe to be made to Oracle.
With adoCommand
Set .ActiveConnection = conn
.CommandText = FUNC_NAME
.CommandType = adCmdStoredProc
.Prepared = True
.Parameters.Refresh ' This causes a DBMS_DESCRIBE call to Oracle
End With
Also I was told that it is possible that a describe might be made if you set the active connection to an open connection BEFORE declaring your parameters. I did not investigate this because I always set the active connection just before I call .execute.
With adoCommand
Set .ActiveConnection = conn
.Execute
End With
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 13:32:43 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
