Erro calling stored proceedure

Discussion in 'ASP .Net' started by Problematic coder, Mar 13, 2007.

  1. 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
     
    Problematic coder, Mar 13, 2007
    #1
    1. Advertising

  2. Problematic coder

    Lloyd Sheen Guest

    "Problematic coder" <> wrote in message
    news:...
    > 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
     
    Lloyd Sheen, Mar 13, 2007
    #2
    1. Advertising

  3. On Mar 13, 7:03 pm, "Problematic coder" <> wrote:
    > 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
     
    Alexey Smirnov, Mar 13, 2007
    #3
  4. Problematic coder wrote:
    > 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.

    --
    Göran Andersson
    _____
    http://www.guffa.com
     
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Mar 13, 2007
    #4
  5. On Mar 13, 11:03 am, "Problematic coder" <> wrote:
    > 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
     
    Problematic coder, Mar 13, 2007
    #5
  6. On Mar 13, 9:49 pm, "Problematic coder" <> wrote:
    > On Mar 13, 11:03 am, "Problematic coder" <> wrote:
    >
    >
    >
    >
    >
    > > 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?
     
    Alexey Smirnov, Mar 13, 2007
    #6
  7. On Mar 13, 1:57 pm, "Alexey Smirnov" <> wrote:
    > On Mar 13, 9:49 pm, "Problematic coder" <> wrote:
    >
    >
    >
    >
    >
    > > On Mar 13, 11:03 am, "Problematic coder" <> wrote:

    >
    > > > 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.
     
    Problematic coder, Mar 13, 2007
    #7
  8. On Mar 13, 10:15 pm, "Problematic coder" <> wrote:
    > On Mar 13, 1:57 pm, "Alexey Smirnov" <> wrote:
    >
    >
    >
    >
    >
    > > On Mar 13, 9:49 pm, "Problematic coder" <> wrote:

    >
    > > > On Mar 13, 11:03 am, "Problematic coder" <> wrote:

    >
    > > > > 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
     
    Alexey Smirnov, Mar 13, 2007
    #8
  9. Problematic coder wrote:
    > On Mar 13, 11:03 am, "Problematic coder" <> wrote:
    >> 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.

    --
    Göran Andersson
    _____
    http://www.guffa.com
     
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Mar 13, 2007
    #9
  10. On Mar 13, 3:16 pm, Göran Andersson <> wrote:
    > Problematic coder wrote:
    > > On Mar 13, 11:03 am, "Problematic coder" <> wrote:
    > >> 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.
    >
    > --
    > 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!
     
    Problematic coder, Mar 13, 2007
    #10
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    311
  2. Problematic coder

    Call Oracle Stored Proceedure VB.NET

    Problematic coder, Mar 8, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    459
    Problematic coder
    Mar 9, 2007
  3. Problematic coder

    Call Oracle Stored Proceedure VB.NET

    Problematic coder, Mar 8, 2007, in forum: ASP .Net
    Replies:
    0
    Views:
    339
    Problematic coder
    Mar 8, 2007
  4. Mick Walker
    Replies:
    4
    Views:
    414
    Lloyd Sheen
    Jun 20, 2007
  5. calling a proceedure

    , Mar 23, 2007, in forum: ASP General
    Replies:
    0
    Views:
    119
Loading...

Share This Page