This SQL saved me from writing loopy code in a client-side application. I am using this SELECT to populate combo boxes.
Removing the first comma is not a problem.
I have left it on purpose – it adds an empty entry on top of each drop-down list.
alter session set current_schema = scott
select table_name, max_col_id col_count, col_list
select table_name, column_id,
over (partition by table_name) max_col_id,
select ss.table_name, column_name,
sys_connect_by_path(cc.column_name, ',') col_list
from all_tab_columns cc,
where owner =
sys_context ('userenv', 'current_schema')) ss
where cc.table_name = ss.table_name and
cc.owner = sys_context ('userenv', 'current_schema')
connect by prior cc.column_id = cc.column_id - 1 and
prior cc.table_name = cc.table_name and
prior cc.owner = sys_context ('userenv', 'current_schema')
start with column_id = 1)
where column_id = max_col_id
The output is copied directly from Toad:
A deserved praise from a modest weekend code warrior:
Oracle Apex Portal is a development tool that is always online.
It is accessible from anything with a browser.
It is platform independent.
It is free from security hassles of VPN and Remote Desktop.
It is free.
I do not feel like I work overtime on my weekend when I use apex.oracle.com for prototyping.
It is more like playing games online.
The Uber Browser demo was published on apex.oracle.com.
The application is long gone (Oracle regularly cleans up demo accounts), but the source code is still available,
It uses the low cost pagination query described in the previous post.
Since the apex.oracle.com site does not hide users schemas from each other – Uber Browser becomes a dangerous tool.
To protect tables of other developers I had to limit it to my own schema and two demo tables.
Under the hood: a good mix of Dynamic SQL with Oracle XML gadgets (DBMS_XMLQuery + XSLT).
Very few moving parts.
Here is a little entertaining project I dealt with lately – to write a generic table browser using Application Express.
Since the browser is generic, i.e. SELECT statement is not known until run time – I cannot use most of the RAD gadgets of the IDE and many useful APEX features, like the built-in pagination.
The solution is simple:
use Dynamic SQL,
select one page of rows at a time,
convert it into XML,
run through a style sheet using DBMS_XMLQuery and
get a complete HTML page at the end.
Handmade pagination is trivial – unless you select from a very large table.
Here is an example of SQL I came up with:
Surprisingly enough the error means what it says – a missing bind variable.
Not excusing the weird behavior of the (otherwise lovely) DBMS_XMLQuery package.
This query works fine in my Toad, in spite of the unusual place for a bind variable.
select :r_max from dual where rownum <= :r_max
If I try to use DBMS_XMLQuery to turn the query results into XML I get an error.
ctx := dbms_xmlquery.newContext(
'select :r_max from dual where rownum <= :r_max');
dbms_xmlquery.setBindValue(ctx, 'r_max', 10);
The error is:
oracle.xml.sql.OracleXMLSQLException: Missing IN or OUT parameter at index:: 1
The obscure initialization parameter named ASM_POWER_LIMIT has the range of values from
Is this a reference to the amplifier that goes to 11 from This is Spinal Tap?