DataTable Adapters and Oracle Provider (2005)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using the GUI tools to have a Table Adapter's Fill& GetData proc point to a
SPROC in the database (or a function) works well if the proc is a Function
that has no parameters, OR if it is a proc and you are using SQL Server.

In my case, I want to use functions inside Oracle, and want to pass
parameters. First I tried using SPROC. Note I'm not trying to return a
cursor here - my goal is simply to get the SPROC to run.


CREATE OR REPLACE PROCEDURE getScreenByApparatus (parmapparatusID in varchar)
IS
myCursor doe_pkg.TPI_Cursor;

BEGIN

open myCursor for
Select scr.screenID, scr.screenType,scr.Description, scr.userid
from screen scr, screenprocessstep sps
where sps.apparatusID = 1 -- parmApparatusID
and sps.screenID = scr.screenID;
end getScreenByApparatus;


--- End SQL ---


In the DataSet designer, I set the SCREENTableAdapter (attached to the
SCREEN object I dragged over) to use these values:

Command Text: getScreenbyApparatus
CommandType: StoredPrcocedure
ExecuteMode: Reader
Parameters:
Parameter1:
DbType: AnsiString
Direction: Input
ProviderType: VarChar

I Right click Preview Data, select the table and procedure, and fill in the
Parameter (either Hi There, "Hi There" or "hiThere")

I then get back a dialog:
Could not be previewed:
ORA-6550: line 1 column 7: PLS-00306. wrong number or types of arguments in
call to 'GETSCREENBYAPPARATUS'
ORA-6550:line 1, column 7
PL/SQL: Statement ignored.

(I know I have a select statement that's pointing into a cursor, and I've
tried this with an OUT cursor as the second parameter, OUT cursors as return
values, but will skip that discussion until I get the basics working).

This all works smashingly well in SQL Server.

What does it all mean?



However, when I create the
For example, my Oracle
 
Hi,

I tried it on my machine, however, it cannot be reproduced. Did you used
the latest version of Visual Studio .NET 2005 RC and get this error?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks for looking at this kevin. So you're saying that you can access an
Oracle 10g provider through a SPROC and have a Number parameter passed in
through a DataTable adapter?

If so, could you please post the code?

Thanks
 
I think this is the latest. My copy of the ISO is dated 23 Sept 2005, and
MSDN says the image was updated 12 Sept.


From Copy Info:


Microsoft Visual Studio 2005
Version 8.0.50727.26 (RTM.050727-2600)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Enterprise

Microsoft Visual Basic 2005 55603-000-0000016-00279
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 55603-000-0000016-00279
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 55603-000-0000016-00279
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 55603-000-0000016-00279
Microsoft Visual J# 2005

Microsoft Visual Studio Tools for Office 55603-000-0000016-00279
Microsoft Visual Studio Tools for the Microsoft Office System

Microsoft Visual Web Developer 2005 55603-000-0000016-00279
Microsoft Visual Web Developer 2005

Visual Studio 2005 Team Edition for Architects 55603-000-0000016-00279
Microsoft Visual Studio 2005 Team Edition for Software Architects

Visual Studio 2005 Team Edition for Developers 55603-000-0000016-00279
Microsoft Visual Studio 2005 Team Edition for Software Developers

Visual Studio 2005 Team Edition for Testers 55603-000-0000016-00279
Microsoft Visual Studio 2005 Team Edition for Software Testers

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005
 
I think I have it. I changed my SPROC (or my function) on the SQL Server from

Function getScreenByAppID (apparatusID IN NUMBER)


to
Function getScreenByAppID (apparatusID NUMBER)


and all went well.

Thanks
 
It was nice to hear that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top