Secured content area views - how to embed item links? - Oracle Application Server Portal

the content area views are really great for implementing a specialized search mechanism.
Unfortunately I need to display links to the items (url, text, file, ...) as well. And I need to display the folder link to that item as well.
How can I do it?
portal30.wwv_thinghtml.item_link and getitemurl didn't do it. Neither did portal30.wwsbr_link_util.get_folder_url work to get the folder link.
Any suggestions?
Best regards,

You'll have to figure out the structure of the URLs and format them explicitly. There's enough information in the content area views and underlying tables to format the URLs for most objects. Note that the documented content area views are secured (users will only see what they are authorized to see), whereas going directly against the underlying tables is not. Therefore, you should always try to use the views, or at least join to them if you need information directly from a table.
We don't document the URL structure because they can (and will) change from one release to the next.
For example, here is a procedure that lists subfolder links for a give parent folder:
create or replace
p_parentid IN VARCHAR2,
p_caid IN VARCHAR2)
sfURL varchar2(256);
imgSRC varchar2(20) := '/images/tfold.gif';
for c in ( SELECT,s.display_name
from portal30.wwsbr_all_folders s,
portal30.wwv_user_corners p
where =
and p.parentid = p_parentid
and s.caid = p.siteid
and p.siteid = p_caid )
sfURL := 'PORTAL30.wwv_main.main?p_cornerid='
|| || '&p_siteid=' || p_caid;
htp.p('<a href="' || sfURL
           || "><img src="/images/tfold.gif" border="0"></a>');
htp.p('<a href="' || sfURL || ">'
|| c.display_name || '</a>');
end loop;
when others then


How can I integrate a Filter..

I have made a costumized Report in SQl.
Now I would have a Filter funktion in this report.
Do anyone know how I can integrate it?
I mean a funktion like APEX_Item.Checkbox().
ps.: It isn't an interactive Report
so far
Philippe Gleiche 
I think you need to provide a bit more information about what you mean when you say that you want to define a filter.
The following thread, although mainly about using multiselect items, covers one implementation of a filter function in apex using dynamic SQL:
Selecting Multiples Values from Multiselect Box
You can apply variations of same method to implementing pretty much any type of 'filter' you wish.
However, your posting references the APEX_Item.Checkbox() function which is slightly different - you use this within the context of your select clause, as opposed to the where clause, and which by definition won't filter the result set.
You can however create an checkbox item in a region which you can reference in your query - I'm assuming that's what you really intend.
As I say, please provide more information if the above doesn't assist you. 
i am sorry i wasn't concretly enougth, and my english isn't very good
i have a function like these
* Return SQL to to query View for master data from APEX GUI
FUNCTION getApexReportSQL(i_n_table_id IN RETURN VARCHAR2 IS
t_table masterdata_table%ROWTYPE;
v_sql VARCHAR2(32767);
t_cols t_column_list;
t_table := getTableData(i_n_table_id => i_n_table_id);
t_cols := getTableColumnsFromMD(i_n_table_id => i_n_table_id);
v_sql := 'SELECT ';
-- Add Checkbox for selection
v_sql := v_sql || apex_tools.getCheckboxItem(i_n_item_id => c_check_column_id,
i_v_value => 'HASH_VALUE',
i_v_column_alias => c_check_column_alias);
-- Add ROW_ID
v_sql := v_sql || ', ' ||
apex_tools.getDisplayAndSaveItem(i_n_item_id => c_row_id_column_id,
i_v_value => c_row_id_column_name,
i_v_column_alias => c_row_id_column_alias);
-- Add Masterdata Set selection
v_sql := v_sql || ', ' || getApexMasterDataSetItem();
-- Add Rows
FOR i IN 1 .. t_cols.COUNT
v_sql := v_sql || ',' || apex_tools.getTextItem(i_n_item_id => to_char(c_predefined_columns + i),
i_v_text => t_cols(i).name,
i_n_max_length => t_cols(i).data_size,
i_v_column_alias => t_cols(i).NAME);
v_sql := v_sql || ' FROM ' || c_view_prefix ||;
-- Add new empty row
v_sql := v_sql || chr(13) || 'UNION ALL' || chr(13) ||
getEmptyRow(i_n_table_id => i_n_table_id, i_n_cols => t_cols.count);
RETURN v_sql;
END getApexReportSQL;
it generates me a generic DDL
now i search a apex_tool or apex.item function to integrate a filter similar the filter in the interactiv reports from APEX 
Okay you appear to have what looks like a pretty sophisticated looking generic query generator there (although it doesn't appear to be generating DDL as you suggest).
Judging by your questioning (please don't take offense!), I'm guessing that you didn't write it? Which makes me wonder if it might be better to write your query semi-statically i.e. directly reference the table you want with the appropriate select and where clauses. Either way, it might require a bit more effort to get it the way you want.
I still need to work out what it is you need to filter:
1. The columns being returned
2. The rows being returned (i.e. the result set)
The reason I ask is that, depending on which is required, you will have two different implementations.

Passing SQL Query to Procedure as Parameter

I'm trying to accomplish what seems so simple, yet I can't get it right. I want to pass an sql query via a parameter to a procedure. In that procedure, I want to loop through the results, and create options for a select list. I've tried using EXECUTE IMMEDIATELY, cursors, SYS_REFCURSOR, etc, but nothing seems to be quite right. Any ideas on how to accomplish this? Thanks!!PROCEDURE Dropdown(  nam IN VARCHAR2,  qry IN VARCHAR2,)IS  html VARCHAR2(1000);BEGIN  html := '<select id="test" name="test">';  /* ?? */  html := html || '<option value="' || val || '">' || descrip ||'</option>';  html := html || '</select>';  htp.p(html);  END Dropdown;
You seem to be reinventing the wheel.Step 1 - take a look at APEX.  it comes with the database.What you are describing is what APEX calls a List of Values (LOV)sign up for a free sandbox account on Step 2 - review APEX's features and capabilities with what you already have.Have you implemented Interactive Reports?What about graphs?Ability to build web pages for mobile devices?etc., etc. Step 3 - install APEX locally and try things out. Step 4 - if you really want to build your own version of APEXOracle Ref CursorsSpecifically, the last section. MK
32c7dec8-383b-4e7e-bb81-de75a79e425b wrote:
I want to pass an sql query via a parameter to a procedure.
How will you handle varying number of columns returned of varying datatypes?
Hi,This example works but I don´t know if it should be done this way because of performance issues.I agree with Mike, take a look at APEX and REF CursorsPROCEDURE Dropdown(  nam IN VARCHAR2,  qry IN VARCHAR2,)ISBEGIN execute immediate (' declare html varchar2(1000);begin html := '||''''||'<select id="test" name="test">'||''''||';for reg in ('||qry||') LOOP  html := html || '||''''||'<option value="'||''''||'|| reg.val ||'||''''|| '">'||''''||'|| reg.descrip ||'||''''||'</option>'||''''||';  END LOOP;    html := html || '||''''||'</select>'||''''||';  dbms_output.put_line(html);  end;');END Dropdown;Hope it helps
A variation would be to use DBMS_SQL to PARSE and DESCRIBE the results.This way you can check to make sure that you are getting a SELECT statement that returns only 2 columns. MK
This is my working implementation. Thanks to all! PROCEDURE buildDropdown(   sqlqry IN VARCHAR2)IS    TYPE curtype  IS REF CURSOR;    src_cur curtype;    curid NUMBER;    desctab DBMS_SQL.DESC_TAB;    colcnt NUMBER;    val VARCHAR2(500);    descrip VARCHAR2(500);BEGIN OPEN src_cur FOR sqlqry;curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);    FOR i IN 1 .. colcnt LOOP    DBMS_SQL.DEFINE_COLUMN(curid, i, val, 500);END LOOP; WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP    DBMS_SQL.COLUMN_VALUE(curid, 1, VAL);    DBMS_SQL.COLUMN_VALUE(curid, 2, descrip);    html := html || '<option value="' || val || '">' || descrip || '</option>';END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); END buildDropdown;
Is there a way to implement a similar solution when the query being passed in uses dual? For example, I want to be able to pass a query like this, and have it produce the same dropdown output: SELECT 'mydescrip' descrip, 'myval' val FROM dual UNION SELECT 'mydescrip' descrip, 'myval' val FROM dual Again, I've searched countless postings and documentation, and can't find an accurate implementation.
a query is a query.Have you tried using the query in question? Now, to simplify that query, (if your list is static) you could do a CREATE VIEW.However, I've learned that when Business says "yes, those will never change"They really mean "yes, those values will never change until we need to change them"This means that this should be in a table. but, please, please, PLEASE take a look at Application Express. MK

Tab Navigation via Direct Access URLS

I am experiencing Difficulty with direct access URLS to specific tabs and subtabs within a page:
I create an anchor tag with the following Direct Access URL
The following is the URL I am sent to when I click on it.,38950&_dad=portal&_schema=PORTAL
This URL does not display a page but displays a blank page.
By looking at where I should have been sent I can see that the correct URL should have been:,38668,79_38950&_dad=portal&_schema=PORTAL
Is this a known bug?
I am using Portal
Yes this is a known bug in 9.0.2 version.
This issue has been fixed in 9.0.4 release. 
This is a Fix that I am using until we upgrade our portal version... which we are not prepared to do.
( p_pagename IN varchar2,
p_objectname in varchar2,
p_url OUT varchar2)
-- To modify this template, edit file PROC.TXT in TEMPLATE
-- directory of SQL Navigator
-- Purpose: Briefly explain the functionality of the procedure
-- Person Date Comments
-- --------- ------ -------------------------------------------
temp_url varchar2(2000);
page_id number;
current_id number;
parent_id number;
site_id number;
-- Declare program variables as shown above
-- Determine Initial Parents and children etc
select id,SITEID into page_id,site_id from wwpob_page$ where name=p_pagename;
select id,PARENTID into current_id,parent_id from wwpob_page$ where name=p_objectname;
temp_url := '&_dad=portal&_schema=PORTAL';
while (parent_id !=page_id) loop
temp_url := to_char(site_id) || '_' || to_char(current_id) || temp_url;
select id,PARENTID into current_id,parent_id from wwpob_page$ where id=parent_id;
temp_url := ':' || temp_url;
end loop;
temp_url := ',' || to_char(site_id) || '_' || to_char(current_id) || temp_url ;
temp_url := '' || to_char(site_id) || ',' || to_char(page_id) || temp_url;
p_url := temp_url;
END; -- Procedure

Templating engine in PLSQL

Hi All!
If any body interested to use templating with PLSQL I wrote a small package for this purpose. Source is on: []
Use it if you want it for anything. 
hi - other than the obvious (generating templates of some description), do you have any more detail? 
user11226465 wrote:
Hi All!
If any body interested to use templating with PLSQL I wrote a small package for this purpose. Source is on: []
Use it if you want it for anything.What's it supposed to do.
The code contains no comments and the package spec is non-descriptive.
What is "templating" supposed to mean?
Downloaded... and now in the Recyclebin.... 
If You look procedure exampleTpl You can easily understand for what this package can be used.
Template is very simple: <tabel><tr><td></td>{th}<td>{$ci}</td>{\th}{tr}<tr>{td}<td>({$r} * {$c} = {$k})<\td>{\td}{\td}</tr>{\tr}</table>.
It si template for generating HTML table.
If You heard about Velocity or freeMaker in JAVA world or in PHP world templating is very common use to generate various kind of sources.
PROCEDURE exampleTpl
tplName CONSTANT tName := 'ht';
tplContentLen CONSTANT tPosition := 1000;
LF CONSTANT tName := CHR (10);
CR CONSTANT tName := CHR (13);
createTpl (
, '<table>'
|| LF
|| ' <tr>'
|| LF
|| ' <td></td>'
|| LF
|| '{th} <td>{$ci}</td>'
|| LF
|| '{\th}'
|| ' </tr>'
|| LF
|| '{tr} <tr>'
|| LF
|| ' <td>{$ri}</td>'
|| LF
|| '{td} <td>({$r} * {$c} = {$k})</td>'
|| LF
|| '{\td} </tr>'
|| LF
|| '{\tr}</table>');
startSec (tplName);
FOR c IN 1 .. 5
startSec (tplName, 'th');
assignVar (tplName, 'ci', TO_CHAR (c));
endSec (tplName);
FOR r IN 1 .. 5
startSec (tplName, 'tr');
assignVar (tplName, 'ri', TO_CHAR (r));
FOR c IN 1 .. 5
startSec (tplName, 'td');
assignVar (tplName, 'r', TO_CHAR (r));
assignVar (tplName, 'c', TO_CHAR (c));
assignVar (tplName, 'k', TO_CHAR (r * c));
endSec (tplName);
endSec (tplName);
endSec (tplName);
IF (LENGTH (getTplContent (tplName)) > tplContentLen)
DBMS_OUTPUT.put_line (SUBSTR (getTplContent (tplName), 1, tplContentLen) || ' ...');
DBMS_OUTPUT.put_line (getTplContent (tplName));
dropTpl ('ht');
END exampleTpl; 
You can see in package specification few procedures.
Template is containing sections and variables wich you can fill with any data. Sections can be embedded.
Tag for section is : {section_name}anythin between{\section_name} like XML or HTML tags.
Tag for variable is: {$variable_name}.
And this is all. Very easy and you must use only two construction. This package want to be a clone of PHP library for same purspuse []. I try to make is more user frendly and compact. I use it to generate a lot of different source files from database repository of our in house framework.
This is not close to Velocity or FreeMaker.This tools is very havy and complex and contain embedded language. This package is only for filling a lot of tags and sections in template.
PROMPT Create package PLTpl ...
PROCEDURE createTpl (tplName IN tName, tplContent IN tContent);
PROCEDURE logSec (tplName IN tName, secName IN tName := NULL);
PROCEDURE startSec (tplName IN tName, secName IN tName := NULL);
PROCEDURE assignVar (tplName IN tName, varName IN tName, varValue IN tContent := NULL);
PROCEDURE endSec (tplName IN tName);
FUNCTION getTplContent (tplName IN tName)
RETURN tContent;
PROCEDURE dropTpl (tplName IN tName);
PROCEDURE exampleTpl;
haattila wrote:
You can see in package specification few procedures.Not easily, unless you know what you're looking at.
The procedure/functions names don't lend themselves to being obvious as to what they do, and there's absolutely no comments in the package spec to indicate how to use it. All fine providing an example procedure, but without any comments in that, it's a case of people trying to go through it and see what it's supposed to do.
Perhaps some examples of what it would be useful for may help too.
Just trying to give some constructive criticism. 
Yes, you are right. Without comments is not easily. This is first working version and I didn't write any comment into package. I will try to describe every procedure and attache some examples to completing a hole picture. 
Before you put too much effort into this, you might want to have a look at the htf and htp packages. They have included a lot of html logic.
Instead of writeing all the tags by yourself, I think it would be wise to use those packages.
Furthermore I do not know how your package is ment to be used, but dbms_output is not an good method to create text at all.

function returning pdf link - need to add image along with PDF Link

Hello everyone,
I currently have a function that returns multiple PDF Links as a string with each PDF LInk on their own line. What I would like to do now is add a little PDF Icon before each link. I have used:
'<img src="#WORKSPACE_IMAGES#pdficon_small.gif" />' in the SQL and it works fine. If I move the code to the Function it returns nothing.
Here is my function:
create or replace function "FILE_NAME_LINK"
(xapp_id in VARCHAR2,
xapp_rec_id in VARCHAR2)
return VARCHAR2
xreturn_string varchar2(1000);
i int := 1;
     for c1 in ( select *
                    from MAT_FILE_STORE
                    where MAT_FILE_STORE.APP_ID = xapp_id and
                         MAT_FILE_STORE.APP_REC_ID = xapp_rec_id)
          if i = 1 then
               xreturn_string := '' || c1.ABSTRACT || '';
               xreturn_string := xreturn_string || '<br><br>' || '' || UPPER(c1.ABSTRACT) || '';
          end if;
          i:= i + 1;
     END LOOP;
     return xreturn_string;
I make the changes to the IF i = 1 section to include the icon like this:
          if i = 1 then
               xreturn_string := '<img src="#WORKSPACE_IMAGES#pdficon_small.gif" /> ' || c1.ABSTRACT || '';
               xreturn_string := xreturn_string || '<br><br>' || '<img src="#WORKSPACE_IMAGES#pdficon_small.gif" /> ' || UPPER(c1.ABSTRACT) || '';
          end if;
Compile it with no errors, check my application and I get no icons.
Any clue as two what is going on here? 
Display your report in your application and then do a view-source to look at the HTML of the page that's generated by Apex. Find the report region and look for the img tags. That should help you sort out whether you're getting the correct syntax to refer to the image files or not.
Hi rgarza28,
I face this problem before, and here's my solution.
the problem comes from that ApEx replace the #WORKSPACE_IMAGES# with the appropriate link only in its PL/SQL and SQL boxes not inside a schema PL/SQL code.
so I get around this and I pass the #WORKSPACE_IMAGES# as a string the schema function as a varchar2 parameter.
as follows:
create or replace function "FILE_NAME_LINK"
(xapp_id in VARCHAR2,
xapp_rec_id in VARCHAR2,
img_path in VARCHAR2)
return VARCHAR2and inside the function concat the img_path parameter as follows:
xreturn_string := '<img src="'||img_path||'pdficon_small.gif" /> ' || c1.ABSTRACT || '';and so on...
and when you call the function call it like this
Thanks Earl
After looking at the source and not finding the code I realized what was going on. I was working with the wrong function ! lol I found and added the code to the correct function and now works as I need it to. 
Glad you got it sorted out.