locked
ASP.Net Oracle stored procedure returns no record RRS feed

  • Question

  • User1199913221 posted

    I have assigment to convert Oracle Form application to ASP.NEt. I am quite new in Oracle and have problem to retrieve data using Oracle SP.

    Here is my VB code

    If oracleConnect.ConnectDb.State = 1 Then
    
                    Dim cmd = oracleConnect.ConnectDb.CreateCommand
                    With cmd
                        .CommandType = CommandType.StoredProcedure
                        .CommandText = "DBP_SELECT_FIX_SECUR_PROFILE"
                        .Parameters.Add("p_code", Oracle.DataAccess.Client.OracleDbType.Varchar2, 20).Value = searchCode '.Direction = ParameterDirection.Input
                        .Parameters.Add("p_program", Oracle.DataAccess.Client.OracleDbType.Varchar2, 20).Value = searchProgram '.Direction = ParameterDirection.Input
                        .Parameters.Add("p_page_number", Oracle.DataAccess.Client.OracleDbType.Int32).Value = pageNumber '.Direction = ParameterDirection.Input
                        .Parameters.Add("p_page_size", Oracle.DataAccess.Client.OracleDbType.Int32).Value = recordSize '.Direction = ParameterDirection.Input
                        .Parameters.Add("p_CODE_out", Oracle.DataAccess.Client.OracleDbType.Varchar2, 20).Direction = ParameterDirection.Output
                        .Parameters.Add("p_SHORTNAME", Oracle.DataAccess.Client.OracleDbType.Varchar2, 20).Direction = ParameterDirection.Output
                        .Parameters.Add("p_Fxsytp_id", Oracle.DataAccess.Client.OracleDbType.Int32, 20).Direction = ParameterDirection.Output
                        .Parameters.Add("p_ICS", Oracle.DataAccess.Client.OracleDbType.Int32, 20).Direction = ParameterDirection.Output
                        .Parameters.Add("p_Fixsecacc_Id", Oracle.DataAccess.Client.OracleDbType.Int32, 20).Direction = ParameterDirection.Output
                        .Parameters.Add("p_Rownum", Oracle.DataAccess.Client.OracleDbType.Int32, 20).Direction = ParameterDirection.Output
                    End With
     
    
    Dim reader = cmd.ExecuteReader()
    
                    While reader.Read
                        If reader.HasRows Then
                            GetListSecuritiesRoles = reader
                        End If
                    End While
    
    Oracle SP code
    create or replace
    procedure dbp_select_fix_secur_profile
    ( p_code in varchar2
    , p_program in varchar2
    , p_page_number in integer
    , p_page_size in integer
    , p_CODE_out out varchar2
    , p_SHORTNAME out varchar2
    , p_Fxsytp_id out integer
    , p_ICS out integer
    , p_Fixsecacc_Id out integer
    , p_Rownum out integer
    ) 
    as
    begin
      declare cursor c is 
          Select * From (Select  t.*, Rownum As RN 
                           From ( 
                           Select Distinct Fxsytp.Code CODE,
                                 Fixsecacc.Fxsytp_Id Fxsytp_Id,
                                 Prog.Shortname SHORTNAME,
                                 Orgs.ICS_Code ICS_CODE, 
                                 Fixsecacc.Fixsecacc_Id Fixsecacc_Id 
                            From Fix_Dba.Fix_Security_Accesses Fixsecacc, 
                                 Fix_Dba.Fix_Security_Types Fxsytp,
                                 Fix_Dba.Organizations Orgs, Fix_Dba.Programs Prog,
                                 fix_dba.egov_application_codes Egc 
                            Where Fxsytp.Fxsytp_Id = Fixsecacc.Fxsytp_Id 
                            And Fxsytp.Code Like  ''||p_code||''
                            And Prog.Shortname Like ''||p_program ||''
                            And  Fixsecacc.Egovapplcd_id = Egc.Egovapplcd_id(+) 
                            And Orgs.Org_Seq_Num (+) = Fixsecacc.Org_Seq_Num 
                            And Prog.Der_Code (+) = Fixsecacc.Der_Code 
                            And ( Fixsecacc.Inactive_Date Is Null 
                                  Or Trunc(Fixsecacc.Inactive_Date) > Trunc(Sysdate)) 
                            And ( Fxsytp.Inactive_Date Is Null 
                                   Or Trunc(Fxsytp.Inactive_Date) > Trunc(Sysdate)) 
                            Order by 1) 
                            t ) 
          Where RN Between  p_page_number
          And  p_page_size;
       begin
          open c;
          loop
            fetch c into
                   p_CODE_out
                 , p_Fxsytp_id
                 , p_SHORTNAME
                 , p_ICS
                 , p_Fixsecacc_Id
                 , p_Rownum;
                 
                              --exit when c%notfound;
                 IF c%notFOUND THEN  -- fetch failed, so exit loop
                     DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || p_code || ' ' || P_CODE_OUT); 
                     EXIT WHEN c%NOTFOUND OR c%NOTFOUND IS NULL;
                 ELSE  
                    DBMS_OUTPUT.PUT_LINE('Page # = ' || p_Rownum || ' Code = ' || p_CODE_out || ', Program = ' || p_SHORTNAME || ',ICS = ' || P_ICS);
                 END IF;
                 
           end loop;   
           close c;
       end;  
       
       EXCEPTION 
           WHEN others THEN  
          DBMS_OUTPUT.PUT_LINE ('ERROR');
    end dbp_select_fix_secur_profile;
    When I run SP on Oracle site using SQL Developer it did return data. However when I run SP using VB code it retrieves no data. I replaced VB code by code
    like this
    Dim cmd = oracleConnect.ConnectDb.CreateCommand
                                 cmd.CommandText = "SELECT  * FROM  (SELECT  t.*, ROWNUM AS RN " & _
                                      "FROM ( " & _
                                              "Select Distinct Fxsytp.Code CODE," & _
                                              "Fixsecacc.Fxsytp_Id Fxsytp_Id," & _
                                              "Prog.Shortname SHORTNAME," & _
                                              "Orgs.ICS_Code ICS_CODE, " & _
                                              "Fixsecacc.Fixsecacc_Id Fixsecacc_Id " & _
                                              "From Fix_Dba.Fix_Security_Accesses Fixsecacc, " & _
                                              "Fix_Dba.Fix_Security_Types Fxsytp," & _
                                              "Fix_Dba.Organizations Orgs, Fix_Dba.Programs Prog," & _
                                              "fix_dba.egov_application_codes Egc " & _
                                              "Where Fxsytp.Fxsytp_Id = Fixsecacc.Fxsytp_Id " & _
                                              "And Fxsytp.Code LIKE '" & searchCode & "' " & _
                                              "and Prog.Shortname like '" & searchProgram & "' " & _
                                              "And  Fixsecacc.Egovapplcd_id = Egc.Egovapplcd_id(+) " & _
                                              "And Orgs.Org_Seq_Num (+) = Fixsecacc.Org_Seq_Num " & _
                                              "And Prog.Der_Code (+) = Fixsecacc.Der_Code " & _
                                              "And ( Fixsecacc.Inactive_Date Is Null " & _
                                              "     Or Trunc(Fixsecacc.Inactive_Date) > Trunc(Sysdate)) " & _
                                              "And ( Fxsytp.Inactive_Date Is Null " & _
                                              "     Or Trunc(Fxsytp.Inactive_Date) > Trunc(Sysdate)) " & _
                                              "Order by 1) t ) " & _
                                               "WHERE RN BETWEEN " & pageNumber & " And " & recordSize
    
    
                    Dim reader = cmd.ExecuteReader()
    
                    While reader.Read
                        If reader.HasRows Then
                            GetListSecuritiesRoles = reader
                        End If
                    End While
    And it did work perfectly. My question is why I did not get any data calling Qracle SP from VB?
    Thursday, November 17, 2011 11:01 AM

All replies

  • User269602965 posted

    Example of calling stored procedure

    VB.NET code in ASP.NET application
    
        ' Get data from stored procedure '
        Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
            Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectBirdNames", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output)
              conn.Open()
              Using oda As New OracleDataAdapter(cmd)
                Dim ds As New DataSet()
                oda.Fill(ds)
                Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
              End Using
            End Using
          End Using
        Catch ex As Exception
        End Try
    
    Oracle PL/SQL code
        
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
      PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor);
    END {PACKAGENAME};
    /
    
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS
    
    PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor)
    IS
    BEGIN
      OPEN ListBirdNames FOR
        SELECT BIRDNAME FROM {SCHEMANAME}.LKUP_BIRDNAME;
    END;
    
    END {PACKAGENAME};
    /
    

     

    Thursday, November 17, 2011 10:59 PM
  • User1199913221 posted

    Thanks a lot Lannie!

    I will try it!

    Friday, November 18, 2011 9:08 AM
  • User269602965 posted

    Example If you are going to use Code Behind Page/Form for the SQL statement call, instead of STORED PROC

    so here is an example:

    This is an update statement, but notice the proper use of Parameter Name as a BIND Variable in the SQL statement

    with parameter name prefaced by a COLON symbol.  In this example the parameter name is CUSTOMER_SEQ

    and in the SQL the parameter is referenced as :CUSTOMER_SEQ.

    Also, notice the use of SQL statement as a XML variable instead of the harder to read, and more error prone

    String Builder way of building an SQL statement.  My error rate dropped significantly when I stopped using string building

    and migrated to XML tag <SQL>{SQL statement}</SQL> called by SQL.Value to retrieve the string.

    Imports System.Xml.Linq.XElement
    
      Public Shared Sub updateUnitsActiveFlag(ByVal decCustomerSeq As Decimal)
        ' Update the UNITS.ACTIVE_FLAG on Customer set to Inactive'
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("AuthenticatedOracleConnectionString").ConnectionString
        Try
          Dim SQL =
          <SQL>
          UPDATE {YOURSCHEMANAME}.UNITS
          SET ACTIVE_FLAG        = 'Inactive',
              SEND_CONTRACT_FLAG = 'Do not send'
          WHERE CUSTOMER_SEQ = :CUSTOMER_SEQ
          </SQL>
          Using conn As New OracleConnection(connectionString)
            Using cmd As New OracleCommand(SQL.Value, conn)
              cmd.Parameters.Clear()
              cmd.Parameters.Add("CUSTOMER_SEQ", OracleDbType.Decimal, decCustomerSeq, ParameterDirection.Input)
              conn.Open()
              cmd.ExecuteNonQuery()
            End Using
          End Using
        Catch ex As Exception
        End Try
      End Sub
    Friday, November 18, 2011 11:24 AM
  • User1199913221 posted

    Lannie,<o:p></o:p>

    Thanks for your XML example.<o:p></o:p>

    I figured out how to get data using VB code and Oracle REF Cursor Package/SP and not going to use SQL statement (it is actually against my company practice and used it just to test SQL statement in SP). I probably will follow your advice and try XML too.<o:p></o:p>

    Thanks for your time and help!   I really appreciated it Smile<o:p></o:p>

    Friday, November 18, 2011 12:48 PM