Erro calling stored proceedure

P

Problematic coder

The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.

Here is the code I am using:

Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()


Here is the error I get:

System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350
 
L

Lloyd Sheen

Problematic coder said:
The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.

Here is the code I am using:

Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()


Here is the error I get:

System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350

Perhaps the schema name??

LS
 
G

Guest

The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.

Here is the code I am using:

Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()

Here is the error I get:

System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Check if your user can call that procedure
 
?

=?ISO-8859-1?Q?G=F6ran_Andersson?=

Problematic said:
The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.

Here is the code I am using:

Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()


Here is the error I get:

System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350

The name of the stored procedure is not recognised.

Check the spelling. Check that you are connecting to the correct
database. Check that the stored procedure is actually there.
 
P

Problematic coder

The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.

Here is the code I am using:

Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()

Here is the error I get:

System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Metho­d
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350

OK I did overlook granting execute to to the web application userid,
that is now granted but still did not fix the problem.

I still get the exact same error

The connection objcnn is fine because I do inserts with it prior to
attempting to execute this.

The error message seems to make reference to line 1, column 7 - i
assume this is in the stored procedure, here are the first few lines :

CREATE OR REPLACE
PROCEDURE set_status_and_flags
AS
v_something varchar2(9);
v_somethingelse varchar2(3);
BEGIN

It executes fine and does everything expected when exected via Oracle
SQL developer

Another quick test I ran was changing the name of the procedure in the
vb.net call to one I knew did not exist and I still got the same error
except with the incorrect name, so I would say you are on the right
line that it cannot find the procedure but I have no clue why, it is
there!

Thanks
 
G

Guest

The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.
Here is the code I am using:
Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()
Here is the error I get:
System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Metho­­d
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350

OK I did overlook granting execute to to the web application userid,
that is now granted but still did not fix the problem.

I still get the exact same error

The connection objcnn is fine because I do inserts with it prior to
attempting to execute this.

The error message seems to make reference to line 1, column 7 - i
assume this is in the stored procedure, here are the first few lines :

CREATE OR REPLACE
PROCEDURE set_status_and_flags
AS
v_something varchar2(9);
v_somethingelse varchar2(3);
BEGIN

It executes fine and does everything expected when exected via Oracle
SQL developer

Another quick test I ran was changing the name of the procedure in the
vb.net call to one I knew did not exist and I still got the same error
except with the incorrect name, so I would say you are on the right
line that it cannot find the procedure but I have no clue why, it is
there!

Thanks- Hide quoted text -

- Show quoted text -

Well, I'm not sure but there did you specify the parameters:

v_something varchar2(9);
v_somethingelse varchar2(3);

Can it be the reason?
 
P

Problematic coder

The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.
Here is the code I am using:
Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()
Here is the error I get:
System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Metho­­­d
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350
OK I did overlook granting execute to to the web application userid,
that is now granted but still did not fix the problem.
I still get the exact same error
The connection objcnn is fine because I do inserts with it prior to
attempting to execute this.
The error message seems to make reference to line 1, column 7 - i
assume this is in the stored procedure, here are the first few lines :
CREATE OR REPLACE
PROCEDURE set_status_and_flags
AS
v_something varchar2(9);
v_somethingelse varchar2(3);
BEGIN
It executes fine and does everything expected when exected via Oracle
SQL developer
Another quick test I ran was changing the name of the procedure in the
vb.net call to one I knew did not exist and I still got the same error
except with the incorrect name, so I would say you are on the right
line that it cannot find the procedure but I have no clue why, it is
there!
Thanks- Hide quoted text -
- Show quoted text -

Well, I'm not sure but there did you specify the parameters:

v_something varchar2(9);
v_somethingelse varchar2(3);

Can it be the reason?- Hide quoted text -

- Show quoted text -

Those are variables that get assigned a value further into the PL SQL
like so:

BEGIN
FOR rec IN (SELECT A.THISCOLUMN,
A.THATCOLUMN
FROM MYDATABASE_ADM.MYTABLE A
WHERE
v_something := rec.THISCOLUMN;
v_somethingelse := rec.THATCOLUMN;

etc.

So it is not expecting these values as parameters
If I execute this manually on the database no parameters are required
and it does everything as expected.

I did notice however that if I call an incorrect proceedure name I get
exactly the same error.
 
G

Guest

The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.
Here is the code I am using:
Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()
Here is the error I get:
System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Metho­­­­d
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350
OK I did overlook granting execute to to the web application userid,
that is now granted but still did not fix the problem.
I still get the exact same error
The connection objcnn is fine because I do inserts with it prior to
attempting to execute this.
The error message seems to make reference to line 1, column 7 - i
assume this is in the stored procedure, here are the first few lines :
CREATE OR REPLACE
PROCEDURE set_status_and_flags
AS
v_something varchar2(9);
v_somethingelse varchar2(3);
BEGIN
It executes fine and does everything expected when exected via Oracle
SQL developer
Another quick test I ran was changing the name of the procedure in the
vb.net call to one I knew did not exist and I still got the same error
except with the incorrect name, so I would say you are on the right
line that it cannot find the procedure but I have no clue why, it is
there!
Thanks- Hide quoted text -
- Show quoted text -
Well, I'm not sure but there did you specify the parameters:
v_something varchar2(9);
v_somethingelse varchar2(3);
Can it be the reason?- Hide quoted text -
- Show quoted text -

Those are variables that get assigned a value further into the PL SQL
like so:

BEGIN
FOR rec IN (SELECT A.THISCOLUMN,
A.THATCOLUMN
FROM MYDATABASE_ADM.MYTABLE A
WHERE
v_something := rec.THISCOLUMN;
v_somethingelse := rec.THATCOLUMN;

etc.

So it is not expecting these values as parameters
If I execute this manually on the database no parameters are required
and it does everything as expected.

I did notice however that if I call an incorrect proceedure name I get
exactly the same error.- Hide quoted text -

- Show quoted text -

http://www.dbasupport.com/forums/showthread.php?t=37474
 
?

=?ISO-8859-1?Q?G=F6ran_Andersson?=

Problematic said:
The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.

Here is the code I am using:

Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()

Here is the error I get:

System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Metho­d
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350

OK I did overlook granting execute to to the web application userid,
that is now granted but still did not fix the problem.

I still get the exact same error

The connection objcnn is fine because I do inserts with it prior to
attempting to execute this.

The error message seems to make reference to line 1, column 7 - i
assume this is in the stored procedure, here are the first few lines :

CREATE OR REPLACE
PROCEDURE set_status_and_flags
AS
v_something varchar2(9);
v_somethingelse varchar2(3);
BEGIN

It executes fine and does everything expected when exected via Oracle
SQL developer

Another quick test I ran was changing the name of the procedure in the
vb.net call to one I knew did not exist and I still got the same error
except with the incorrect name, so I would say you are on the right
line that it cannot find the procedure but I have no clue why, it is
there!

Thanks

You can't execute the stored procedure set_status_and_flags by
specifying "MYPROC" as command text.
 
P

Problematic coder

Problematic said:
The intention is to call a stored proceedure which sets flags in the
database, this requires no parameters and the page does not need an
output from the stored proceedure, though is this is necessary I could
change that.
Here is the code I am using:
Dim objcom2 = New Data.OracleClient.OracleCommand
With objcom2
.Connection = objcnn
.CommandTimeout = 60
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "MYPROC"
End With
objcom2.ExecuteNonQuery()
Here is the error I get:
System.Data.OracleClient.OracleException was caught
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'MYPROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="Microsoft.VisualBasic"
StackTrace:
at
Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Metho­­d
TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags
Flags)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container
BaseReference, String MethodName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags
InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object
Instance, Type Type, String MemberName, Object[] Arguments, String[]
ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean
IgnoreReturn)
at internal_processing.btnSubmit_Click(Object sender, EventArgs
e) in C:\Documents and Settings\uid\My Documents\Visual Studio
2005\WebSites\.......:line 350
OK I did overlook granting execute to to the web application userid,
that is now granted but still did not fix the problem.
I still get the exact same error
The connection objcnn is fine because I do inserts with it prior to
attempting to execute this.
The error message seems to make reference to line 1, column 7 - i
assume this is in the stored procedure, here are the first few lines :
CREATE OR REPLACE
PROCEDURE set_status_and_flags
AS
v_something varchar2(9);
v_somethingelse varchar2(3);
BEGIN
It executes fine and does everything expected when exected via Oracle
SQL developer
Another quick test I ran was changing the name of the procedure in the
vb.net call to one I knew did not exist and I still got the same error
except with the incorrect name, so I would say you are on the right
line that it cannot find the procedure but I have no clue why, it is
there!

You can't execute the stored procedure set_status_and_flags by
specifying "MYPROC" as command text.

--
Göran Andersson
_____http://www.guffa.com- Hide quoted text -

- Show quoted text -

Thanks, though that was deliberately changed for posting publicly,
just forgot the next post.

Anyway I have finally resolved it, everybody was on the right track
especially the first poster, I don't know how I missed it since I do
this all the time:

I forgot to specify mydatabaseusername.myproc and since there was no
alias it could not see it

I was looking for a more complicated answer, oddly it rarely is
whenever you get totally stuck

Thank you everyone for your suggesions, none were inaccurate, I was
just a bit slow running with them
Have a great day!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,764
Messages
2,569,566
Members
45,041
Latest member
RomeoFarnh

Latest Threads

Top