Migrar Procedure do firbird para o Oracle
19/06/2010
0
Olá, tenho uma procedure que funciona 100% no firebird, mas no Oracle não consigo fazer funcionar, no firebird tenho assim:
CREATE OR ALTER PROCEDURE PROC_TOTALIZA_CTRC_CONTABILIDAD (
codigo dmn_inteiro)
returns (
sete dmn_valores,
doze dmn_valores,
total dmn_valores,
icms dmn_valores)
as
begin
for
select sum(cd.icms) as sete, cast(null as decimal(15,2)) as doze, cast(null as decimal(15,2))as total, cast(null as decimal(15,2))as ICMS
from conhecimentocontabilidadedetail cd
where cd.aliquota=7 and cd.idconhecimentopai =:codigo
union
select cast(null as decimal(15,2)), sum(cd.icms) as doze, cast(null as decimal(15,2))as total, cast(null as decimal(15,2))as ICMS
from conhecimentocontabilidadedetail cd
where cd.aliquota=12 and cd.idconhecimentopai =:codigo
union
select cast(null as decimal(15,2)), cast(null as decimal(15,2)) as doze, sum(cd.valor) as total, cast(null as decimal(15,2))as ICMS
from conhecimentocontabilidadedetail cd
where cd.idconhecimentopai =:codigo
union
select cast(null as decimal(15,2)), cast(null as decimal(15,2)) as doze, cast(null as decimal(15,2))as total, sum(cd.icms)as ICMS
from conhecimentocontabilidadedetail cd
where cd.idconhecimentopai =:codigo
into :sete, :doze, :total, :icms
do
suspend;
end
no oracle coloquei assim mas não funciona:
CREATE OR REPLACE PROCEDURE PROC_TOTAL_CTRC_CONTABILIDADE
(
CODIGO IN NUMBER
, SETE OUT NUMBER
, DOZE OUT NUMBER
, TOTAL OUT NUMBER
, ICMS OUT NUMBER
) AS
BEGIN
select sum(cd.icms) as sete, cast(null as number) as doze, cast(null as
number)as total, cast(null as number) as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.aliquota=7 and cd.idctrcpai=10 group by cast(null as number),
cast(null as number), cast(null as number)
union all
select cast(null as number) as sete, sum(cd.icms) as doze, cast(null as
number)as total, cast(null as number)as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.aliquota=12 and cd.idctrcpai=10
union all
select cast(null as number) as sete, cast(null as number)as doze, sum(cd.valor)
as total, cast(null as number)as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.idctrcpai=10
union all
select cast(null as number) as sete, cast(null as number)as doze, cast(null as
number) as total, sum(cd.icms) as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.idctrcpai=10
into sete, doze, total, icms;
END PROC_TOTAL_CTRC_CONTABILIDADE;
Poderiam me dizer onde estou errando? o erro que o compilador aponta é o
seguinte:
Erro(10,2): PL/SQL: SQL Statement ignored
Erro(25,2): PL/SQL: ORA-00933: comando SQL não encerrado adequadamente
CREATE OR ALTER PROCEDURE PROC_TOTALIZA_CTRC_CONTABILIDAD (
codigo dmn_inteiro)
returns (
sete dmn_valores,
doze dmn_valores,
total dmn_valores,
icms dmn_valores)
as
begin
for
select sum(cd.icms) as sete, cast(null as decimal(15,2)) as doze, cast(null as decimal(15,2))as total, cast(null as decimal(15,2))as ICMS
from conhecimentocontabilidadedetail cd
where cd.aliquota=7 and cd.idconhecimentopai =:codigo
union
select cast(null as decimal(15,2)), sum(cd.icms) as doze, cast(null as decimal(15,2))as total, cast(null as decimal(15,2))as ICMS
from conhecimentocontabilidadedetail cd
where cd.aliquota=12 and cd.idconhecimentopai =:codigo
union
select cast(null as decimal(15,2)), cast(null as decimal(15,2)) as doze, sum(cd.valor) as total, cast(null as decimal(15,2))as ICMS
from conhecimentocontabilidadedetail cd
where cd.idconhecimentopai =:codigo
union
select cast(null as decimal(15,2)), cast(null as decimal(15,2)) as doze, cast(null as decimal(15,2))as total, sum(cd.icms)as ICMS
from conhecimentocontabilidadedetail cd
where cd.idconhecimentopai =:codigo
into :sete, :doze, :total, :icms
do
suspend;
end
no oracle coloquei assim mas não funciona:
CREATE OR REPLACE PROCEDURE PROC_TOTAL_CTRC_CONTABILIDADE
(
CODIGO IN NUMBER
, SETE OUT NUMBER
, DOZE OUT NUMBER
, TOTAL OUT NUMBER
, ICMS OUT NUMBER
) AS
BEGIN
select sum(cd.icms) as sete, cast(null as number) as doze, cast(null as
number)as total, cast(null as number) as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.aliquota=7 and cd.idctrcpai=10 group by cast(null as number),
cast(null as number), cast(null as number)
union all
select cast(null as number) as sete, sum(cd.icms) as doze, cast(null as
number)as total, cast(null as number)as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.aliquota=12 and cd.idctrcpai=10
union all
select cast(null as number) as sete, cast(null as number)as doze, sum(cd.valor)
as total, cast(null as number)as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.idctrcpai=10
union all
select cast(null as number) as sete, cast(null as number)as doze, cast(null as
number) as total, sum(cd.icms) as ICMS
from sis_ctrc_contabilidade_detail cd
where cd.idctrcpai=10
into sete, doze, total, icms;
END PROC_TOTAL_CTRC_CONTABILIDADE;
Poderiam me dizer onde estou errando? o erro que o compilador aponta é o
seguinte:
Erro(10,2): PL/SQL: SQL Statement ignored
Erro(25,2): PL/SQL: ORA-00933: comando SQL não encerrado adequadamente
Rodrigo Galvan
Curtir tópico
+ 0
Responder
Clique aqui para fazer login e interagir na Comunidade :)