Ytukyg

This Code is:

CREATE OR REPLACE

PROCEDURE Actualiza_Saldo(fecha_ini IN DATE, fecha_fin IN DATE) RETURN NUMBER

AS

  fechaTemp DATE;

  diasTotales NUMBER := fecha_fin- fecha_ini;diasLaborables NUMBER;

  sab VARCHAR2(10) := 'SÁBADO';dom VARCHAR2(10) := 'DOMINGO';diasTemp VARCHAR2(10);

BEGIN

  diasLaborables:= diastotales;



  FOR i IN 0..diasTotales LOOP

    fechaTemp := fecha_ini + i;

    DBMS_OUTPUT.PUT_LINE(to_char(fechaTemp));

    diasTemp := TO_CHAR(fechaTemp, 'DAY', 'NLS_DATE_LANGUAGE=SPANISH');

    IF (TRIM(diasTemp)=sab or TRIM(diasTemp)=dom) THEN

      diaslaborables := diaslaborables-1;

    END IF;

  END LOOP;

  dbms_output.put_line(diaslaborables);

  RETURN diasLaborables;

END Actualiza_Saldo;

If I execute without returning it works, if I try to return a value it fails, I do not know what could be happening.

The error of oracle is:

Warning: la ejecución ha terminado con advertencias

PROCEDURE Actualiza_Saldo(fecha_ini Compilado.



Error que empieza en la línea 1 del comando:

EXEC Actualiza_Saldo();

Informe de error:

ORA-06550: línea 1, columna 7:

PLS-00905: el objeto HR.ACTUALIZA_SALDO no es válido

ORA-06550: línea 1, columna 7:

PL/SQL: Statement ignored

06550. 00000 -  "line %s, column %s:n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

The code with constant value:

CREATE OR REPLACE

PROCEDURE Actualiza_Saldo(fecha_ini IN DATE DEFAULT '10/08/2018', fecha_fin IN DATE DEFAULT '30/08/2018')

AS

  fechaTemp DATE;

  diasTotales NUMBER := fecha_fin- fecha_ini;diasLaborables NUMBER;

  sab VARCHAR2(10) := 'SÁBADO';dom VARCHAR2(10) := 'DOMINGO';diasTemp VARCHAR2(10);

BEGIN

  diasLaborables:= diastotales;



  FOR i IN 0..diasTotales LOOP

    fechaTemp := fecha_ini + i;

    DBMS_OUTPUT.PUT_LINE(to_char(fechaTemp));

    diasTemp := TO_CHAR(fechaTemp, 'DAY', 'NLS_DATE_LANGUAGE=SPANISH');

    IF (TRIM(diasTemp)=sab or TRIM(diasTemp)=dom) THEN

      diaslaborables := diaslaborables-1;

    END IF;

  END LOOP;

  dbms_output.put_line(diaslaborables);

END Actualiza_Saldo;



EXEC Actualiza_Saldo();

And the exit of the code without the returns and the test values ​​is a route from the start date and the final date subtracting the days Saturday and Sunday.

PROCEDURE Actualiza_Saldo(fecha_ini Compilado.

anonymous block completed

10/08/18

11/08/18

12/08/18

13/08/18

14/08/18

15/08/18

16/08/18

17/08/18

18/08/18

19/08/18

20/08/18

21/08/18

22/08/18

23/08/18

24/08/18

25/08/18

26/08/18

27/08/18

28/08/18

29/08/18

30/08/18

14

But if I try to return the value the algorithm dies.
I have no idea what I am doing wrong or where is the fault, if you could help me I would greatly appreciate it.

Procedures cannot return a value, in Oracle, You can have out-parameters which would be visible after the procedure runs.

But here is another option you can try, instead of writing code to generate dates between two, try to do it using a single select query and open a out cursor to have the values populated after the proc completes

An example as follows

create or replace procedure generate_dates(start_date in date, end_date in date, result_set out sys_refcursor)

as

begin

   open result_set for

      select trunc(start_date)+level as output_dates

        from dual

   connect by level<=trunc(end_date)-trunc(start_date);

end;

if you are using sqlplus to connect to your database

you would call the proc as follows

var x refcursor



begin

  generate_dates(date '2018-01-01',date '2018-12-31',:x);

end;



print x;

My mistake was that I was not doing a function but a procedure solved.

CREATE OR REPLACE

FUNCTION HR.Actualiza_Saldo(fecha_ini IN DATE/* DEFAULT '10/08/2018'*/, fecha_fin IN DATE/* DEFAULT '30/08/2018'*/) RETURN NUMBER

AS