4 Replies Latest reply: Jul 25, 2013 1:15 AM by chinsheng.toh RSS

    Querying EBS data from APEX through PL/SQL process


      Hi all,


      I have read through http://www.oracle.com/technetwork/developer-tools/apex/apex-ebs-wp-cabot-consulting-169064.pdf


      and    http://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-ebs-extension-white-paper-345780.pdf


      But I still do not have much idea on how do I query for data from ebs. What I am trying to achieve is to query for a table in EBS and put the data as a httpresponse


      then parse the data using javascript by creating a html region in APEX.


      Previously, I was creating an application process in the following manner




      element_List varchar2(4000) := '';






      for i in (select END_MEASURE from SCS_ELEMENT_DATA where ATTRIBUTE10 = :P9_ASSET_ID) loop


      element_List := element_List || i.END_MEASURE || ' ';


      end loop;


      exception when no_data_found then null;








      My table structure in EBS is something like this:


      XXEAM_LAM_ELEMENTS (lam_element_id, csi_instance_id, element_name, element_desc, label, type, continuous, reference, shared, start_measure, end_measure, lat_long)


      Would appreciate any advice as I'm new to both APEX and EBS.

        • 1. Re: Querying EBS data from APEX through PL/SQL process

          What is the exact question?  Presumably Apex is not installed in the EBS database, so you probably need a database link created on the APEX database to connect to EBS.


          Then you just query like:

          select * from table@dblinkname
          • 2. Re: Querying EBS data from APEX through PL/SQL process
            Tom Petrus

            Or if your apex installation is in the same DB, and your apex parsing schema is not APPS (shouldn't be, this is mentioned in the white paper) then you'll have to create synonyms for the objects you require in the APPS schema and provide your parsing shema the correct grants.

            We're just guessing. As Scott says, you didn't really throw a lot of usable info our way. You've read the whitepaper, great. Did you perform any other steps?

            • 3. Re: Querying EBS data from APEX through PL/SQL process

              Hi Scott and Tom,


              Sorry about this.


              My Apex is installed in the same instance as my EBS. I assume this means they are in the same DB? So if this is the case, can I just query with the EBS table name and the APEX application will know its EBS table and not APEX table?


              Because previously my query was based on APEX tables and now I have query based on my EBS table using an APEX application process. I couldn't really understand what's needed even after looking through the whitepaper. I do not need to create a view/report based on the queried data because my APEX page only has a HTML region where I put all my javascript code in.


              Hope this is clearer. Sorry for not being clear on my issue.




              • 4. Re: Querying EBS data from APEX through PL/SQL process

                Hi Guys,


                Sorry, this is my bad. I realised I can just query directly which the EBS table names. Sorry for all the troubles caused.