If anybody has any ideas I'd sure appreciate some help. I'm including code that is in form1 and the code for the stored procedures/package/table. This code is from microsofts site.
The error I'm getting is:
run-time error'-21564645(1654e65)':
ORA-06550:line 1, column 28:
PLS-00201:identifer 'SSN' must be declared
ORA-06550:line1, column 7:
PL/SQL: Statement ignored
Form1:
Option Compare Database
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim CPw2 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long
Private Sub cmdGetEveryone_Click()
Set Rs.Source = CPw1
Rs.Open
While Not Rs.EOF
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.MoveNext
Wend
Rs.Close
End Sub
Private Sub cmdGetOne_Click()
On Error Resume Next
Set Rs.Source = CPw2
inputssn = InputBox("Enter the SSN you wish to retrieve:")
CPw2(0) = inputssn
Rs.Open
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.Close
End Sub
Private Sub Form_Load()
Conn = "Provider=OraOLEDB.Oracle;Data Source=Mosaics;" & _
"User Id=SFDCEIM;" & _
"Password=SFDCEIM"
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _
& "lname})}"
Set CPw1 = New ADODB.Command
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With
QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _
& "lname})}"
Set CPw2 = New ADODB.Command
With CPw2
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adInteger, adParamInput)
End With
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set CPw2 = Nothing
Set Rs = Nothing
End Sub
referenced table code:
DROP TABLE person;
CREATE TABLE person
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));
INSERT INTO person VALUES(555662222,'Sam','Goodwin');
INSERT INTO person VALUES(555882222,'Kent','Clark');
INSERT INTO person VALUES(666223333,'Jane','Doe');
COMMIT;
/
package code:
CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER;
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
body code:
CREATE OR REPLACE PACKAGE BODY packperson
AS
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
/
The error I'm getting is:
run-time error'-21564645(1654e65)':
ORA-06550:line 1, column 28:
PLS-00201:identifer 'SSN' must be declared
ORA-06550:line1, column 7:
PL/SQL: Statement ignored
Form1:
Option Compare Database
Option Explicit
Dim Cn As ADODB.Connection
Dim CPw1 As ADODB.Command
Dim CPw2 As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Conn As String
Dim QSQL As String
Dim inputssn As Long
Private Sub cmdGetEveryone_Click()
Set Rs.Source = CPw1
Rs.Open
While Not Rs.EOF
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.MoveNext
Wend
Rs.Close
End Sub
Private Sub cmdGetOne_Click()
On Error Resume Next
Set Rs.Source = CPw2
inputssn = InputBox("Enter the SSN you wish to retrieve:")
CPw2(0) = inputssn
Rs.Open
MsgBox "Person data: " & Rs(0) & ", " & Rs(1) & ", " & Rs(2)
Rs.Close
End Sub
Private Sub Form_Load()
Conn = "Provider=OraOLEDB.Oracle;Data Source=Mosaics;" & _
"User Id=SFDCEIM;" & _
"Password=SFDCEIM"
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
QSQL = "{call packperson.allperson({resultset 9, ssn, fname, " _
& "lname})}"
Set CPw1 = New ADODB.Command
With CPw1
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
End With
QSQL = "{call packperson.oneperson(?,{resultset 2, ssn, fname, " _
& "lname})}"
Set CPw2 = New ADODB.Command
With CPw2
Set .ActiveConnection = Cn
.CommandText = QSQL
.CommandType = adCmdText
.Parameters.Append .CreateParameter(, adInteger, adParamInput)
End With
Set Rs = New ADODB.Recordset
With Rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cn.Close
Set Cn = Nothing
Set CPw1 = Nothing
Set CPw2 = Nothing
Set Rs = Nothing
End Sub
referenced table code:
DROP TABLE person;
CREATE TABLE person
(ssn NUMBER(9) PRIMARY KEY,
fname VARCHAR2(15),
lname VARCHAR2(20));
INSERT INTO person VALUES(555662222,'Sam','Goodwin');
INSERT INTO person VALUES(555882222,'Kent','Clark');
INSERT INTO person VALUES(666223333,'Jane','Doe');
COMMIT;
/
package code:
CREATE OR REPLACE PACKAGE packperson
AS
TYPE tssn is TABLE of NUMBER(10)
INDEX BY BINARY_INTEGER;
TYPE tfname is TABLE of VARCHAR2(15)
INDEX BY BINARY_INTEGER;
TYPE tlname is TABLE of VARCHAR2(20)
INDEX BY BINARY_INTEGER;
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname);
END packperson;
/
body code:
CREATE OR REPLACE PACKAGE BODY packperson
AS
PROCEDURE allperson
(ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
PROCEDURE oneperson
(onessn IN NUMBER,
ssn OUT tssn,
fname OUT tfname,
lname OUT tlname)
IS
CURSOR person_cur IS
SELECT ssn, fname, lname
FROM person
WHERE ssn = onessn;
percount NUMBER DEFAULT 1;
BEGIN
FOR singleperson IN person_cur
LOOP
ssn(percount) := singleperson.ssn;
fname(percount) := singleperson.fname;
lname(percount) := singleperson.lname;
percount := percount + 1;
END LOOP;
END;
END;
/