Run query: ORA-01008: not all variables bound

Help Activities
, , ,
1

Hello
i have simple query where i need to pass the parameter
im hitting this issue

Run query: ORA-01008: not all variables bound

having the message_ID as input
any idea?
hazem

2

Can you share your exact query. As per this may be this is an oracle query right ?

3

@Hazem_Saleh

are you using @ syntax to pass values?

is the name exactly matching?

cheers

4

Hello Both
im using : its oracle query

@"WITH ACC_MASTER_Entries AS (
    SELECT DISTINCT
        CASE 
            WHEN acc.EVENT = 'REVL' THEN 'REVERSED'
            WHEN ft_ret.CONTRACT_REF_NO IS NOT NULL THEN 'RETURNED'
            WHEN p.PAYMENTINSTRUCTION_ID IS NOT NULL THEN 'RETURN_PENDING'
            ELSE 'COMPLETED'
        END AS Payment_status,
        acc.TRN_REF_NO,
        acc.EVENT,
        acc.TRN_CODE,
        acc.AMOUNT_TAG,
        acc.TRN_DT,
        SUBSTR(ft.ULT_BENEFICIARY1, 6) AS CreditorAcc,
        CAST(ft.ULT_BENEFICIARY2 AS VARCHAR2(4000)) AS CreditorName,
        CAST(ft.ULT_BENEFICIARY3 AS VARCHAR2(4000)) AS CreditorAddress,
        p.PAYMENTINSTRUCTION_ID AS SwubsReturnId,
        ft_ret.CONTRACT_REF_NO AS ContractRefForReturn
    FROM FCUBS.ACTB_HISTORY acc
    INNER JOIN FCUBS.CONTRACT_MASTER ft
        ON ft.CONTRACT_REF_NO = acc.TRN_REF_NO
    LEFT JOIN FCUBS.FTTB_CONTRACT_MASTER ft_ret
        ON ft_ret.USER_REF_NUMBER = 'R' || SUBSTR(acc.TRN_REF_NO, 2)
    LEFT JOIN FINTRANSCOM.PAYMENTINSTRUCTION p
        ON ft_ret.CONTRACT_REF_NO IS NULL
       AND p.EXTERNAL_PAYMENT_ID = 'R' || SUBSTR(acc.TRN_REF_NO, 2)
    WHERE acc.AC_NO      = :bc_account
      AND acc.AC_BRANCH  = '010'
      AND acc.DRCR_IND   = 'C'
      AND acc.FCY_AMOUNT = :amount
      AND acc.TRN_DT BETWEEN :msg_received_date - :lookbackDays 
                         AND     :msg_received_date
)
SELECT *
FROM ACC_MASTER_Entries
WHERE (:originalCreditorName IS NULL OR CreditorName = :originalCreditorName)
  AND (:originalCreditorAccount IS NULL OR CreditorAcc = :originalCreditorAccount)"

i checked the name and of course the type im even trying to pass wrong values to test but stuck on this error

it has only 6 parameter and have them as input

image
image1192×148 5.56 KB

Thanks

5

Hi @Hazem_Saleh

Could be your query uses some parameters more than once.

Oracle counts each appearance as a separate parameter — even fro same name.

Try if you can Change the WHERE condition so each parameter appears only once, like this:

WHERE CreditorName = NVL(:originalCreditorName, CreditorName)
AND CreditorAcc = NVL(:originalCreditorAccount, CreditorAcc)

4 Likes
Forum Engagement Daily Reports
7

Super thanks never noticed this

:face_blowing_a_kiss:

1 Like
8

Great to hear it fixed the problem, please mark as solution so it can help others too. :slight_smile:

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.