3 Replies Latest reply: Nov 19, 2012 12:48 AM by Prabodh RSS

    ITEM EROR

    AquaNX4
      Good Morning:

      I am getting an error after trying to run an SQL statement with a bind variable. I have written numerous statement like these before, but this one is giving me this error:

      report error:
      ORA-01008: not all variables bound

      Application Express 2.1.0.00.39 platform

      SELECT T1.AUDIT_DATE "AUDIT DATE",
      T2.CLOCK_NUMBER "AUDITOR CLOCK #",
      T2.LAST_NAME
      || ', '
      || T2.FIRST_NAME "AUDITOR'S NAME",
      T4.SENSITIVE_ITEM "AUDITED ITEM",
      T3.SI_DISPO
      FROM TABLE TAB T1
      LEFT JOIN TAB T3
      ON T1.DISPOSITION = T3.DISPO_ID
      LEFT JOIN TAB T4
      ON T1.ITEM_ID = T4.LU_SI_ID
      LEFT JOIN TAB T2
      ON T1.ENTERED_BY = T2.EMP_ID
      WHERE T1.ENTERED_BY = :P12_AUDITOR

      The :P12_AUDITOR refers to an item that works off of a dynamic SQL select list which is:

      SELECT UNIQUE T2.LAST_NAME || ', ' || T2.FIRST_NAME D, T2.EMP_ID R
      FROM TAB1 T1
      LEFT JOIN TAB2 T2
      ON T1.ENTERED_BY = T2.EMP_ID
      ORDER BY 1

      Am I missing something here?
        • 1. Re: ITEM EROR
          Prabodh
          Hi *969700* ,
          I am getting an error after trying to run an SQL statement with a bind variable. I have written numerous statement like these before, but this one is giving me this error:
          report error:
          ORA-01008: not all variables bound

          Application Express 2.1.0.00.39 platform
          >
          Wow! That version is still in use!!
          >
          SELECT T1.AUDIT_DATE "AUDIT DATE",
          T2.CLOCK_NUMBER "AUDITOR CLOCK #",
          T2.LAST_NAME
          || ', '
          || T2.FIRST_NAME "AUDITOR'S NAME",
          T4.SENSITIVE_ITEM "AUDITED ITEM",
          T3.SI_DISPO
          FROM TABLE TAB T1
          LEFT JOIN TAB T3
          ON T1.DISPOSITION = T3.DISPO_ID
          LEFT JOIN TAB T4
          ON T1.ITEM_ID = T4.LU_SI_ID
          LEFT JOIN TAB T2
          ON T1.ENTERED_BY = T2.EMP_ID
          WHERE T1.ENTERED_BY = :P12_AUDITOR
          >
          This appears to be Report query.
          If it is indeed a report query then it will expect the :P12_AUDITOR item to have some value in the session state , other than null.
          >
          The :P12_AUDITOR refers to an item that works off of a dynamic SQL select list which is:

          SELECT UNIQUE T2.LAST_NAME || ', ' || T2.FIRST_NAME D, T2.EMP_ID R
          FROM TAB1 T1
          LEFT JOIN TAB2 T2
          ON T1.ENTERED_BY = T2.EMP_ID
          ORDER BY 1
          >
          Is the :P12_AUDITOR assigned a value before the Report Region is rendered?
          The steps you follow when you get the error are not clear. Do you get the error the first time the page loads? Or, do you submit the page , with the :P12_AUDITOR item selection, when you get the error?

          Run the page in Debug mode to see when and what values are assigned to P12_AUDITOR when you get the error.
          >
          Am I missing something here?>
          Cheers,
          • 2. Re: ITEM EROR
            AquaNX4
            Thanks for your response. It is indeed a report query, and even after I replaced :P12_AUDITOR with a static assignment, I would get this error. The error appeared when the page was rendering, and after clicking on "go", which alerted me to the fact that it had a problem with the item (P12).

            However, as I was awaiting some guidance from this forum, I started to rewrite the original SQL statement. One of the first changes I made was to remove the concatenate process in the select statement and PRESTO, it worked after that. I have no idea why that would make a difference, but it works like a charm now. (possibly a bug in the year 1874 version of APEX that I am running) :)
            • 3. Re: ITEM EROR
              Prabodh
              I do not think it is a bug that you have hit, it is too basic to be a bug.
              In all probability the page started working once the value of P12_AUDITOR was set in the session state.

              To confirm whether the issue is really fixed by removing the concat you should re-login into the application and check. And preferably directly from the application URL, not when running the application within Application Builder.

              Cheers,