failure in function snippet to read table

Question

I’m developing a function that reads from one table and inserts into the other:

The error is: Warning: Function created with build errors.

I removed the select line and it works. So, the error is in it.

create or replace function FC_INS_TIPO_GASTO  
return number  
as  

  v_numSubCota transparencia_gastos.numSubCota%type;  
  v_txtDescricao transparencia_gastos.txtDescricao%type;  

begin  
  select distinct numSubCota into v_numSubCota, txtDescricao into 

  v_txtDescricao
  from transparencia_gastos
  where sgUF='PB' or sgUF='PE'; 



  insert into tipo_gasto
  (cod_tipo_gasto, des_tipo_gasto, dtc_cadastro)
  values
  (v_numSubCota, v_txtDescricao, systimestamp);



  return 0;  
end;  
/  

Can’t select dentor from Begin end? But I’ve tested it out of the block and it doesn’t work either.

Answer 1

Yes, it is possible to do dml operations without problem inside given plsql block, the problem is in the syntax of your select, you used twice the into clause within the same query. Correct would be

select distinct numSubCota, txtDescricao
  into v_numSubCota, v_txtDescricao
  from transparencia_gastos
 where sgUF = 'PB'
    or sgUF = 'PE';

Remembering that the "select into" operation only allows the return of one record at a time. In your code as it contains an OR condition in the filter, gives the impression that it can return more than one record.

In your case, as you already answered, it would be better to make a loop by making the Insert. However, another option would be to use an "Insert select" thus performing everything in one operation and optimizing performance.

insert into tipo_gasto
  (cod_tipo_gasto, des_tipo_gasto, dtc_cadastro)
  select distinct numSubCota, txtDescricao, systimestamp
    from transparencia_gastos
   where sgUF = 'PB'
      or sgUF = 'PE';

Answer 2

I solved using cursor.

create or replace function FC_INS_TIPO_GASTO return number as

  cursor c_transparencia_gastos is
    select distinct numSubCota, txtDescricao
      from transparencia_gastos
     where sgUF = 'PB'
        or sgUF = 'PE';

begin
  for rg in c_transparencia_gastos loop
    insert into tipo_gasto
      (cod_tipo_gasto, des_tipo_gasto, dtc_cadastro)
    values
      (rg.numSubCota, rg.txtDescricao, systimestamp);

  end loop;

  return 0;
end;

Of course I put error handling and the return is not only, 0, it depends on the error, but as the question was only about select, I just put the main answer.