Report / Script variables improvements

koja

Member³
Hi Marco,

I'm trying to get some longstanding wishes working and with some workarounds are almost there:

Code:
-- &<name="selowner" type="string" list="select username from dba_users where oracle_maintained='N' order by username" uppercase="yes">.&<name="seltable" type="string" list="select table_name from dba_tables where owner = :selowner order by table_name" uppercase="yes">
Select distinct &<name="colname" type="none" list="select column_name from dba_tab_cols where owner = :selowner and table_name = :seltable order by column_name" lowercase="yes">
from   &<name=ownerdeq type="none" list="1" default="select lower(:selowner) from dual" hidden="yes">.&<name="tabledeq" type="none" list="1" default="select lower(:seltable) from dual" hidden="yes">[+where="
where  "][where];

is as far as I get. Only since I replaced list with default I keep receiving two errors:
ownerdeq: Error executing SELECT statement:
ORA-01008: not all variables bound
and one for tabledeq.

Also the [+where="
where "][where] results in a second input form

Is there a way to hide the first 2 results? or another way I can do this?
 
Hi Marco,

In this specific case I am trying to build a template resulting in a simple query. I am working on an existing system, where I have to research the data all the time. A couple of smart templates can save me time. I start with getting the schema, from the schema I select a specific table(using previously selected schema) and from the table I select a specific column, using previously selected owner and table_name. After that I need all those variables as dequoted string in order to build the query. Maybe a couple of simple template functions could do the trick, like dequote, lower, upper etc.
This is a simple scenario, but I have other more complicated templates to generate package and type beginnings that require a similar constructs.
 
Last edited:
This example allows you to select an owner, a table of this owner, and fetches all matching table properties:

Code:
select t.* from all_tables t
where t.owner like &<name="table owner"
                     hint="The table owner (wildcards allowed)"
                     type="string"
                     default="select user from dual"
                     ifempty="%"
                     list="select username from all_users order by username">
and t.table_name like &<name="table name"
                        hint="The table name (wildcards allowed)"
                        type="string"
                        default="%"
                        ifempty="%"
                        list="select table_name from all_tables where owner like :table_owner order by table_name">
order by t.owner, t.table_name
 
Hi Marco, as I said I'm trying to get a bit further with templates. To make my point I selected a rather simple example. Basically what I want is templates for queries and methods I use frequently. What I'm trying to do is create an improved version of the following template:

Code:
select distinct [column_name]
from  [owner].[table_name]

where I have replaced column_name, owner and table_name with queries, so I can select them from the metadata.

It's not a big deal, but it would expand the template capabilities greatly if there were solutions for some of the issues, such as template functions lower, upper and dequote.
 
Last edited:
Back
Top