3 Replies Latest reply: Jul 22, 2013 12:40 PM by Joe Upshaw RSS

    create a dinamic LOV using string substitution

    Ingenio

      Hello

      I need to create a List of Values as follows using my application item CALLED GLOBAL_SCHEMA

       

      GLOBAL_ESCHEMA := RPSENV  is set on load trigger.

       

      select N_COUNTRY d, COD_COUNTRY r

      from V('GLOBAL_ESCHEMA').COUNTRIES@DB_APEX_RPS

      order by 1

       

      do you have suggestions for me?

      please help.

       

      ____________________________________________

      note:

      select N_COUNTRY d, COD_COUNTRY r

      from RPSENV.COUNTRIES@DB_APEX_RPS

      order by 1

       

      WORKS GOOD

        • 1. Re: create a dinamic LOV using string substitution
          Jj Mahe -Oracle

          I think that this is not possible in PL/SQL, to have indirection in identifiers via variables. In relational spirit, I would suggest that you add an attribute, let say "country_group",

          and store all your countries in a single table (or view) and then simply build your LOV query with a where clause :

          :GLOBAL_ESCHEMA := 'RPSENV' ;

          ...

          ... where country_group = :GLOBAL_ESCHEMA ...

          ...

          Not exactly what you are looking for, but a direction to the solution.

          • 2. Re: create a dinamic LOV using string substitution
            Ingenio

            What I did,

            Created the global item on Shared components> application items called global_schema  with no restriction so I can send the value by the browser.

            every query I use, like this: select col1, col2 from &GLOBAL_SCHEMA..table1@dblink_db1 where col1 = 1;

            note that there is double period  before table1, its because &ITEM. stores the value of the schema user owner of table1, so think of RPSENV.TABLE1  ==> &ITEM..table1

             

            and it works!

            • 3. Re: create a dinamic LOV using string substitution
              Joe Upshaw

              Not sure why I would need this but, I did want to say that that is pretty cool!

               

              Thanks for sharing the solution.

               

              -Joe