locked
Oracle Application Express RRS feed

  • 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

    Sunday, April 12, 2015 4:48 AM

All replies

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

    Sunday, April 12, 2015 10:34 AM
  • 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.
    
    

    Monday, April 13, 2015 11:13 AM