This discussion is archived
4 Replies Latest reply: Jul 24, 2013 11:15 PM by TohChinSheng RSS

Querying EBS data from APEX through PL/SQL process

TohChinSheng Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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
    TohChinSheng Newbie
    Currently Being Moderated

    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
    TohChinSheng Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points