Report / Script variables improvements

Forums Products PL/SQL Developer Report / Script variables improvements

Print Thread

Report / Script variables improvements #63644 03/18/22 11:52 AM
Joined: Mar 2007 Posts: 110 Hazerswoude-Dorp, NL koja OP Member
OP koja Member Joined: Mar 2007 Posts: 110 Hazerswoude-Dorp, NL	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: Quote 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? Certainty of death, small chance of succes, What are we waiting for... (Gimly)

Re: Report / Script variables improvements koja #63648 03/19/22 09:34 AM
Joined: Aug 1999 Posts: 22,219 Marco Kalter Member
Marco Kalter Member Joined: Aug 1999 Posts: 22,219	Can you describe what your the goal is? Marco Kalter Allround Automations

Re: Report / Script variables improvements koja #63649 03/21/22 08:35 AM
Joined: Mar 2007 Posts: 110 Hazerswoude-Dorp, NL koja OP Member
OP koja Member Joined: Mar 2007 Posts: 110 Hazerswoude-Dorp, NL	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 by koja; 03/21/22 09:47 AM. Certainty of death, small chance of succes, What are we waiting for... (Gimly)

Re: Report / Script variables improvements

koja #63666 03/22/22 09:24 AM

Joined: Aug 1999

Posts: 22,219

Marco Kalter Offline

Member

Marco Kalter

Member

Joined: Aug 1999

Posts: 22,219

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

Marco Kalter
Allround Automations

Re: Report / Script variables improvements koja #63698 03/25/22 10:44 AM
Joined: Mar 2007 Posts: 110 Hazerswoude-Dorp, NL koja OP Member
OP koja Member Joined: Mar 2007 Posts: 110 Hazerswoude-Dorp, NL	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 by koja; 03/25/22 10:45 AM. Certainty of death, small chance of succes, What are we waiting for... (Gimly)

Moderated by support

Link Copied to Clipboard