function snippet failed to read table

Question

function snippet failed to read table

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I'm developing a function that read from one table and inserts into another:

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

I've 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 not select dentin from begin end? But I've already tested out of the block and it does not work either.

oracle pl-sql

asked by anonymous 06.10.2018 / 02:26

2 answers

Set, fetch and check a Cookie after a video (YouTube API) finish - JavaScript How to change wodpress paging to load more

score 0 · Answer 1

Yes, it is possible to do dml operations with no problem within the m plsql block, the problem is in your select syntax, you used the into clause twice within the same query. the correct one 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 you to return one record at a time. In your code as it contains an OR condition on the filter, it gives the impression that it can return more than one record.

In your case, as you already replied, it would be better to loop through the insert. But another option would be to use an "insert select" so doing 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';

score 0 · 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, depends on the error, but as the question was only about the select, I just put the main answer.