|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 28th, 2003, 05:40 AM
|
|||
|
|||
Vb code for manipulating multiple record data returned from PL/SQL
Hi
I have a oracle stored procedure which returns a TABLE of NUMBERS(100) ie (defined as OUT) in PL/SQL can any one help me to call the procedure and store the returned value in visual basic 6 so that i can manipulate the same . I use ADODB , & ORALCE PROVIDER FOR OLEDB for connection what are the different ways I can call this stored procedure to return the above data ? Thanks. 
|
|
#2
October 28th, 2003, 07:49 AM
|
||||
|
||||
|
if you'll use the ADODB Command object, then you can dimension parameters to match the ones in your Stored Procedure argument list. That includes a parameter type called adParamOutput.. .here's an example..
Code:
dim cmdSample as ADODB.Command
dim rsSample as ADODB.Command
Set cmdSample = New ADODB.Command
Set cmdSample.ActiveConnection = YOUR ACTIVE CONNECTION
cmdSample.CommandText= "YOUR_STORED_PROCEDURE"
cmdSample.CommandType = adCmdStoredProc
With cmdSample.Parameters
.Append cmdSample.CreateParameter("@SampleInput", adChar, adParamInput, Size:=15)
.Append cmdSample.CreateParameter("@SampleOuput", adVarChar, adParamOutput, Size:=100)
end with
with rsSample
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open Source:=cmdSample
end with
You'll have to make sure that you're using the right Data Access Provider for Oracle (obviously).. .I"m guessing that SQLOLEDB will work?
__________________
Fisherman "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
|
|
#4
October 28th, 2003, 11:31 AM
|
||||
|
||||
|
well, in most cases, when you are dealing with a Stored Procedure (PL-SQL statements) you will have parameters that you pass in (and possibly out), just like passing a parameter to (or from) a function or sub in VB. For example, let's say that I've got a parameterized Stored Procedure in which I am selecting certain records from a table... the SQL syntax for the statement would, of course, look like this
Code:
Select * from tblSomeTable Where [Field] LIKE [value] well.. with PL-SQL, I can dimension variables for that SQL Statement, like this Code:
Declare @SampleInput as char 15, Declare @SampleOutput as varchar 1000 Begin @SampleOutput= Select * from tblSomeTable Where [Field] Like @SampleInput return @SampleOutput Go Doing this does several things. It lessens the possibility of an injection attack by a hacker, as well as processing time on (what could be) a slower, less powerful client, thus making a "fatter" server app. Also, it makes it easier to loop through values on the client side by just reassigning the corresponding ADODB Command Parameters to new values and reexecuting, and when dealing with SQLServer, it will actually optimize the query and hold it on the machine, waiting for parameters to be passed... this makes statements "faster" because they don't have to be instantiated by SQLServer at every call. Sorry to ramble, CP, but here's your answer. the @SampleInput and @SampleOutput in the VB Code match the parameter declarations inside of the Stored Procedure, thus allowing the application to access the data produced by the SP. Hope I didn't insult anyone's intelligence... I'm kinda long-winded! 
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > Vb code for manipulating multiple record data returned from PL/SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
