Solved

vb.net call oracle package

Posted on 2013-01-11
3
741 Views
Last Modified: 2013-01-11
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
0
Comment
Question by:SimonPrice33
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 30

Accepted Solution

by:
Brad Howe earned 500 total points
ID: 38766570
You package is called SP_RESETUSER and not SP_RESTUSER

.CommandText = "PKG_SYNCHRONIZATION.SP_RESTUSER"

Cheers,
Hades666
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 38766574
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
0
 

Author Closing Comment

by:SimonPrice33
ID: 38766588
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.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question