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

    Querying EBS data from APEX through PL/SQL process

    chinsheng.toh

      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

       

      declare

       

      element_List varchar2(4000) := '';

       

      begin

       

      begin

       

      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;

       

      end;

       

      htp.prn(element_List);

       

      end;

       

      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
          Sc0tt

          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
              chinsheng.toh

              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.

               

              Regards,

              CS

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

                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.

                 

                Thanks.

                 

                Regards,

                CS