ASP.Net Oracle stored procedure returns no record

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?

Answer 1

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};
/

 

Answer 2

User1199913221 posted

Thanks a lot Lannie!

I will try it!

Answer 3

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

Answer 4

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 <o:p></o:p>