asked on

vb.net call oracle package

Hi Experts

I am trying to call a package within an oracle database from vb.net

The code I am using is

 Try
            Dim userID As New OracleParameter("vELINK_USER_ID", OracleType.Number)
            userID.Value = "20092"
            Dim objectID As New OracleParameter("vOBJECT_TYPE_ID", OracleType.Number)

            Dim resetsync As New OracleCommand
            With resetsync
                .Connection = sqlconn
                .CommandText = "PKG_SYNCHRONIZATION.SP_RESTUSER"
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add(userID)
            End With
            sqlconn.ConnectionString = connstr
            sqlconn.Open()
            resetsync.ExecuteNonQuery()
            sqlconn.Close()
        Catch ex As Exception
            sqlconn.Close()
            MsgBox(ex.ToString)
        End Try

Open in new window

However I get the following error

ORA-06550: line 1, column 27:
PLS-00302: component 'SP_RESTUSER' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

The package within Oracle is as follows

/* Removes users sync log records to allow reset and re-sync of all records,
   if passed an object type then only that type gets reset */
PROCEDURE SP_RESETUSER(
	vELINK_USER_ID	NUMBER,
	vOBJECT_TYPE_ID NUMBER DEFAULT NULL)
AS
BEGIN

	DELETE MOBILE_SYNC_LOG
	WHERE ELINK_USER_ID = vELINK_USER_ID AND
	(vOBJECT_TYPE_ID IS NULL OR SYNC_OBJECT_TYPE_ID =vOBJECT_TYPE_ID);

END SP_RESETUSER;

Open in new window

the user does have permission to execute commands so do not believe this to be the issue and would be grateful for any help that could get me around this issue.

thanks

Simon

ASKER CERTIFIED SOLUTION

Brad Howe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Praveen Kumar Chandrashekatr

its an privilege issues only the user should have been given the execute permission.

check this below doc for more info

https://forums.oracle.com/forums/thread.jspa?threadID=894690

http://www.dba-oracle.com/t_pls_00302.htm

SimonPrice33

ASKER

I believe we would call this not seeing the the wood through the trees.....

thank you...

I had just got it to work using

sqlconn.ConnectionString = connstr
sqlconn.Open()
sqlcmd.Connection = sqlconn
sqlcmd.CommandText = "BEGIN DELETE MOBILE_SYNC_LOG WHERE ELINK_USER_ID = '" & txtUserNo.Text & "'; END;"
sqlcmd.ExecuteNonQuery()
sqlconn.Close()

but am grateful that you were abble to spot this as I much prefer to use the pkg and manage this rather than alter the code each time i need to make a change.