Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using a variable db link for an interactive report

John Kolden-OracleNov 5 2013 — edited Nov 7 2013

I have a requirement where users will select an instance (db link) from an apex LOV and the data in an interactive report will query based on the db link selected.

I can use a db link in an interactive report query but only if I provide the exact name of the link. If I do something link select * from foo@:P12_DBLINK it doesn't work.

It seems my only option is to use something like apex_collection.create_collection_from_query and then I can build my query with dynamic sql and use a variable for the dblink name. Then I just create an IRR based on my collection.

Does that sound like that correct approach and/or am I missing anything?

Any suggestions are most appreciated,

john
This post has been answered by Vite DBA on Nov 6 2013
Jump to Answer

Comments

TexasApexDeveloper

You can, but why exactly do you want to query across a dblink?  I would be leery of doing such due to speed issues and also trying to pull across clobs/blobs across the dblink..

Thank you,

Tony Miller

LuvMuffin Software

John Kolden-Oracle

Thanks Tony -- it's just the business requirement. There aren't many records so performance should not be an issue.

John Kolden-Oracle

I guess I have it working ok. I am able to create a region of type 'PL/SQL Dynamic Content' and do something like this and it works. As I change the dblink select list the report refreshes with data from that instance. Pretty cool! I just can't believe it's this much work to do this. This is just one column of data and I have about 10 columns that I need to display:

declare


type array_t is varray(100) of number;


array array_t;


l_sql varchar2(4000);


begin


l_sql := 'SELECT credit_card_trxn_id FROM EXM_CREDIT_CARD_TRXNS@'||:P4_DBLINK;


execute immediate l_sql BULK COLLECT INTO array;




for i IN 1..array.count


LOOP


 HTP.p (array(i));


 HTP.br;


END LOOP;


end;
TexasApexDeveloper

Instead of this, why not try this approach: Oracle APEX Interactive report based on PLSQL function | Oracle Application Express

Thank you,

Tony Miller

LuvMuffin Software

1 person found this helpful
Vite DBA
Answer

Hi John,

why not create a classic report based on a PL/SQL function returning a query.

Andre

Marked as Answer by John Kolden-Oracle · Sep 27 2020
TexasApexDeveloper

Because people like the way interactive reports look & feel.. and with the code from the posted article, you can get that in an interactive report..

Thank you,

Tony Miller

LuvMuffin Software

Vite DBA

Just providing options Tony, all the features of an IR may not be that important in this case.

Andre

John Kolden-Oracle

Thank you both!! I knew there was a place where I could define plsql returning sql, but I couldn't find it anywhere!! I am going to go with that for now, and will check out the link that Tony provided if I have time before we roll this out. Thanks again!

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 5 2013
Added on Nov 5 2013
#apex-discussions
8 comments
1,229 views