CODEWITHSUNDEEP
sqloracle-databaseoracle-apexireportora-01008
burakm
burakm

Reputation: 13

ORA-01008 Not all variables bound error on Oracle Apex application

I am trying to create a PDF in an Oracle APEX application. For this i am using ireport 5.6.0 version and create xml code and then using jrxml2pdf app that i found on the net to turn the xml code into pdf in my apex app. Many times I have managed to create pdf with this method and display it in my applications. Here is the problem, when i tried to put a pie chart on my pdf i've had an error that says ORA-01008 not all variables bound. But my code works fine on toad, also works fine on ireport's preview screen (i can see the pie chart and the values are correct). But after that I added this xml code to jrxml2pdf and tried to see the pdf I saw in ireport, this error occurs

Here is my sql query for the code. When I put values ​​instead of parameters the pdf works on apex too. so the problem is on variables not on the apps that i use. what may cause this error?

SELECT
    Results,
    SUM(Quantity) AS Quantity
FROM
    (
        SELECT
            CASE
                WHEN ddt.f_status = 'OK' THEN 'OK'
                WHEN ddt.f_status = 'NOK' OR ddt.f_status IS NULL THEN 'NOK'
            END AS Results,
            CASE
                WHEN ddt.f_status = 'OK' THEN 1
                WHEN ddt.f_status = 'NOK' OR ddt.f_status IS NULL THEN 1
                ELSE 0
            END AS Quantity
        FROM
            KT_QMS_HEADERS_V he
            LEFT OUTER JOIN KT_QMS_DETAILS_V dt ON dt.f_model_id = he.f_model_id AND dt.f_header_id = he.seq_id
            LEFT OUTER JOIN KT_QMS_DETECT_V ddt ON ddt.f_order = 1 AND dt.seq_id = ddt.f_detail_id AND ddt.f_hullno = $P{P13_HULLNO}
        WHERE
            he.f_model_id = $P{P13_MODEL}
    ) subquery
WHERE
    Results IS NOT NULL
GROUP BY
    Results

This is an edited version of my first code. I changed it to use the parameters only once. It still works fine on ireport preview. But still throws the same error on apex.

Upvotes: 1

Views: 296

Answers (1)

MT0
MT0

Reputation: 168806

If one application is using named bind variables then you only need to provide the two values for $P{P3_MODEL} and $P{P3_HULLNO} and the values will be provided for all variables matching those names.

If another application is using positional bind variables then you need to provide a value for every instance of bind variable and if a bind variable is repeated then you need to provide duplicates of those values (in the order in which they occur in the query).

Alternatively, rewrite your query to be simpler so that each bind variable only appears once.

I think this is the same as your query (but you have not provided any sample data so it is untested):

SELECT SUM(CASE WHEN dtt.f_status = 'OK' THEN 1 END) AS OK,
       SUM(CASE WHEN dtt.f_status = 'NOK' OR dtt.f_status IS NULL THEN 1 END) AS NOK
FROM   KT_QMS_HEADERS_V  he
       LEFT OUTER JOIN KT_QMS_DETAILS_V  dt
       ON     dt.f_model_id  = he.f_model_id
          AND dt.f_header_id = he.seq_id
       LEFT OUTER JOIN JOIN KT_QMS_DETECT_V ddt
       ON     ddt.f_order  = 1
          AND dt.seq_id    = dtt.f_detail_id
          AND ddt.f_hullno = $P{P3_HULLNO}
WHERE  he.f_model_id = $P{P3_MODEL}

Upvotes: 2

Related Questions