Tab1
-------------------------------------------------------
| RG | NOME | DT_INI | DT_FIM |
-------------------------------------------------------
| 25 | João | 01/05/85 | 30/02/90 |
-------------------------------------------------------
| 26 | Maria | 25/01/93 | 19/12/02 |
-------------------------------------------------------
| 27 | José | 06/04/00 | 20/08/10 |
-------------------------------------------------------
Transformo o que é linha em colunas.
Tab2
-------------------------------------------------------------------------
| CD | DESC | _25 | _26 | _27 |
-------------------------------------------------------------------------
| 01 | RG | 25 | 26 | 27 |
-------------------------------------------------------------------------
| 02 | Nome | João | Maria | José |
-------------------------------------------------------------------------
| 03 | Dt_Ini | 01/05/85 | 25/01/93 | 06/04/00 |
-------------------------------------------------------------------------
| 04 | Dt_Fim | 30/02/90 | 19/12/02 | 20/08/10 |
-------------------------------------------------------------------------
Estou tentando fazer uma query dinâmica aonde ele roda um update e caso não afetar nenhuma linha insere os dados.
Tab2
-------------------------------------------------------------------------
| CD | DESC | _25 | _26 | _27 |
-------------------------------------------------------------------------
| 01 | RG | 25 | 26 | 27 |
-------------------------------------------------------------------------
| 02 | Nome | João | Maria | José |
-------------------------------------------------------------------------
| 03 | Dt_Ini | 01/05/85 | 25/01/93 | 06/04/00 |
-------------------------------------------------------------------------
| 04 | Dt_Fim | 30/02/90 | 19/12/02 | 20/08/10 |
-------------------------------------------------------------------------
| 05 | Maçã | 5 | | |
-------------------------------------------------------------------------
| 06 | Banana | | 3 | 2 |
-------------------------------------------------------------------------
Pra inserir a quantidade (5) na coluna _25 que corresponde ao João preciso fazer isso dinâmicamente.
Para isso executo o seguinte código :
declare
v_intern number;
v_query varchar2(1000);
begin
for pac in (select tmp2.PACI_ID -- cursor de pacientes
,tmp2.REGISTRO
,tmp2.NOME
,tmp2.DT_INI
,tmp2.DT_FIM
,tmp2.DT_ULT_CONS
,tmp2.DT_OBITO
from tmp2
where tmp2.PACI_ID = 2760927) loop
v_intern := 0;
for adm_in in (select adm.ADM_ANO -- cursor de admissões
,adm.ADM_NR
,adm.ADM_INST_CD
,adm.ADM_DT_INGRESSO
,said.SAID_DT_HR
,said.SAID_TISA_SQ
from adm_admissao@reporting adm
,sai_saida@reporting said
where adm.ADM_ANO = said.SAID_ADM_ANO (+)
and adm.ADM_INST_CD = said.SAID_INST_CD (+)
and adm.ADM_NR = said.SAID_ADM_NR (+)
and adm.ADM_PACI_ID = pac.PACI_ID
and adm.ADM_DT_INGRESSO <= pac.DT_FIM
and said.SAID_DT_HR >= pac.DT_INI
and adm.ADM_TP = 'IN'
and (adm.ADM_ST <> 9
or adm.ADM_ST is null)
and said.SAID_DT_HR_CANC (+) is null
order by adm.ADM_DT_INGRESSO) loop
v_intern := v_intern + 1;
for item in (select itge.ITGE_CD -- cursor de itens
,itge.ITGE_TP
,itge.ITGE_NM
,sum(icon.ICON_QT) qtde
from cpa_item_conta@reporting icon
,cpa_conta_paciente@reporting copa
,prc_item_generico@reporting itge
where icon.ICON_COPA_SQ = copa.COPA_SQ
and icon.ICON_ITGE_CD = itge.ITGE_CD
and icon.ICON_ITGE_TP = itge.ITGE_TP
and copa.COPA_ADM_ANO = adm_in.ADM_ANO
and copa.COPA_ADM_NR = adm_in.ADM_NR
and copa.COPA_INST_CD = adm_in.ADM_INST_CD
and itge.ITGE_CD = 'MASSA'
-- and icon.ICON_DT_INICIO between pac.DT_INI
-- and pac.DT_FIM
group by itge.ITGE_CD
,itge.ITGE_TP
,itge.ITGE_NM) loop
---------------------------------------------------------------
---------------------------------------------------------------
begin
v_query := 'update tmp3'||chr(10);
v_query := v_query || 'set tmp3.V_'||pac.PACI_ID||' = '||'nvl(tmp3.V_'||pac.PACI_ID||',0) + '||item.QTDE||chr(10);
v_query := v_query || 'where tmp3.ITGE_CD = '''||item.ITGE_CD||''''||chr(10);
v_query := v_query || 'and tmp3.ITGE_TP = '''||item.ITGE_TP||''';'||chr(10);
v_query := v_query || 'if SQL%ROWCOUNT > 0 then'||chr(10);
v_query := v_query || ' dbms_output.put_line(''Atualizou corretamente'');'||chr(10);
v_query := v_query || 'else'||chr(10);
v_query := v_query || ' insert into tmp3'||chr(10);
v_query := v_query || ' (tmp3.ITGE_CD'||chr(10);
v_query := v_query || ' ,tmp3.ITGE_TP'||chr(10);
v_query := v_query || ' ,tmp3.ITGE_NM'||chr(10);
v_query := v_query || ' ,tmp3.V_'||pac.PACI_ID||')'||chr(10);
v_query := v_query || ' values('''||item.ITGE_CD||''''||chr(10);
v_query := v_query || ' ,'''||item.ITGE_TP||''''||chr(10);
v_query := v_query || ' ,'''||item.ITGE_NM||''''||chr(10);
v_query := v_query || ' ,'''||item.QTDE||''');'||chr(10);
v_query := v_query || ' dbms_output.put_line(''Inseriu'');'||chr(10);
v_query := v_query || 'end if;';
insert into xxx values(v_query);
commit;
execute immediate v_query;
commit;
exception
when others then
dbms_output.put_line('Não inseriu | '||sqlerrm);
end;
---------------------------------------------------------------
---------------------------------------------------------------
end loop;
end loop;
end loop;
exception
when others then
null;
end;Ele faz um insert na tabela xxx, o código é executável e funciona, quando copio da tabela xxx.
Quando ele faz o execute immediate dá um erro.
alguém sabe o que está errado ??ORA-00911: caractere inválido
