Details: "Oracle: ORA-01008: not all variables bound "

[craig811]

Hi,

I am trying to use my sql in a direct query:

SELECT SEGMENT1"Item Number", WIP_ENTITY_ID "WIP ID", WIP_ENTITY_NAME "WIP Job Number" ,SUM(VAL)"Value of STD Adjustment"
FROM
( SELECT MSIB.SEGMENT1,WDJ.WIP_ENTITY_ID, WE.WIP_ENTITY_NAME, MTA.BASE_TRANSACTION_VALUE VAL ,MTA.ACCOUNTING_LINE_TYPE LINE, WDJ.CREATION_DATE,WDJ.DATE_COMPLETED,GP.PERIOD_NAME
FROM APPS.MTL_TRANSACTION_ACCOUNTS MTA,
APPS.WIP_DISCRETE_JOBS WDJ,
APPS.WIP_ENTITIES WE ,
MTL_SYSTEM_ITEMS_B MSIB,
GL_PERIODS GP
WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = 121
AND MTA.ACCOUNTING_LINE_TYPE = 2
AND MTA.REFERENCE_ACCOUNT = 758049
AND MTA.ORGANIZATION_ID = 121
AND WDJ.STATUS_TYPE IN (4,12)
AND MTA.TRANSACTION_SOURCE_ID = WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND WE.PRIMARY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND TRUNC(MTA.PROGRAM_UPDATE_DATE) >= START_DATE --Program date is the date used for the mapping as it reflects when the item was updated for the variance-
AND TRUNC(MTA.PROGRAM_UPDATE_DATE) <= END_DATE
AND GP.PERIOD_NAME =:Period --Keep or Remove Paraementer
AND PERIOD_SET_NAME = 'Std Accounting'
UNION ALL
SELECT MSIB.SEGMENT1,WDJ.WIP_ENTITY_ID, WE.WIP_ENTITY_NAME, WTA.BASE_TRANSACTION_VALUE VAL , WTA.ACCOUNTING_LINE_TYPE LINE, WDJ.CREATION_DATE,WDJ.DATE_COMPLETED,GP.PERIOD_NAME
FROM APPS.WIP_TRANSACTION_ACCOUNTS WTA,
APPS.WIP_DISCRETE_JOBS WDJ,
APPS.WIP_ENTITIES WE ,
MTL_SYSTEM_ITEMS_B MSIB,
GL_PERIODS GP
WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = 121
AND WTA.ACCOUNTING_LINE_TYPE = 2
AND WDJ.STD_COST_ADJUSTMENT_ACCOUNT = 758049
AND WDJ.STATUS_TYPE IN (4,12)
AND WTA.ORGANIZATION_ID = 121
AND WTA.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND WE.PRIMARY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND TRUNC(WTA.PROGRAM_UPDATE_DATE) >= START_DATE
AND TRUNC(WTA.PROGRAM_UPDATE_DATE) <= END_DATE
AND GP.PERIOD_NAME  >= ' JAN-19'
AND PERIOD_SET_NAME = 'Std Accounting'
)
GROUP BY WIP_ENTITY_NAME, WIP_ENTITY_ID ,SEGMENT1,LINE,CREATION_DATE,DATE_COMPLETED,PERIOD_NAME

The above query works fine in Toad, however when I run it in Power BI I get the following massage: Details: "Oracle: ORA-01008: not all variables bound "

Any idea why?

[Subin]

Hi @v-piga-msft 

Its realy helped for me..Thanks..

[v-piga-msft]

Hi @craig811 ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Daiani]

Hi,

I'm having the same problem, my query works well in oracle, but when I use it in Power BI, it generates the error ORA-01008. Any ideas?

Select

cod_motivo ,descr_motivo,CPF_TITULAR, CD_CONTRATANTE, NM_CONTRATANTE, CHAVE_BENEFICIAIO, NOME_BENEFICIARIO, CPF_BENEFICIARIO, U##IN_TIPO_PESSOA, CD_MODALIDADE, PRODUTO, NR_TER_ADESAO, ESTRUTURA, CD_USUARIO, DT_EXCLUSAO_PLANO, DT_INCLUSAO_PLANO, EVENTO, DS_EVENTO, MENSALIDADE, CUSTO, MES, ANO

from(

with base as (

select p.cd_contratante,c.nm_contratante,car.cd_carteira_inteira chave_beneficiaio,u.nm_usuario nome_beneficiario,u.cd_cpf cpf_beneficiario,

c.u##in_tipo_pessoa,u.cd_modalidade,prod.DS_GRUPO produto,

p.cd_modalidade||'-'||p.cd_plano||'-'||p.cd_tipo_plano estrutura,

u.nr_ter_adesao,u.cd_usuario,u.dt_exclusao_plano,u.dt_inclusao_plano,

(SELECT usuario.CD_CPF

                FROM seben.usuario usuario

               WHERE u.cd_modalidade=usuario.cd_modalidade

                 AND u.NR_TER_ADESAO=usuario.nr_ter_adesao

                 AND u.cd_titular=usuario.cd_usuario) cpf_titular ,

                       mot.cd_motivo    cod_motivo ,

       mot.ds_motivo    descr_motivo

from seben.usuario u

join seben.propost p on p.cd_modalidade = u.cd_modalidade and p.nr_ter_adesao = u.nr_ter_adesao

join seben.ter_ade t on p.cd_modalidade = t.cd_modalidade and p.nr_ter_adesao = t.nr_ter_adesao

join seben.contrat c on p.cd_contratante = c.cd_contratante and c.u##in_tipo_pessoa='F'

join seben.car_ide car on (car.PROGRESS_RECID = (Select max(progress_recid) From seben.car_ide a 

                                               Where a.CD_UNIMED     = 48

                                               And a.CD_MODALIDADE = u.CD_MODALIDADE

                                               And a.NR_TER_ADESAO = u.NR_TER_ADESAO

                                               And a.CD_USUARIO    = u.CD_USUARIO

                                               and rownum=1))

join  seger.ti_pl_sa tp on  p.cd_modalidade = tp.cd_modalidade AND p.cd_plano = tp.cd_plano AND p.cd_tipo_plano = tp.cd_tipo_plano  

left join emsesp.de_grup_prod prod on  tp.u_int_3 = prod.cd_grupo 

left join seger.motcange mot on mot.cd_motivo=u.CD_MOTIVO_CANCEL AND mot.in_entidade='MC'

where u.cd_sit_usuario >=5

and ( (u.dt_exclusao_plano is null) or (u.dt_exclusao_plano between '01-jan-2020' and '31-jan-2020') )

and u.cd_modalidade in (1,2,3,5,6,10,20,21,22,24,25,60,61,62,63,64,69) 

and ( u.dt_inclusao_plano <> case when u.dt_exclusao_plano is null then to_date('01/01/1901','dd/mm/yyyy') else u.dt_exclusao_plano end)

and u.nr_ter_adesao > 0 

and (

      (p.cd_contratante <> 676 and c.u##in_tipo_pessoa='F')

         or (   

              p.cd_contratante = 676

              and (p.cd_modalidade,p.cd_plano,p.cd_tipo_plano) not in ( (20,2,16),(20,2,18) ,(20,2,19),(20,8,10),(20,2,17),(20,6,77 )  )

             )

    )           

),  

mensalidades as (

select

b.cod_motivo ,b.descr_motivo,b.cpf_titular ,b.CD_CONTRATANTE, b.NM_CONTRATANTE, b.CHAVE_BENEFICIAIO, b.NOME_BENEFICIARIO, b.CPF_BENEFICIARIO, b.U##IN_TIPO_PESSOA, b.CD_MODALIDADE, b.produto,b.NR_TER_ADESAO, b.estrutura,b.CD_USUARIO, b.DT_EXCLUSAO_PLANO, b.DT_INCLUSAO_PLANO,

to_char(v.cd_evento) evento ,ef.DS_EVENTO ds_evento,v.vl_usuario mensalidade,0 custo,v.mm_referencia mes,v.aa_referencia ano

from base b 

join sefin.vlbenef v on v.CD_MODALIDADE = b.CD_MODALIDADE

                     and v.NR_TER_ADESAO = b.NR_TER_ADESAO

                     and v.AA_REFERENCIA = 2020

                     and v.MM_REFERENCIA = 1

                     and v.CD_USUARIO = b.cd_usuario

                     and v.CD_EVENTO in

('100','101','102','103','104','105','106','107','108','109','150','151','152','153','156','157','159','160','162','163','164','165','175','176','177','178','181','182','602','603','604','605','652','653','654','655','659','660','661','662','670','671','672','676','677','680','685','689','694','695','728','729','748','756','757','759','760','765','766','800','801','808','809','850','851','935','936')

join seger.evenfatu ef on v.cd_evento = ef.cd_evento And ef.IN_ENTIDADE = 'FT' 

),

custo as ( 

select

b.cod_motivo ,b.descr_motivo,b.cpf_titular ,b.CD_CONTRATANTE, b.NM_CONTRATANTE, b.CHAVE_BENEFICIAIO, b.NOME_BENEFICIARIO, b.CPF_BENEFICIARIO, b.U##IN_TIPO_PESSOA, b.CD_MODALIDADE, b.produto,b.NR_TER_ADESAO, b.estrutura,b.CD_USUARIO, b.DT_EXCLUSAO_PLANO, b.DT_INCLUSAO_PLANO,

  LPAD (TO_NUMBER (   LPAD (mv.cd_esp_amb, 2, '0')|| LPAD (mv.cd_grupo_proc_amb, 2, '0')|| LPAD (mv.cd_procedimento, 3, '0')|| mv.dv_procedimento ), 8, '0' )  evento,

         RPAD (amb.ds_procedimento##1, 60)                    ds_evento,

0 mensalidade,mv.vl_real_pago  custo,mv.nr_perref mes,mv.dt_anoref ano

from base b

join secon.moviproc mv on mv.cd_modalidade = b.cd_modalidade and mv.nr_ter_adesao=b.nr_ter_adesao and mv.cd_usuario = b.cd_usuario and mv.nr_perref =&mes and mv.dt_anoref = &ano

  left join seger.ambproce amb on amb.cd_esp_amb                = mv.cd_esp_amb

                                AND amb.cd_grupo_proc_amb        = mv.cd_grupo_proc_amb

                                AND amb.cd_procedimento          = mv.cd_procedimento

                                AND amb.dv_procedimento          = mv.dv_procedimento

where mv.u##nr_serie_doc_original NOT IN ('LCI', 'LCOB', 'LCH', 'LINT')

     AND mv.U##IN_LIBERADO_PAGTO<>3

     and mv.IN_LIBERADO_CONTAS<>9

union all

select

b.cod_motivo ,b.descr_motivo,b.cpf_titular ,b.CD_CONTRATANTE, b.NM_CONTRATANTE, b.CHAVE_BENEFICIAIO, b.NOME_BENEFICIARIO, b.CPF_BENEFICIARIO, b.U##IN_TIPO_PESSOA, b.CD_MODALIDADE, b.produto,b.NR_TER_ADESAO, b.estrutura,b.CD_USUARIO, b.DT_EXCLUSAO_PLANO, b.DT_INCLUSAO_PLANO,

        LPAD (mv.cd_tipo_insumo, 2, '0') || '-'|| LPAD (mv.cd_insumo, 8, '0') evento,

         RPAD (insumos.ds_insumo, 60)                        ds_evento,

0 mensalidade,mv.vl_real_pago  custo,mv.nr_perref mes,mv.dt_anoref ano

from base b

join secon.mov_insu mv on mv.cd_modalidade = b.cd_modalidade and mv.nr_ter_adesao=b.nr_ter_adesao and mv.cd_usuario = b.cd_usuario and mv.nr_perref in (1,2) and mv.dt_anoref = 2020

    join seger.insumos insumos on insumos.cd_tipo_insumo=mv.cd_tipo_insumo  

                                    AND insumos.cd_insumo=mv.cd_insumo

) 

select

cod_motivo ,descr_motivo,CPF_TITULAR, CD_CONTRATANTE, NM_CONTRATANTE, CHAVE_BENEFICIAIO, NOME_BENEFICIARIO, CPF_BENEFICIARIO, U##IN_TIPO_PESSOA, CD_MODALIDADE, PRODUTO, NR_TER_ADESAO, ESTRUTURA, CD_USUARIO, DT_EXCLUSAO_PLANO, DT_INCLUSAO_PLANO, EVENTO, DS_EVENTO, MENSALIDADE, CUSTO, MES, ANO

from(

select

cod_motivo ,descr_motivo,cpf_titular ,CD_CONTRATANTE, NM_CONTRATANTE, CHAVE_BENEFICIAIO, NOME_BENEFICIARIO, CPF_BENEFICIARIO, U##IN_TIPO_PESSOA, CD_MODALIDADE, PRODUTO, NR_TER_ADESAO, ESTRUTURA, CD_USUARIO, DT_EXCLUSAO_PLANO, DT_INCLUSAO_PLANO, EVENTO, DS_EVENTO, MENSALIDADE, CUSTO, MES, ANO

from mensalidades

union all

select

cod_motivo ,descr_motivo,cpf_titular ,CD_CONTRATANTE, NM_CONTRATANTE, CHAVE_BENEFICIAIO, NOME_BENEFICIARIO, CPF_BENEFICIARIO, U##IN_TIPO_PESSOA, CD_MODALIDADE, PRODUTO, NR_TER_ADESAO, ESTRUTURA, CD_USUARIO, DT_EXCLUSAO_PLANO, DT_INCLUSAO_PLANO, EVENTO, DS_EVENTO, MENSALIDADE, CUSTO, MES, ANO

from custo

)

group by

cod_motivo ,descr_motivo,CPF_TITULAR, CD_CONTRATANTE, NM_CONTRATANTE, CHAVE_BENEFICIAIO, NOME_BENEFICIARIO, CPF_BENEFICIARIO, U##IN_TIPO_PESSOA, CD_MODALIDADE, PRODUTO, NR_TER_ADESAO, ESTRUTURA, CD_USUARIO, DT_EXCLUSAO_PLANO, DT_INCLUSAO_PLANO, EVENTO, DS_EVENTO, MENSALIDADE, CUSTO, MES, ANO

)

Thanks in advance!

[v-piga-msft]

Hi @craig811 ,

By my research, normally the error is caused by the parameter you used in the query.

I'm afraid that your issue is caused by here:

AND GP.PERIOD_NAME =:Period --Keep or Remove Paraementer

---

Is the the parameters of Power BI? If so, I'm afraid this isn't supported by now. Please vote up this idea or create a new one.

In addition, you could have a reference of the similar threads which have been solved.

https://community.powerbi.com/t5/Desktop/Query-Parameters-Not-Bound-Oracle-Database/td-p/334384

https://community.powerbi.com/t5/Desktop/Power-BI-Desktop-Bind-Variables-in-Oracle-Queries/td-p/8101...

Best  Regards,

Cherry