Oracle Application Express

Question

User1166333741 posted

Hi,

probably an easy question but I've been working on an assignment for school where I need to make an  application with Oracle Application Express.
I want to use this function to calculate the amount of working days between two dates:

create or replace function WERKDAGEN (p_startdatum in date, p_einddatum in date) return integer
is
                v_startdatum date :=p_startdatum;
        v_einddatum date:=p_einddatum;
        v_count integer:=0;
begin                 
while v_startdatum <= v_einddatum
        loop
        if            
        to_char(v_startdatum,'D') not in ('6','7')                     
    then
            v_count := v_count+1;                        
    end if;
        v_startdatum:=v_startdatum+1;
    end loop;
return v_count;
end;

When I use this function in iSQL and i put in two values it works.
I want to pick the starting date and the ending date values from my table.
How do I use other values like startdatum_verlof and einddatum_verlof from another table goedgek_verlofaanvraag as the input in this function?
Do I need to use a cursor? Or is there an option in Oracle Application Express for this problem?

I hope someone can help me with this problem

Answer 1

User269602965 posted

1. Is the application feeding a single row of data from a front end .NET FORM (Web, WPF, Winforms) for the calculation?

2. Or are you doing this on many rows at once.

In the case of 1.  You can all the PLSQL function from within a SQL statement for that filtered row

   or if you are inserting the results of the calculation you can use ODP.NET to pass the variables IN and get the result OUT as Oracle Parameters.

In the case of 2, you might be wanting to feed start dates and end dates from many rows to populate a table with out data,

then you would want to use an ODP.NET REF CURSOR to populate the table on the front end application.

Example of passing data into and out as ref cursor from .net to PLSQL package procedure using IN parameters and OUT REFCURSOR to FILL the dataset with out data.

/* 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}.rcGET_BIRD_DATA", conn)
          cmd.CommandType = CommandType.StoredProcedure
          cmd.Parameters.Clear()
          cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input)
          cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input)
          cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input)
          cmd.Parameters.Add("ret_REFCUR", 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
PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                         ,p_AOU_CODE    IN  NUMBER
                         ,p_BIRD_CLASS  IN  VARCHAR2
                         ,ret_REFCUR    OUT SYS_REFCURSOR);
END PKG_BIRDS;
/

CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME}
AS
PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                         ,p_AOU_CODE    IN  NUMBER
                         ,p_BIRD_CLASS  IN  VARCHAR2
                         ,ret_REFCUR    OUT SYS_REFCURSOR)
BEGIN
	OPEN ret_REFCUR FOR
		SELECT
			AOU_CODE,
			BIRD_CLASS,
			BIRD_NAME,
			DATE_BAND,
			WING_LENGTH,
			TAIL_LENGTH,
			EYE_COLOR,
			PLUMAGE_CODE
		FROM
			{SCHEMANAME}BANDED_BIRDS
		WHERE
			DATE_BAND  > p_DATE_BAND
			AND
			AOU_CODE   = p_AOU_CODE
			AND
			BIRD_CLASS = p_BIRD_CLASS
END;

END {SCHEMANAME}.{PACKAGENAME};
/

Answer 2

User269602965 posted

SELECT
  a.WERKNEMER_ID,
  a.NAAM,
  {SCHEMANAME}.WERKDAGEN(b.STARTDATUM_VERLOF, b.EINDATUM_VERLOF) AS WERKDAGEN
FROM
  EMPLOYEE a
INNER JOIN GOEDGEK_VERLOFAANVRAAG b ON a.WERKNEMER_ID = b.WERKNEMER_ID
/

The user will have to have execute permissions on WERKDAGEN function/procedure

Just as guess as you have not provided the data definition of your tables you are joining.