4 Replies Latest reply: Aug 12, 2014 5:56 AM by 1001953 RSS

    Access page item value referenced from a cursor

    1001953

      Hi,

       

      I have written a process to insert records into a table.

      This process has a cursor which pulls the item name based on the app_id and app_page_id.

      How do I get the value of the item fetched by the cursor.

      DECLARE

      A NUMBER;

      b number:=0;

      CURSOR C IS select AI.ITEM_NAME ITEM,lABEL  from APEX_APPLICATION_PAGE_ITEMS AI

      WHERE ai.APPLICATION_ID=:APP_ID  and page_id=:APP_PAGE_ID;

      FOR R IN C

      LOOP

      IF v('R.ITEM') IS NOT NULL THEN --/* how do i access the item value as in here v('R.ITEM') .I tried using :r.item,':'||r.item,'v('''||R.ITEM||''')' but no success */

      INSERT INTO TEAM VALUES (A,R.LABEL,v('R.ITEM'),:APP_USER,SYSDATE);

      END IF;

      END LOOP;

      COMMIT;

      END;

        • 1. Re: Access page item value referenced from a cursor
          fac586

          1001953 wrote:

          Please update your forum profile with a real handle instead of "1001953".

          I have written a process to insert records into a table.

          This process has a cursor which pulls the item name based on the app_id and app_page_id.

          How do I get the value of the item fetched by the cursor.

          DECLARE

          A NUMBER;

          b number:=0;

          CURSOR C IS select AI.ITEM_NAME ITEM,lABEL  from APEX_APPLICATION_PAGE_ITEMS AI

          WHERE ai.APPLICATION_ID=:APP_ID  and page_id=:APP_PAGE_ID;

          FOR R IN C

          LOOP

          IF v('R.ITEM') IS NOT NULL THEN --/* how do i access the item value as in here v('R.ITEM') .I tried using :r.item,':'||r.item,'v('''||R.ITEM||''')' but no success */

          INSERT INTO TEAM VALUES (A,R.LABEL,v('R.ITEM'),:APP_USER,SYSDATE);

          END IF;

          END LOOP;

          COMMIT;

          END;

          v(r.item)

           

          The v function parameter is the item name, and r.item is the cursor record element containing the item name.

           

          (Do not write SQL and PL/SQL in uppercase. It isn't 1968 and they aren't COBOL.)

          • 2. Re: Access page item value referenced from a cursor
            1001953

            Thanks fac586.

            How about if I want to assign a value to the item I fetched in the cursor.

             

            Say I want to assign (select name from team)

            i tried,select name into v(r.item) from team where title=r.label

            and i also tried

            v(r.item):=(select name from team)

            but I am getting this error

            PLS-00306: wrong number or types of arguments in call to 'V'

            • 3. Re: Re: Access page item value referenced from a cursor
              fac586

              1001953 wrote:

               

              How about if I want to assign a value to the item I fetched in the cursor.

               

              Say I want to assign (select name from team)

              i tried,select name into v(r.item) from team where title=r.label

              and i also tried

              v(r.item):=(select name from team)

              but I am getting this error

              PLS-00306: wrong number or types of arguments in call to 'V'

              This shows that you have serious defects in your knowledge and understanding of basic PL/SQL syntax and the fundamental computing concepts of functions and indirection.

               

              v is a function like any other SQL function [for example sin() or upper()]. It takes the name of an application or page item as a parameter and returns the current session state value of that item (or null if the item does not exist). If the parameter is a PL/SQL variable such as r.item, then the value passed is the value of the variable. A value cannot be assigned to v(r.item), in the same way that a value cannot be assigned to sin(pi/2) or upper(surname).

               

              To set an item value using indirection, where the name of the item is stored as the value of another item or variable, use the apex_util.set_session_state API method:

               

              ...
              select name into l_name from team;
              apex_util.set_session_state(r.item, l_name);
              ...
              

               

              Suggest you familiarise yourself with the basics of PL/SQL and APEX development:

               

              • 4. Re: Access page item value referenced from a cursor
                1001953

                Thank you so much