locked
ORA-06550: line 1, column 37: PLS-00201: identifier 'XYZ' must be declared RRS feed

  • Question

  • User-111119262 posted

    HI,

    i have getting this error when i call PL/SQL stored procedure.

    i use OraOLEDB.Oracle.1 provider to connect my VS 2010 Application to oracle database.

    i am working on windows 64bit OS and use 11g 64bit client.

    i am also try to change PlSQLREset  to 1 (true) in registry but not work

    Please help

    Many Thanks in Advance.

    Tuesday, September 4, 2012 12:57 AM

All replies

  • User269602965 posted

    Show the code calling the stored procedure

    Show the stored procedure

    Then forum users can help better

    Tuesday, September 4, 2012 10:06 AM
  • User-111119262 posted

    Hi lannie,

    please find my code and SP below.

    Code:

    Oraclecon =New OleDbConnection(strDBConfig)
    Oraclecon.Open()
    Dim myCMD As New OleDbCommand("{ call RR0001.RR0091_01(?,{resultset 500,out_dc_cus_type_id,out_type_name,out_default_challenge,out_user_defined_challenge,out_return_status,out_return_msg})}", Oraclecon)
    myCMD.CommandTimeout =CInt(strCommandTimeout)
    myCMD.Parameters.Add("inp_dc_cus_type_id", OleDbType.Numeric).Value = v_in_cus_type_id
    objReader = myCMD.ExecuteReader

    SP: in my first procedure i call plsql sp:-

    procedure RR0091_01 (
          inp_dc_cus_type_id          in       number,
          out_dc_cus_type_id          OUT      d0000.dt_dc_cus_type_id,
          out_type_name               OUT      d0000.dt_type_name,
          out_default_challenge       OUT      d0000.dt_default_challenge,
          out_user_defined_challenge  OUT      d0000.dt_user_defined_challenge,
          out_return_status           OUT      d0000.dt_return_status,
          out_return_msg              OUT      d0000.dt_return_msg
       ) is
        Begin
        d0000.d0046_01 (
          inp_dc_cus_type_id        ,
          out_dc_cus_type_id        ,
          out_type_name             ,
          out_default_challenge     ,
          out_user_defined_challenge,
          out_return_status         ,
          out_return_msg           
       ) ;
    END RR0091_01;

    =====and second sp which call in first is==========

    procedure d0046_01 (
          inp_dc_cus_type_id          in       number,
          out_dc_cus_type_id          OUT      dt_dc_cus_type_id,
          out_type_name               OUT      dt_type_name,
          out_default_challenge       OUT      dt_default_challenge,
          out_user_defined_challenge  OUT      dt_user_defined_challenge,
          out_return_status           OUT      dt_return_status,
          out_return_msg              OUT      dt_return_msg
       ) is
          required_fields_exception    exception;
          CURSOR dc_cus_type_cur
          IS
             select t.dc_cus_type_id dc_cus_type_id, t.type_name type_name,
             t.default_challenge_flag default_challenge,
             t.user_defined_challenge_flag user_defined_challenge
             from dc_customer_types t
             where t.dc_cus_type_id = inp_dc_cus_type_id;      

          CURSOR dc_cus_type_all_cur
          IS
             select t.dc_cus_type_id dc_cus_type_id, t.type_name type_name,
             t.default_challenge_flag default_challenge,
             t.user_defined_challenge_flag user_defined_challenge
             from dc_customer_types t
             order by t.dc_cus_type_id; 
             loop_count number:=1;
       BEGIN
          out_return_status(1) := 1;
          out_return_msg(1) := null;      
         
          if inp_dc_cus_type_id is null        
          then
             raise required_fields_exception;
          end if;
          if inp_dc_cus_type_id = 0 then
            FOR dc_cus_type_rec IN dc_cus_type_all_cur
            LOOP
               out_dc_cus_type_id (loop_count) := dc_cus_type_rec.dc_cus_type_id;
               out_type_name (loop_count) := dc_cus_type_rec.type_name;
               out_default_challenge (loop_count) := dc_cus_type_rec.default_challenge;
               out_user_defined_challenge (loop_count) := dc_cus_type_rec.user_defined_challenge;
               dbms_output.put_line('out_type_name (loop_count)' || out_type_name (loop_count));          
               loop_count := loop_count + 1;
            END LOOP;
          else
            FOR dc_cus_type_rec IN dc_cus_type_cur
            LOOP
               out_dc_cus_type_id (loop_count) := dc_cus_type_rec.dc_cus_type_id;
               out_type_name (loop_count) := dc_cus_type_rec.type_name;
               out_default_challenge (loop_count) := dc_cus_type_rec.default_challenge;
               out_user_defined_challenge (loop_count) := dc_cus_type_rec.user_defined_challenge;
               dbms_output.put_line('out_type_name (loop_count)' || out_type_name (loop_count));         
               loop_count := loop_count + 1;
            END LOOP;
          end if;
          IF loop_count = 1
          THEN
             raise no_data_found;
          ELSIF loop_count > 1
          THEN
             out_return_status (1) := 0;
             out_return_msg (1) := 'Records Successfully Fetched';
          END IF;        
           exception
                  when required_fields_exception then
                 out_return_status(1):=1;
                 out_return_msg(1):='enter mandatory fields!!!';       
          when no_data_found
          then
             out_return_status(1) := 2;
             out_return_msg(1) := 'record(s) not found.';
          when others
          then
             out_return_status(1) := 3;
             out_return_msg(1) :=
                'dcmw0046_01: internal error. others ' || sqlerrm;           
       end d0046_01;

    When I run this i got Error message saying:

    PLS-00201: identifier 'OUT_DC_CUS_TYPE_ID' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    I am new on oracle.

    Please help

    Many Thanks in Advance

    Wednesday, September 5, 2012 12:45 AM
  • User-578610739 posted

    Hi Aliasgerraj,

    In first sp, first line, what is d0000.dt_dc_cus_type_id? and the same you write in all declare variable after output keyword.

    out_dc_cus_type_id          OUT      d0000.dt_dc_cus_type_id,   

    Are your sp in compile successfully? if not, then first solve sp issue. You must give proper reference if use any datatype or cursor etc.

    After that as below link , declare and pass the parameter .

    http://srikanthtechnologies.com/articles/dotnet/oraclesp.html

    Wednesday, September 5, 2012 8:26 AM
  • User269602965 posted

    http://docs.oracle.com/cd/E11882_01/win.112/e17726.pdf

    You are not specifying your parameter direction,

     and your OUT parameters in your OLEDB code.

    See the Oracle OLEDB guide, 2-27 to end of chapter 2.

    Oracle expects parameters for each value you are passing into and out of the stored proc.

    Wednesday, September 5, 2012 10:45 PM
  • User-111119262 posted

    Hi Ajay,

    my stored procedure run corrently in pl/sql development.

    out_dc_cus_type_id          OUT      d0000.dt_dc_cus_type_id,

    here d0000.dt_dc_cus_type_id, refer to d0000 pakage datatype

    Thursday, September 6, 2012 1:10 AM
  • User-111119262 posted

    I Change my code as i found how to call Oracle PL/SQL Sp, but it give me below error msg:

    Exception ORA-01008: not all variables bound

    Dim oraCon As New OracleConnection("Data Source=MyDB;Password=123;User ID=ABC")
                           
                            oraCon.Open()
                           

                           
                            Dim myCMD As New OracleCommand _
                            ("begin DB01.DB091_01(:inp_dc_cus_type_id,:out_dc_cus_type_id,:out_type_name,:out_default_challenge,:out_user_defined_challenge,:out_return_status,:out_return_msg); end;", oraCon)

                            ' create parameter objects for each parameter
                            Dim inp_dc_cus_type_id As New OracleParameter("inp_dc_cus_type_id", OracleDbType.Int32)
                            Dim out_dc_cus_type_id As New OracleParameter("out_dc_cus_type_id", OracleDbType.Int32)
                            Dim out_type_name As New OracleParameter("out_type_name", OracleDbType.Varchar2, 100)
                            Dim out_default_challenge As New OracleParameter("out_default_challenge", OracleDbType.Char, 1)
                            Dim out_user_defined_challenge As New OracleParameter("out_user_defined_challenge", OracleDbType.Char, 1)
                            Dim out_return_status As New OracleParameter("out_return_status", OracleDbType.Int32)
                            Dim out_return_msg As New OracleParameter("out_return_msg", OracleDbType.Varchar2, 200)
                           

                            '' Set parameter Direction
                            inp_dc_cus_type_id.Direction = ParameterDirection.Input
                            out_dc_cus_type_id.Direction = ParameterDirection.Output
                            out_type_name.Direction = ParameterDirection.Output
                            out_default_challenge.Direction = ParameterDirection.Output
                            out_user_defined_challenge.Direction = ParameterDirection.Output
                            out_return_status.Direction = ParameterDirection.Output
                            out_return_msg.Direction = ParameterDirection.Output
                           

                            '' Setup the values for PL/SQL Associative Array
                            inp_dc_cus_type_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray
                            out_dc_cus_type_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray
                            out_type_name.CollectionType = OracleCollectionType.PLSQLAssociativeArray
                            out_default_challenge.CollectionType = OracleCollectionType.PLSQLAssociativeArray
                            out_user_defined_challenge.CollectionType = OracleCollectionType.PLSQLAssociativeArray
                            out_return_status.CollectionType = OracleCollectionType.PLSQLAssociativeArray
                            out_return_msg.CollectionType = OracleCollectionType.PLSQLAssociativeArray

                            inp_dc_cus_type_id.Value = v_in_cus_type_id
                            out_dc_cus_type_id.Value = Nothing
                            out_type_name.Value = Nothing
                            out_default_challenge.Value = Nothing
                            out_user_defined_challenge.Value = Nothing
                            out_return_status.Value = Nothing
                            out_return_msg.Value = Nothing

                            '' set parameter Size for each parameter
                            out_dc_cus_type_id.Size = 1
                            out_type_name.Size = 1
                            out_default_challenge.Size = 1
                            out_user_defined_challenge.Size = 1
                            out_return_status.Size = 1
                            out_return_msg.Size = 1_fileLogger.Log("Define  Parameter Size")

                           
                            out_type_name.ArrayBindSize = New Int32() {100}
                            out_default_challenge.ArrayBindSize = New Integer() {1}
                            out_user_defined_challenge.ArrayBindSize = New Integer() {1}
                            out_return_msg.ArrayBindSize = New Integer() {200}

     
                            oraObjAdapter = New OracleDataAdapter(myCMD)
                            oraObjAdapter.Fill(Ds)

    Help please, Many Thanks in Advance

    Sunday, September 9, 2012 4:55 AM
  • User269602965 posted

    Missing dimensioning of your dataset

            Dim Ds As New DataSet()

    And

    Put your code inside USING TRY CATCH structure to handle exceptions and give you some more information on errors by handling the error

        Using conn As New OracleConnection(connectionString)
          Try
            ' code for pararmeters, dataset, data adapters, etc. '
          Catch ex As Exception
            'error logging code here '
          Finally
            conn.Close()
            conn.Dispose()
          End Try
        End Using

    Sunday, September 9, 2012 5:44 PM
  • User-111119262 posted

    Hi Lannie,

    I already defined DataSet as you write above.

    And yes i use Try Catch but not use using to handling the error.

    i try many which is given on google. but not succeed yet.

    in my procedure i use "TYpe Is Table" variable for output parameter.

    i think i didn't mapp properly this on my code side.

    Monday, September 10, 2012 12:37 AM