Visual Basic :: Calling An Oracle SP That Is In A Package

Trouble Calling Oracle Package Using RDO
The code below is not running for me. It's using RDO to access an Oracle Package. Does anyone know what's wrong with it? I would appreciate it if anyone who knows how to fix this problem contact me at bruce.yang@merrillcorp.com.

Thanks.

Public oConn As rdoConnection
Public rstBoxes As rdoResultset
Public rstBoxes1 As rdoQuery
...

Set oConn = New rdoConnection
With oConn
.Connect = "DSN=DCSRENEW;DATABASE=DCSRENEW;UID=DCSRENEW;PWD=DCSRENEW;"
.CursorDriver = rdUseNone
.LoginTimeout = 5
.EstablishConnection (rdDriverNoPrompt)
End With

................

Dim mySQL As String

mySQL = "{call DCSRENEW.process_bates_num.Get_Bates_number_Range(?,?,{resultset 100,
OUTBATESNUM})}"
Set rstBoxes1 = oConn.CreateQuery("", mySQL)
rstBoxes1(0) = txtDocID.Text
rstBoxes1(1) = txtpackageID.Text

Set rstBoxes = rstBoxes1.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

Errorr:
Run time 40002
OUTBATESNUM must be declared..

**note** the out parameter OUTBATESNUM is declared in the store procedure "Get_Bates_number_Range",
and that store procedure has 2 in param & 1 out param.

Oracle Package.Procedure Works But Package.Function() Does Not ?
Hi,

I am trying to call a function in a package in Oracle, from VB. I am using the traditional ADODB with connection, command and parameter objects. The function has 6 input parameters and it returns a string value.

We have no problems reaching the similar Procedure under a package, but when it comes to accessing the function, it gives errors. I got "no descriptor found", after some changes "object stored Procedure does not exist" .

Any suggestions on how differently to access a function within a package as opposed to a procedure ?

Thanks,
Arch

Edited by - Archvile on 9/14/2004 3:07:01 PM

Oracle Package
I want to use vb to call oracle package and get the parameter value passed from oracle package. Please tell me how to do. Some examples will be appreciated. thanks

Include Oracle Into Package
Hey. Hav done a timesheet program witch connect to a oracle server. Installation works but can not run the program. think its because i dont hav oracle client installed on the target computors? Is there another way?

Package Deployment With VB And Oracle
Hi! all,
I have developed accounting applications with VB6/personal oracle8. Now i have to deploy this application. i have used Inno Setup program for package.

when i install this software at client's coputer, is oracle require in the client machine?

how i automize entire set up process so that when set up finish, my application work by clicking shortcut from desktop?

right now i have to manually install oracle at client side. then i make DSN and the i install my application. i want all this process in set up only.

pls help.

Installation Package Using Oracle
hi..

can we create an installation package in VB and oracle and run it on a machine which does not have oracle installed on it.

thanks for your help!!

Compiling Oracle Package From Vb
Hi,
I have saved a oracle pl/sql package in C drive. Now i want to compile it from vb form (say at the click of a button). Any idea how i can do it.
regds,
bhaskar

Help - Calling Oracle SP Using VB
Si or anyone else: hope you can help me here:

If i use a stored procedure in oracle like the following:

CREATE OR REPLACE PROCEDURE addPerson
(Id IN varchar2, name IN varchar2, age IN varchar2, gender IN varchar2)
IS
BEGIN
INSERT INTO Person_tab
(Id, name, age, gender)
VALUES
(Id, name, age, gender);

END;

Could I use the attached code to upload data from Excel? I did exactly the same for Access, that worked.
WIll the code require significant changes? I know I will have to change the connectionstring to oracle and the call to the new procedure (addPerson). What else will I need to change?

Thank you guys

dan

Calling A DTS Package From VB
I built a little vb app that calls a DTS package in the SQL Server. I have it running on my machine, and it works correctly. I now want to run the vb app on a client machine. The problem I have is that the client machine has no SQL server on it. The VB app makes reference to Microsoft DTSPackage Object Library. I read that I only needed to install the dtspkg.dll to run the vb app. I tried to manually register the dll but I get the following error:

LoadLibrary("dtspkg.dll") failed - The specified module could not be found.

Is there something I am missing? Is this what I have to do to run the vb app and call the DTS package. We don't want to install the Enterprise manager tool on the client machine. Any ideas?

How To Use The Datbase Package And The Procedures Of Oracle
Hi

How can i use the package , procedure and function of the oracle with in , out and inout parameters. please give me one example of that .

Regards
Chakresh Kumar

Error In Using RDO To Execute Oracle Package.
The following code is giving me the error below. It's a VB 6.0 app, using RDO to execute an Oracle Package. Can anyone help? I'm using Oracle OraHome92 Driver for my DSN. I am not allow to use ADO to fix the problem, cause it's an existing program, and I need to fix the problem.

Thanks,

Public oConn As rdoConnection
Public rstBoxes As rdoResultset
Public rstBoxes1 As rdoQuery
...

Set oConn = New rdoConnection
With oConn
.Connect = "DSN=DCSRENEW;DATABASE=DCSRENEW;UID=DCSRENEW;PWD=DCSRENEW;"
.CursorDriver = rdUseNone
.LoginTimeout = 5
.EstablishConnection (rdDriverNoPrompt)
End With

................

Dim mySQL As String

mySQL = "{call DCSRENEW.process_bates_num.Get_Bates_number_Range(?,?,{resultset 100,
OUTBATESNUM})}"
Set rstBoxes1 = oConn.CreateQuery("", mySQL)
rstBoxes1(0) = txtDocID.Text
rstBoxes1(1) = txtpackageID.Text

Set rstBoxes = rstBoxes1.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

Errorr:
Run time 40002
OUTBATESNUM must be declared..

**note** the out parameter OUTBATESNUM is declared in the store procedure "Get_Bates_number_Range",
and that store procedure has 2 in param & 1 out param.

Execute Oracle Package Thro VB6
<html><div style='background-color:'><DIV></DIV>
<P>Hi,</P>
<P>I have a oracle package.Now i want to execute that package thro VB6 and retrieve the records and store it in a recordset by passing only one parameter.</P>
<P>Can anyone please share your code with me,</P>
<P>Thanks,</P>
<P>VKT<BR><BR><BR></P>
<DIV></DIV></div><br clear=all><hr>Over 6,70,000 brides and grooms. <a href="http://g.msn.com/8HMEENIN/2755??PS=">Click here to join for free.</a> </html>

Oracle Backup And Create Package
I am using VB6 and Oracle 9i to build up my Hospital Mgt. System Application. I want to add a "CREATE BACKUP" feature in my application. I have created a batch file like this

exp USERID=name/password BUFFER=4096 LOG="c:error.txt" FILE=trialpath TABLES..... etc

s=Shell(f.bat)

It works fine. But i am confused wheather it will work or not when i will build an end user setup package. will it work in a system where oracle is not installed? Plzz help me

With Regards,
Rajarshi

Calling Oracle Function
Hi
I am migrating SQL Server 2000 to Oracle using Oracle Migration Workbench. Following is source code and migrating code for example. I would like to how I can use recordset queried from Oracle function.

****** SQL SERVER 2000 PROCEDURE *****************

CREATE PROCEDURE fun_test
@deptno int,
@ename varchar(25)
AS
SELECT empno, job, sal
FROM emp
WHERE deptno = @deptno AND ename = @ename;

RETURN @@ROWCOUNT

GO

***************************************************

***** ORACLE PACKAGE AND FUNCTION ***************

CREATE OR REPLACE PACKAGE dept_test
AS
TYPE rct1 IS REF CURSOR;
END;
/

CREATE OR REPLACE FUNCTION fun_test (
in_deptno IN NUMBER,
in_ename IN VARCHAR2,
out_rs OUT dept_test.RCT1
)
RETURN NUMBER
AS
rc NUMBER;
BEGIN
OPEN out_rs FOR
SELECT empno, job, sal
FROM emp
WHERE deptno = in_deptno AND ename = in_ename;

COMMIT;
rc := 0;
RETURN rc;

EXCEPTION
WHEN others THEN
ROLLBACK;
rc := -1;
RETURN rc;
END;
/

***************************************************

*********** VB Code to call SQL Server 2000 Procedure *****

Public Function fun_test() As FunctionTest

Dim rstBTF As New ADODB.Recordset

On Error GoTo CountBTFErr

Screen.MousePointer = 11

SQLBuff = "fun_test '" & gdeptno & "', " & gename

rstBTF.Open SQLBuff, gADOConn, adOpenForwardOnly, adLockReadOnly, adCmdText

rstBTF.MoveFirst

Do Until rstBTF.EOF
fun_test.empno = rstBTF!empno
fun_test.job = rstBTF!job
fun_test.sal = rstBTF!sal

rstBTF.MoveNext
Loop

rstBTF.Close

Screen.MousePointer = 1

Exit Function

CountBTFErr:
MsgBox Error(Err) & Chr$(13) & Chr$(13) & "Please Contact Admin."

End Function

********************************************************************** ****

Please help me to solve the problem.

Thanks in advance.

Rana

Calling A Oracle Function In VB
Hi ,
I have a function in a Oracle database which is returning a value. I want to call that function and i want to get the returning value in VB front end. Database connectivity is RDO

I know how to do this is by using ADO objects (ADODB.COMMAND)
But this system is using RDO method and RDO objects.

How can i do this by using RDO. Pls give some help

Calling Oracle Report Using VB6
Hi,

I just want to ask on how to call an oracle reports in VB6. Your help is highly appreciated.

Calling Oracle Script From Vb.net
How would you call or run an Oracle SQL script from vb.net?

For example I want to simply run this script from within a vb.net procedure:

bec_sbc_HDR.sql

create or replace force view bec_sbc_HDR
as
selectjbb_comp_code, jbb_cont_code, 'HDR' HDR
, to_char(JBB_PERIOD_ENDING_DATE, 'YYYYMMDD') RUN_DATE
,Rev_Letter Rev_Letter
,'Template Rev. Date 11/27/2002' Template_Rev
fromda.jbbill, da.jcjob_table, da.uetd_sbc_file
wherejob_comp_code = jbb_comp_code
andjob_code = jbb_job_code
andjob_comp_code = comp_code
andjob_default_dept_code = department;
--

Thanks,

Calling Oracle Procedure From Vb6
Hi All,

Here is my sample procedure

Create procedure date_value(diff in Number;
dt1 out varchar2, dt2 out varchar2)
as
begin
select to_char(sysdate - diff,'dd-Mon-yyyy')into dt1, to_char(sysdate + diff,'dd-Mon-yyyy')into dt2 from dual;
return;
end;

I need to call this procedure from VB. By passing the form value for diff and capture the two variables.

Here I mentioned a sample procedure but the actual procedure is also having the same return type.

Awaiting your response.

Regards,
Subbu S

VB Calling Oracle SP With Cursor
Hi All,
I have the following Oracle stored procedure, which called/executed from visual basic 6 using ADO.

I call it to retrive the data, it work fine with the cursor, bu when I try to executed (for save, update or/and delete) by usin ADO.command in the way mensioned down in VB it give error ( don't want to use the method of .append parmeters, because change my application from using MS-SQL server to Oracle).

Pls, I need someone to try this and help me.

Thanks to all with best regards,

Subair B.

This is the store procedure:-
CREATE OR REPLACE PROCEDURE SP_Departments
(
DepNo IN VARCHAR2 DEFAULT NULL,
DepName IN VARCHAR2 DEFAULT NULL,
Address IN VARCHAR2 DEFAULT NULL,
Operation IN VARCHAR2 DEFAULT NULL,
RCT1 IN OUT GLOBALPKG.RCT1
)
AS
ASSIGNMENTVARIABLE0 NUMBER(2,0) := 0;
BEGIN
IF Operation = 'Rtv' THEN

OPEN RCT1 FOR
SELECT *
FROM Departments
WHERE Departmentno = depNo;
ELSE
IF Operation = 'Save' THEN
BEGIN
SELECT 1 INTO ASSIGNMENTVARIABLE0 FROM DUAL WHERE EXISTS
(SELECT 1
FROM Departments
WHERE Departmentno = depNo
);
IF(ASSIGNMENTVARIABLE0 != 0 ) THEN

UPDATE Departments
SETDepartmentname = SP_Departments.DepName,
address = SP_Departments.address
WHERE Departmentno = SP_Departments.depNo;
ELSE

INSERT INTO Departments
( Departmentno ,
Departmentname ,
address )
VALUES ( SP_Departments.DepNo ,
SP_Departments.DepName ,
SP_Departments.Address);
END IF;
END;
ELSE
IF Operation = 'Delete' THEN

DELETE FROM Departments
WHERE Departmentno = SP_Departments.depNo;
ELSE
IF Operation = 'Print' THEN

OPEN RCT1 FOR
SELECT *
FROM Departments
ORDER BY departmentno;
END IF;
END IF;
END IF;
END IF;
END;

This is the Package:-
CREATE OR REPLACE PACKAGE GLOBALPKG
AS
TYPE RCT1 IS REF CURSOR;
IDENTITY INTEGER;
END;

This is the sql scrip to create the table:-
CREATE TABLE Departments
(
DepartmentNo VARCHAR2 (10) NOT NULL ,
DepartmentName VARCHAR2 (50) NOT NULL ,
Address VARCHAR2 (50) ,
Federal NUMBER(1) DEFAULT ( 0 ) NOT NULL ,
CONSTRAINT PK_Departments PRIMARY KEY (DepartmentNo)
)

And this is the coding in Visual Basic:-
Sub RetrieveDepartment()
Dim rsdata As ADODB.Recordset
'SQL SERVER
'sql = " EXEC SP_Departments '" & Trim(text1(0)) "','','','Rtv' "
'Set rsdata = New ADODB.Recordset
'ORACLE
Dim cm As ADODB.Command
Set cm = New ADODB.Command
cm.ActiveConnection = db
cm.CommandType = adCmdStoredProc
cm.CommandText = "SP_Departments ('" & Trim(text1(0)) "','','','Rtv','')"
Set rsdata = cm.Execute(, , adCmdStoredProc)

If Not rsdata.EOF Then
text1(1) = rsdata("DepartmentName")
text1(2) = IIf(IsNull(rsdata("Address")), "" rsdata("Address"))
Else
text1(1) = ""
text1(2) = ""
End If
rsdata.Close
Set rsdata = Nothing
End Sub

Private Sub SaveOrUpdateData()
Dim Str_DepNo$, Str_DepName$, Str_Address$
' Dim rsSave As ADODB.Recordset
Dim cm As ADODB.Command
Set cm = New ADODB.Command
Str_DepNo = text1(0)
Str_DepName = text1(1)
Str_Address = text1(2)
Dim x As Integer
'SQL SERVER
' sql = "exec SP_Departments '" & Str_DepNo & "','"
' sql = sql & Str_DepName & "','"
' sql = sql & Str_Address & "','Save'"
' Set rsSave = New ADODB.Recordset
' rsSave.Open sql, db, , , adCmdText
'ORACLE
sql = "SP_Departments('" & Str_DepNo & "','"
sql = sql & Str_DepName & "','"
sql = sql & Str_Address & "','Save','')"
'db.Execute sql, , a

Calling Oracle Report In Vb
hi all,
i have created a oracle report in oracle developer,there are two input parameters
for the report.i want to print this report from a vb form on its print button.
so how to call this report in vb with two input parameters.
pls suggest
manoj

Calling Oracle SQL* Loader From VB
Hi all!

I need to load a fixed length file from a CD-ROM (~300MB) into one Oracle table. I was thinking if there is a way to invoke Oracle SQL* Loader (an Oracle dll library). Any help (source code, examples, links) are welcome!

Thanks

Calling Oracle Stored Procedures
Any ideas on how to call Oracle Stored Procedures using ADO.

I need to open RecordSets using ADO. The recordsets would contain the OUT parameters of the Oracle Stored Procedures

Assume that I have opened an ODBC connection. How do I invoke the Stored Procedure.

"Every problem has a solution or atleast a workaround - "

Calling Oracle Stored Procedure From VB
Good Morning

Shopping for some SP help today.

I want to call an Oracle SP from VB passing 2 parameters and receiveing 1 back.

Can anyone help out or point me in the right direction?

Thanks,
Ed Dobias

Calling Oracle Stored Procedure From VB
Hi I have a problem calling an oracle stored procedure.
The following is my code segment:
-----------------
Public Function ExecStoredProc(mConnection As ADODB.Connection, _
Comp_Code As String, _
Doc_type As String, _
Dept_Code As String, _
Run_No As String, _
xlsEmpe_ID As String, _
xlsTrns_Grp As String, _
xlsTrns_Code As String, _
xlsUnit_Of_Mesr As String, _
xlsMthly_Pay_Perd As Integer, _
xlsPay_Mth As Integer, _
xlsPay_Yr As Integer, _
xlsPayr_Mode As String, _
xlsTrns_Date As Date, _
xlsAllw_Code As String, _
xlsAllw_Unit As Integer, _
xlsAllw_Val As Integer, _
xlsAllw_PC As Integer, _
xlsBasc_Yr As Integer, _
xlsBasc_Mth As Integer, _
xlsDate_Fr As Date, _
xlsDate_To As Date, _
xlsRec_Type As String) As String

On Error GoTo ErrorHandler:

Dim cmdSP As New ADODB.Command
'Dim strSQL As String

With cmdSP
.CommandText = "HR_CL_PAYR_ONE_TIME_N"
.CommandType = adCmdStoredProc
.ActiveConnection = mConnection
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '1
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '2
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '3
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '4
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '5
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '6
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '7
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '8
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '9
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '10
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '11
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '12
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '13
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '14
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '15
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '16
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '17
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '18
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '19
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '20
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '21
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '22
End With

'Assign Parameters
cmdSP(0) = Comp_Code
cmdSP(1) = Doc_type
cmdSP(2) = Dept_Code
cmdSP(3) = Run_No
cmdSP(4) = xlsEmpe_ID
cmdSP(5) = xlsTrns_Grp
cmdSP(6) = xlsTrns_Code
cmdSP(7) = xlsUnit_Of_Mesr
cmdSP(8) = xlsMthly_Pay_Perd 'Format(Now(), "mm/dd/yyyy")
cmdSP(9) = xlsPay_Mth
cmdSP(10) = xlsPay_Yr
cmdSP(11) = xlsPayr_Mode
cmdSP(12) = xlsTrns_Date
cmdSP(13) = xlsAllw_Code
cmdSP(14) = xlsAllw_Unit
cmdSP(15) = xlsAllw_Val
cmdSP(16) = xlsAllw_PC
cmdSP(17) = xlsBasc_Yr
cmdSP(18) = xlsBasc_Mth
cmdSP(19) = xlsDate_Fr
cmdSP(20) = xlsDate_To
cmdSP(21) = xlsRec_Type

'execute the query
Call MsgBox("Before executing")
Call cmdSP.Execute
cmdSP.Execute
Call MsgBox("executed")

Exit Function
ErrorHandler:
Debug.Print ("This * * * * SP can't work")
'Raise the error back to the calling procedure

End Function
-----------------
There is a Msgbox prompting me "Before Executing" but there is not msgbox prompting me "Executed"
And the debugger printed out "This * * * * SP can't work". after the "Before executing" msgbox popped out.
So there is an error in my execution statement? But why?

Any help will be greatly appreciated.

Calling Oracle 9i Stored Procedure
How I can call Oracle 9i stored procedure from Visual Basic? The procedures may have parameters. How to pass parameters?

Calling Oracle Stored Procedure
I have created an Oracle Stored Prodcure (it requires 21 input values) to store data into a processeing table. I'm doing this to try and speed up the processing of an application. I have to store anywhere from 1000 to 100000 records from a text file, into the DB for processing. The procedure complies correctly on the Oracle database but how do I call from VB6 (I am using ADO to connect to the db.)

The following is code I have done so far but I get an error with it:

VB Code:
'With Command to Stored ProcedureSet adoCommand = New ADODB.CommandWith adoCommand .ActiveConnection = mdlGeneral.gDBConn .CommandType = adCmdText .CommandText = "{call INS_TEMP_RECS(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}" .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , Trim(Replace(m_strPFor, "'", "''"))) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , strdtRepGen) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , Trim(Replace(Mid(m_strRepFromFile, 2), "'", "''"))) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , Trim(Replace(Mid(m_strTransID, 2), "'", "''"))) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , Trim(Replace(Mid(m_strUName, 2), "'", "''"))) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , Trim(Replace(Mid(m_strTestName, 2), "'", "''"))) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , strdtTestDefDate) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , strdtTestSchedDate) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , strdtTestStartDate) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , strdtTestEndDate) .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , m_lngNumPts) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , strdtSampDate) .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , strET) .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , strTemp) .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , strPSI) .Parameters.Append .CreateParameter(, adVarChar, adParamInput, , Trim(Replace(strFileNamed, "'", "''"))) .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , m_lngTransID) .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , m_lngWellID) .Parameters.Append .CreateParameter(, adNumeric, adParamInput, , strWaterLevel)End WithadoCommand.ExecuteSet adoCommand = Nothing

I get and error saying the parameters are not created correctly.

Any help or comments are very welcome.

Calling Oracle Stored Procs With VB-6 And ADO
Hi,

I am trying to call an Oracle stored proc using VB-6 and ADO but getting the following error.

ORA-06550:Line1, column 25
PLS-00201:identifier teamtable must be declared
ORA-06550:Line1, column 7
PL/SQL statement ignored
---------------------------------------------------

The code I am executing in VB-6 is as follows.

Dim mCnn As ADODB.Connection
Dim mrsSelect As ADODB.Recordset
Dim mCmd As ADODB.Command
Dim msSelect As String
Dim mCmdPrmGender As New ADODB.Parameter

Dim sConnect As String   'Declare our connection string

conn = "Provider=OraOLEDB.Oracle.1;Password=projman;Persist Security Info=True;User ID=projman;Data Source=projman"
  Set mCnn = New ADODB.Connection
  With mCnn
     .CommandTimeout = 10
     .CursorLocation = adUseClient
     .Open conn
  End With

msSelect = " {Call opms.get_team1(?, {resultset 1000, teamTable})}"

Set mCmd = New ADODB.Command

With mCmd
.CommandText = msSelect
.CommandType = adCmdText
.ActiveConnection = mCnn
.Parameters.Append .CreateParameter(, adVarChar, adParamInput, 50)
End With

Set mrsSelect = New ADODB.Recordset

With mrsSelect
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
End With

Set mrsSelect.Source = mCmd.Execute

MsgBox mrsSelect.Fields(0)

-------------------------------------------

The stored proc in ORacle (package OPMS) is as follows

procedure get_team1 (projid in varchar2, teamTable out teamtabletype) is

nCount binary_integer default 1;
v_projid       project_master.projid%type;
v_projname  project_master.name%type;
cursor c1 is
Select  e.name, r.description, t.charge_rate, t.comments
from team t, employee e, roles_lkup r
where t.projid = v_projid
and t.empid = e.empid
and t.roleid = r.roleid;
teamTableObj teamTableType;
begin
v_projid := projid;
Select name into v_projname
from project_master
where projid = v_projid;
for cursorloopcounter in c1 loop
teamtableObj(nCount) := cursorloopcounter;
nCount := nCount + 1;
end loop;
projname := v_projname;
teamtable := teamTableObj;
exception
when no_data_found then
    null;
end get_team1;

--------------------------------------------------
The type specification in the Oracle Package is declared as follows:

type teamRecType is record (
      empName      employee.name%type,
      roleDesc    roles_lkup.description%type,
      rate           team.charge_rate%type,
      comments    team.comments%type);

type teamTableType is table of documents%rowtype
      Index by binary_integer;

Please help. Thanks a lot

Calling Oracle Stored Procedure From VB
Hi I have a problem calling an oracle stored procedure.
The following is my code segment:
-----------------
Public Function ExecStoredProc(mConnection As ADODB.Connection, _
Comp_Code As String, _
Doc_type As String, _
Dept_Code As String, _
Run_No As String, _
xlsEmpe_ID As String, _
xlsTrns_Grp As String, _
xlsTrns_Code As String, _
xlsUnit_Of_Mesr As String, _
xlsMthly_Pay_Perd As Integer, _
xlsPay_Mth As Integer, _
xlsPay_Yr As Integer, _
xlsPayr_Mode As String, _
xlsTrns_Date As Date, _
xlsAllw_Code As String, _
xlsAllw_Unit As Integer, _
xlsAllw_Val As Integer, _
xlsAllw_PC As Integer, _
xlsBasc_Yr As Integer, _
xlsBasc_Mth As Integer, _
xlsDate_Fr As Date, _
xlsDate_To As Date, _
xlsRec_Type As String) As String

On Error GoTo ErrorHandler:

Dim cmdSP As New ADODB.Command
'Dim strSQL As String

With cmdSP
.CommandText = "HR_CL_PAYR_ONE_TIME_N"
.CommandType = adCmdStoredProc
.ActiveConnection = mConnection
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '1
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '2
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '3
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '4
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '5
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '6
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '7
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '8
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '9
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '10
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '11
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '12
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '13
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '14
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '15
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '16
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '17
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '18
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '19
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '20
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '21
.Parameters.Append .CreateParameter _
(, adChar, adParamInputOutput, 50) '22
End With

'Assign Parameters
cmdSP(0) = Comp_Code
cmdSP(1) = Doc_type
cmdSP(2) = Dept_Code
cmdSP(3) = Run_No
cmdSP(4) = xlsEmpe_ID
cmdSP(5) = xlsTrns_Grp
cmdSP(6) = xlsTrns_Code
cmdSP(7) = xlsUnit_Of_Mesr
cmdSP(8) = xlsMthly_Pay_Perd 'Format(Now(), "mm/dd/yyyy")
cmdSP(9) = xlsPay_Mth
cmdSP(10) = xlsPay_Yr
cmdSP(11) = xlsPayr_Mode
cmdSP(12) = xlsTrns_Date
cmdSP(13) = xlsAllw_Code
cmdSP(14) = xlsAllw_Unit
cmdSP(15) = xlsAllw_Val
cmdSP(16) = xlsAllw_PC
cmdSP(17) = xlsBasc_Yr
cmdSP(18) = xlsBasc_Mth
cmdSP(19) = xlsDate_Fr
cmdSP(20) = xlsDate_To
cmdSP(21) = xlsRec_Type

'execute the query
Call MsgBox("Before executing")
Call cmdSP.Execute
cmdSP.Execute
Call MsgBox("executed")

Exit Function
ErrorHandler:
Debug.Print ("This damn SP can't work")
'Raise the error back to the calling procedure

End Function
-----------------
There is a Msgbox prompting me "Before Executing" but there is not msgbox prompting me "Executed"
And the debugger printed out "This damn SP can't work". after the "Before executing" msgbox popped out.
So there is an error in my execution statement? But why?

Any help will be greatly appreciated.

Calling Oracle Stored Procedure In Vb
I have create an Oracle stored Procedure which have two in parameter and two out parameter .I want to know how can i call that procedure through VB application.

Vb Calling Oracle Stored Procedure
i am using vb6 and oracle 8i. i am calling stored procedure on oracle from VB.
in the stored procedure i have inserted some rows in the table for temporary calculation.
the data is automatically committed in the database when the stored procedure is called from VB.
how can i prevent it.
thanks,
naresh

Calling Oracle Stored Procs From Vb
Is there any limitation about the length of the characters of the oracle stored procedures that are called from VB using DAOs?
I have come across a strange problem wherein the stored procedures that are more than 32 characters long are failing while called from VB. This problem is not consistant. It occurs sometimes and vanishes after some time without any changes.
Please help.

Calling Store Procedure In Oracle With ADO
I want to get a recoreset from SP in oracle.

the code of store precedure is listed as below:
---------------------------------------------
CREATE OR REPLACE PROCEDURE SP_TEST
(
ret_cur out DEFPKG.EMPTY_CUR
)
IS
BEGIN
OPEN RET_CUR FOR Select * FROM mytable;
END SP_TEST;
-------------------------------------------------
the type "DEFPKG.EMPTY_CUR" is defined in package "DEFPKG"
TYPE EMPTY_CUR IS REF CURSOR;

And the calling codes is:
---------------------------------------------------------
.....
QSQL = "{call SP_TEST({resultset 1000,ret_cur})}"

Set CPw = New ADODB.Command
With CPw
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With

Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With

Rs = CPw.Execute 'Error occurs
--------------------------------------

when running to the last line,system report ORA-01060 error.
(array binds or executes not allowed).
Oracle document tell me that this is because "The client application attempted to bind an array of cursors or attempted to repeatedly execute against a PL/SQL block with a bind variable of type cursor".
But I donn't know how to solve it.

Thanks a lot.

Calling Oracle Stored Proc
Hi,
I'd like to make a call to an Oracle stored proc. My requirement is pretty simple. Somehow, I need to pass a 2 dimensional array to the stored proc and the stored proc should read it and insert the records into the Oracle tables.
Can anybody help me on this??? I had already asked this question, but it looks like we are short of VB/Oracle Experts...

Ansuman

Calling Oracle Stored Proc
Hi,
I'm trying to call some oracle stored procedures from VB, but I get the ODBC failed error all the time. Could someone please let me know how to call oracle stored procedures from VB using DAO. I've to use DAO because I've VB 5.0 professional edition. The Oracle store proc should be able to return multiple records from a table. I'd appreciate if anyone can send me some existing codes maybe or else pointer to something similar.

Thanks in advance
Ansuman

Calling A CrystalReport From VB To Read A Oracle Database
Hi all,

I have a VB application that executes a Crystal Report that reads an Oracle
database, I'm using the Crystal Report control to execute that ...
but ... I retrieve this error ... a window with the title: "Seagate Crystal
Reports: Database Error" and "Run-time error 20599 (Cannot open SQL server)"

In order to execute the report I'm doing this ...

cr1.Action = 1

somebody can give the code to do this ????

Thanks in advance
Abilio

Calling An Store Procedure In Oracle From VB Code
Hi I would like to parse an sp_procedure in Oracle with VB. Can Anyone show me how to do so?
In fact, my sp list data. Although in SQL SERVER the connection is pretty simple , I do not know how to call the
ORACLE procedure in VB
/*-------------------------*/
sql> CREATE OR REPLACE PROCEDURE stored_procedure_name
  2 (p_result OUT SYS_REFCURSOR)
  3 AS
  4 BEGIN
  5 OPEN p_result FOR SELECT * FROM dept;
  6 END stored_procedure_name;
  7 /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> VARIABLE g_ref REFCURSOR
scott@ORA92> EXECUTE stored_procedure_name (:g_ref)

PL/SQL procedure successfully completed.

/*-------------------------*/
VB Code
/*-------------------------*/
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command

Private Sub cmdCallprocedure_Click()
    On Error GoTo cancel
    Dim strcallproc As String
    strcallproc = "{ call stored_procedure_name(?)}"

    cmd.CommandText = strcallproc
    cmd.CommandType = adCmdUnknown
    cmd.ActiveConnection = cn

    Set p1 = cmd.CreateParameter("a", adUserDefined, adParamOutput)

    cmd.Parameters.Append p1
    cmd.Execute

    'After everything is done...clear all content to ready for using next time
    Set cmd = Nothing
    'Set p1 = Nothing
    'Set p2 = Nothing
    'Set p3 = Nothing
    Exit Sub
cancel:
    MsgBox Err.Description
End Sub

/*-------------------------*/

Any insight is strongly appreciated

Calling Oracle Stored Procedure With Table Datatype
HI,
Following is the Oracle Storeprocedure where MYTABLETYPE is

create or replace type myTableType as table
of varchar2 (255);

create or replace procedure usp_cusip(IN_cusip IN MYTABLETYPE)
IS
BEGIN
NULL;
END;

is it possible to call this storeprocedure from VB, If yes how to pass the input value..

Calling Oracle Stored Procedure Output Is A Ref Cursor.
I am trying to execute a oracle stored procedure from Vbt6 using Ado.
the stored produre take two input parameters and returns a ref cursor
PROCEDURE EDI820_SUMMARY(pJobId IN varchar2, pcheckid In varchar2, p_Summrecd out ref_items)
I would like to retrive the ref cursor to a vb recordset object using ADO.
Please help me out with this,
Thanks,
VS

VB6 Memory "leak" When Calling An Oracle Stored Procedure?
Hey,

I would appreciate some help on this one, if possible...

When I call an Oracle 9i stored procedure that returns a REF CURSOR from VB6 I get what seems a memory "leak". Memory is allocated by the .exe to hold the recordset but is not immediately released when the recordset is closed, as expected. Give the machine a half hour or so and the memory alloacted to the exe will slowly drop to normal. However, I require immediate memory deallocation as we're talking about big recordsets of 100s of Megs. Interestingly, if I don't use the Stored proc and query the table directly using a "conn.Execute" then memory will be allocated and then instantly deallocated as per normal.

-------------------------------------------------------------------------------

---Stored proc (returns a large recordset)---

TYPE csResultSet IS REF CURSOR;

PROCEDURE Test (outCursor OUT csResultSet)
IS
BEGIN
OPEN outCursor FOR ('SELECT * FROM X');
END Test;

--- VB calling code ---

Private Sub Command1_Click()
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
With conn
.Mode = adModeShareDenyNone
.CursorLocation = adUseClient
.ConnectionString = "Provider=MSDAORA.1;User ID=/; Password=; Data Source = xxx"
.Open
End With

Set rs = conn.Execute("Test")

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub

-------------------------------------------------------------------------------

I hope someone can give us a clue to what is going on because I'm going around the twist even more than I normally am,

cheers,
James

Problem Connecting To An Oracle 9i Database With The Oracle ODBC Driver
I have this older VB6 application that I don't have the time to convert to VS2005 right now. I have the Oracle ODBC client installed and the DSN are created as system DSN's. They are not using the "Microsoft ODBC for Oracle" driver, instead they are using the actual Oracle driver. Ive tried several connection strings including:

Code:
cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"

cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN

cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"

cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"

I keep getting the error handler from my function:

Code:
Public Function OpenDatabaseConnection() As Boolean
On Error GoTo errhandler
Dim connectionstring As String

    Set cn = New ADODB.Connection
    cn.Provider = "MSDAORA.1"
    'cn.connectionstring = "Provider=OraOLEDB.Oracle.1;Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
    'cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
    'cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN
    'cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"
    cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"
    cn.Open
    OpenDatabaseConnection = True
    Exit Function
errhandler:
    MsgBox "Connection failed, Enter Login Parameters Again", vbCritical
    OpenDatabaseConnection = False
    Exit Function
End Function

...any ideas on the correct connection string?

Package & Deployment Hangs At Loading Package Types
Using VB6 on Vista. I haven't used my package and deployment for a while but today I recompiled my application and tried to ceate a new package for deployment. It got as far as "loading package types..." and quit. I reinstalled the program a couple of times without success. I noticed at the top of my 'Add-in Manager' there is an item that says "<MISSING>AFVBAddinConnect" if that makes any difference. Hopefully, someone has a solution or reason why this is occurring. Thanks.

Jim S.

Edited by - jschuchert on 8/17/2008 7:05:58 AM

How To Connection To Oracle Dataase Using Oracle 9.2 ODBC Driver
I try to connect to the Oracle database using the Oracle in OraHome92 driver, the code is:

Private Sub ConnectDB()
   Dim sConn As String
   Dim DB As String

   Select Case cboEnviro.Text
      Case "Production"
         DB = "PROD"
      Case "Testing"
         DB = "TEST"
   End Select

   sConn = "Driver=(Oracle in OraHome92);" & "Data Source=" & DB & _
                ";Uid=userid;Pwd=password;"
   dbConn.Open sConn
End Sub

I got the following error message:

Run-time error '2147467259 (80004005)':
[Oracle][ODBC][Ora]ORA12560: TNSrotocol adapter error

Can anyone tell me what wrong with this and how to fix the problem. Thanks.

Connection To An Oracle 9i Database With The Oracle ODBC Driver
I have this older VB6 application that I don't have the time to convert to VS2005 right now. I have the Oracle ODBC client installed and the DSN are created as system DSN's. They are not using the "Microsoft ODBC for Oracle" driver, instead they are using the actual Oracle driver. Ive tried several connection strings including:

Code:

cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"

cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN

cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"

cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"

I keep getting the error handler from my function:

Code:

Public Function OpenDatabaseConnection() As Boolean
On Error GoTo errhandler
Dim connectionstring As String

Set cn = New ADODB.Connection
cn.Provider = "MSDAORA.1"
'cn.connectionstring = "Provider=OraOLEDB.Oracle.1;Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
'cn.connectionstring = "Password=gers4us;Persist Security Info=True;User ID=gers;Data Source=NUREV"
'cn.connectionstring = "Password=" & gstrPassword & ";Persist Security Info=True;User ID=" & gstrUser & ";Data Source=" & gstrDSN
'cn.connectionstring = "Driver={Microsoft ODBC for Oracle};" & "UID=report;PWD=reportpw;DSN=nurev"
cn.connectionstring = "Driver={Microsoft ODBC for Oracle};Server=nurev;Uid=report;Pwd=reportpw;"
cn.Open
OpenDatabaseConnection = True
Exit Function
errhandler:
MsgBox "Connection failed, Enter Login Parameters Again", vbCritical
OpenDatabaseConnection = False
Exit Function
End Function

...any ideas on the correct connection string?

Package And Deployment Wizard Not Generating Package
Hi,

I am trying to generate a package, for a Visual Basic application I wrote, through the Package and Deployment Wizard utility. PDW seems to work great until it gets to the last step. When I click the "Finish" button, instead of generating the package, with its setup and library files, it bombs out/exits without giving any clue/error message why.

I would appreciate any help you could give me on that.

Thanks,

TM

Package & Deployment No Error But Package Not Create
I tried to package my vb program but after i have completed the package and deployment wizard, the wizard just exited without creating the installation package for me. I have check the folder, it only create the setup folder and support folder without files. please advise what could be wrong.

I have tried using the package and deployment for another VB Project without any problem.

Package 'cannot Find File' - L:Package\@test.exe
Hi,

I created a package for my software (written in VB6), but when I try to install, it tells me that a file: L:Package\@test.exe cannot be found (L:Package is the location of the package). Test.exe is supposed to be built into the package, but I get this error. Also, when it tries to remove the installed files, it just gets stuck and crashes.

Anyone got any ideas? - a quick reply would be greatly appreciated.

How Can I Change The Package Size Of Winsock Package.
I have a satellital connection. That´s requieres one maximun package size, the server send a notification of this to the client, but the client forget this.
I need read this notificartion and change the maximun package size!!

TIA

VB Connectivity To Oracle DSN Using Oracle In OraHome92 Driver
Hi,

I have a desktop VB application which connects to an Oracle 9i database. Uptill now it has been using the Microsoft ODBC for Oracle driver (MSORCL32.DLL) to connect to the Oracle DSNs. Now I wish to change over to the "Oracle in OraHome92" driver (SQORA32.DLL).

When the above change is made, the application connects fine and the select queries (simple fetches without aggregate functions) execute alright. However I am facing issues, when I try to execute complex SQL statements such as statements invoking stored procedures and returning recordsets.
eg.
Code:
{ call wPHARMACY_VALIDATIONS.pwPHARMACY_VALIDATIONS(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, {RESULTSET 1, o_validation_error_codes}) }

This gives an oracle error "o_validation_error_codes needs to be defined". This error was not encountered with the Microsoft ODBC driver for Oracle previously.

Also, we are facing issues with executing stored procedures directly using ADODB command object
The error in this case is "-2147217887 Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done".

If anybody has worked on something similar or can give pointers on the same, and similar such issues that we might encounter because of the driver change, please reply.

Thanks & Regards,
V!kram