How to retrieve and use row(s) from database in HTML page?

Question

In my APEX application, I would like to use rows from query as variables, but I don't want to create items for each element. Those variables I will have to show in my custom HTML page.

For example this is query:

SELECT cells.id as id, cells.name as name, storage.capacity as capacity from cells, storage WHERE storage.capacity > 120

Then in my custom HTML code I would like to reference those rows, something like this(sorry I don't know the syntax of variables):

<div>#name[1]#</div><a href="p?#id[1]"...
<div>#name[2]#</div><a href="p?#id[2]"...

So how can I do it for single fetched row and for multiple rows? Which type of process and region to engage?

Dmitriy · Accepted Answer · 2014-10-29 17:10:33Z

1

If you don't want to create items for each element MANUALLY, you can use region of type Form on table or view. APEX creates them automatically. If you don't want to create items automatically, you can create region of type HTML, then go to Region Definition -> Identification, select Type - PL/SQL (anonymous block), Source - type your PL/SQL code there. Example:

declare
  ...
begin
  for i in (select text_col, rownum from mytable) loop
    htp.p(apex_item.text(rownum, text_col) || '<br>');
  end loop;
end;

You can pass to procedure htp.p any string variables. If they contain valid HTML, it will be displayed properly, otherwise you will see your HTML source code. Also you can use apex_item (http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_item.htm#AEAPI192) package to generate HTML code of required items.

edited Oct 29, 2014 at 17:10

answered Oct 29, 2014 at 16:50

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pablo Over a year ago

Dmitry, as always thanks for prompt response. Could you please bring an example how I store and reference records? Do I have to store query result into some collection(using SELECT/INSERT?), then use some syntax to access each record from htp.p? Just so I go in the right direction.

Dmitriy Over a year ago

I modified my answer and added there an example. In this way you store on the page PL/SQL code, that generates web page every time when user opens it. Page contain data, that present in DB at the generation moment.

Pablo Over a year ago

That works like a charm, although I used something like i.text_col inside loop. Thanks again!

Collectives™ on Stack Overflow

How to retrieve and use row(s) from database in HTML page?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related