hi All,
Iam new in oracle stored procedure, i have one stored procedure and it has to be accessed from vb6. my stored procedure as follows

-- employee salary will be entered by the user and itshould show employees less than the the given salary

CREATE OR REPLACE PROCEDURE employee1(empcode OUT VARCHAR2,
empname OUT VARCHAR2, POSITION OUT VARCHAR2, sal IN NUMBER) IS

CURSOR c1 IS SELECT empid, empname, grade FROM empmst
WHERE basicsal < sal;

BEGIN
  OPEN c1;
  LOOP
    FETCH c1
    INTO empcode, empname, POSITION;
    EXIT
  WHEN c1 % NOTFOUND;
END LOOP;

-- my vbcode is the following
    Set db = New ADODB.Connection
    db.CursorLocation = adUseClient
    Set prempcode = New ADODB.Parameter
    Set prempname = New ADODB.Parameter
    Set prbasicsal = New ADODB.Parameter
    Set prposition = New ADODB.Parameter
    Set rsempmst = New ADODB.Recordset
    rsempmst.CursorType = adOpenStatic

DBDriver = "Provider=MSDAORA.1;Password=ttt;User ID=ttt;Persist Security Info=True"

db.Open DBDriver

Set cm = New ADODB.Command
    With cm
    .ActiveConnection = db
    .CommandType = adCmdStoredProc
    .CommandText = "employee1" 'Name of the ORACLE Function, in String
    .CommandTimeout = 10
    End With
    cm.CommandText = "employee1"

cm.Parameters.Append cm.CreateParameter("empcode", adVarChar, adParamOutput, 200)

cm.Parameters.Append cm.CreateParameter("empname", adVarChar, adParamOutput, 200)

cm.Parameters.Append cm.CreateParameter("position", adVarChar, adParamOutput, 200)


cm.Parameters.Append cm.CreateParameter("basicsal1", adNumeric, adParamInput, , Val(Text1.Text))


Set rsempmst.Source = cm.Execute
rsempmst.Open
Do Until rsempmst.EOF
    MsgBox (rsempmst("empid"))
rsempmst.MoveNext
Loop

' this code showing error that
Run time error 3001
Arguments are wrong type are acceptable range, or are inconflict with
one another

Plz helppppppppppp
Thanks

Your line appears to try to retrieve an SQL string as the result of execution. But your procedure (as written) doesn’t return anything in that fashion. You have set it up to return one answer in the procedure’s arguments.

Is there some reason you don’t want to just open a recordset with the constraining SQL? That would be better...     (Some of this syntax might not be right; I did not put this together in a VB IDE, but in Word.)

hi dear thanx for your reply,
recordset only iam using prevsly now i would like to learn procedure and command,
if you chek my procedr there is out parameteres for returning data's.it will be greatefull
if you can tell me how the procedure should be and the vb

Those out parameters can only return one EmpCode (a string), one EmpName (a string), and one number. (Please note how selective capitalization can make the intent of a variable clearer. Note the syntax I am using in answering you: there should be a space following periods at the end of a sentence, and the first letter of a sentence is capitalized.)

But your statement Set rsempmst.Source = cm.Execute indicates that you were expecting fully-formed SQL to be returned. (Plus .Source would always be a string, so the Set keyword would be a syntax error.)

I don't know if you can retrieve the return value of a stored procedure which is a Function. I've never tried.

But your stored procedure should either put the SQL into an OUT argument, or you should use the returned arguments to create the SQL in your VB routine:                       The string in the command's parens is that which you named it in the VB, not the name in the stroed procedure. (They might be the same, but it is the string in VB that matters.)

In your stored procedure, the Fetch Into happens to load the output parameters. But if you are going to return fully-formed SQL, you would build that SQL in the stored procedure using values that are filled by the fetch into, then use <Out-Parameter-Name> = . . . (and put your finished string after the = ).

Finally, you are using a loop in the stored procedure. But all that would happen is that the loop would run, then the last values fetched would remain in the parameters when the procedure terminates. Then your VB would read those final values. You cannot return a series of values from a stored procedure. They behave [u]exactl</u>y like modifying the ByRef arguments of a VB Sub. If there is a loop in that Sub, the arguments will get modified for each iteration, but the calling routine will only see the final value(s).

Dear Mr.BrianWren,
Thank you very much for your answer, actually i am really new in this stored procedure and command(vb), previously im using recordset, but i know that for a better program and fast ,we have to use stored procedure, so i tried in my old program, my basic intention is
user should enter salary parameter, and system should retrieve all the data's which matching the criteria, so can u modify and give me the procedure how it should be and vb code to call the procedure, it would be grateful so that i can continue and learn more. Thanks once more for your time.

Here is the format for a stored procedure that returns a value, that is, a function: But note well the bold-blue parts. Thes are what are called scalar variables. That meanc that they can only hold a value.

A recordset is not a value. I am pretty sure that you cannot return a recordset from an Oracle function.

You say, “ . . . i know that for a better program and fast ,we have to use stored procedure . . . ” (you should capitalize ‘I’ when referring to yourself...) But I don’t know that that is a valid statement. If you need a collection of records in your VB, it looks to me like your only choice is a RecordSet object. And they are not all that slow.

You can speed up your application substantially with indices in Oracle. It is helpful to not add more columns to your SQL than you absolutely need. So avoiding “SELECT *” can be helpful. (The exception would be when you are going to return all columns; in that case using “*” instead of spelling out every column can save a few micro-seconds in the string that is transmitted to Oracle.)

You know, there is the additional consideration that your code will be much more maintainable and readable if the behavior isn’t spread across several different technologies, but is all present for the reading in the VB routine. Then you don’t need to have both VB and Procedure Builder open at the same time just to see what the plan is.

Thanks for your time and reply,
Actually I want to learn procedures and how it can be used in vb6, how can a procedure can return morethan one value,I heard that
by using sys_cursor we can open record set in procedure and it can be
return more than one value.

I have returned more than one value by concatenating the values into a delimited string, then looking for the delimiter in VB (Using split, or looping through the string with InStr()).
Of course, you can have multiple OUT parameters. That's not returning a value as a function does with “return,” but you do get the values.

(A tip: you are using commas , where you should use periods .)

Thank you very much for your reply, I trying to use those.

I know quite some time has passed and hopefully you've already figured things out... If not, please know that Oracle is weird. In your procedure you have to declare an IN OUT variable based on a custom type that is created from a REF CURSOR.

So... step 1 is to create an oracle package (and its body) that has the ref cursor type.

CREATE OR REPLACE PACKAGE PKG_TYPES IS
  TYPE OUT_CURSOR IS REF CURSOR;
END PKG_TYPES;
/
CREATE OR REPLACE PACKAGE BODY PKG_TYPES IS
END PKG_TYPES;
/

Step 2. is to create your procedure so that it includes an IN OUT parameter of the PKG_TYPES.OUT_CURSOR type:

CREATE OR REPLACE PROCEDURE usp_SomeProcedure(SomeDateParam IN DATE, out_cursor IN OUT rti_report.pkg_types.OUT_CURSOR) IS
BEGIN
  OPEN out_cursor FOR
  SELECT * FROM SomeTable
  WHERE SomeField = SomeDateParam;
END;

Step 3. I can't give you details as I use C# and PHP. In VB you will have to define a command object with the appropriate parameters. In this example you would define both the 'SomeDateParam' and the 'out_cursor' parameter. However, you'd only set the value for 'SomeDateParam'. Once you execute the command to fill the resultset it should behave like SQLServer.

Setting things up this way allows you to reuse PKG_TYPES.out_cursor repeatedly for each procedure you write that needs to return results.

Hope this helps someone.