template with substitution variables

[Daniele]

Hi,
I would like to create a template or macro where the outcome of the procedure that I will write is the value of the column of a table in my DB.
Is it possible?
Like $OSUSER, $DATE,... that write a value and not the string "$DATE"

thanks

 

[patch]

If you use my template plugin (download fromhttp://bar-solutions.com) you can create templates with substitution variables:
'$OSUSER' => The name of the Operating System user.
'$DBUSER' => The name of the database user that is currently logged on.
'$DATE' => The current date.
'$TIME' => The current time.
'$CLIPBOARD' => Clipboard text.

Hope this helps.

 

[Daniele]

These variables already exist in pl / sql developer, except $CLIPBOARD.
It is not useful to retrieve the contents of the tables.

I need something like "Query variables":

[$QUERY seq_query =
select lower(object_name) from user_objects
where object_type = 'SEQUENCE'
order by object_name]
select [Sequence=$seq_query,...].nextval into [Variable name] from dual;

 

[Daniele]

The Sequence variable is a suggestion list, because the query is followed by ",...", implying that other values can be entered manually by the user. Query results can simply be viewed as comma separated lists of values. How???

 

[Marco Kalter]

I'm not quite sure what you mean, but in version 11.0 this might be possible. Templates have switched to the substitution variable syntax (old syntax still supported), so you can do things like this:

-- Created on &<name="Created"
                hidden="yes"
                default="select systimestamp from dual">
declare
  -- Local variables here
  i integer;
begin
  -- Test statements here
  [#]
end;

This example will insert the systimestamp value into the text resulting from this template:

-- Created on 04-DEC-14 12.52.18.772000 PM +01:00
declare
  -- Local variables here
  i integer;
begin
  -- Test statements here
  |
end;

 

[Daniele]

Version 11.0 is available?
I have downloaded the latest,10.0.5.1710

 

[Marco Kalter]

Version 11 is not yet available. We'll have a beta version later this month.

 

[Daniele]

Version 11 is not yet available. We'll have a beta version later this month.

What?
Today is 30 Dicember.

 

[IvanZ]

We'll have a beta version later this month.

I bet THEY definately got it

 

[Marco Kalter]

The beta will be available on January 5, 2015. A nice way to start the new year

 

[IvanZ]

Good News!
Happy New Year!

 

[Daniele]

Hi I have the v.11 Beta 3.
If I write

&<name="Created"
hidden="yes"
default="select systimestamp from dual">

I have only one result. For to have more results?
ex.
code:

&<name="Created"
hidden="yes"
default="Select 1,2 from dual">

result:

1 2

 

[Marco Kalter]

You need to create a query with 1 row and 1 field. In case of your example:

Select 1 || ' ' || 2 from dual

 

[Daniele]

I need more rows, like:

Select name
  from department;

result:

dany
jon
tom

 

[Marco Kalter]

You would need to create a query that returns this multi-line string as one field.

 

[Daniele]

You have any example about it?

 

[Marco Kalter]

On Oracle 11.2 you can use the listagg function. For example:

select listagg(ename, chr(10)) within group (order by ename) from emp where deptno = 10

On other database versions you may be able to use the wm_concat function or create your own aggregation function.

 

[Daniele]

OK, thanks.
Another problem with this template:

&<name="Table"
  type="string">

&<name="Index"
  list = "Select c.column_name, c.index_name
            from dba_indexes t, dba_ind_columns c
           where t.table_name = upper(:Table)
             and t.index_name = c.index_name
             and t.uniqueness = 'UNIQUE'"
  multiselect = "yes">

Error:

 

[Daniele]

In the default I can't insert bind var) variable.
If I whant to insert the name of the table dinamically, haw I can do?

 

[Marco Kalter]

It seems like a bug. We'll fix it.

 

[koja]

Hi I have the v.11 Beta 7.

1) default doesn't accept bind variable yet:
& => not all variables are bound :tst1
2) Text depending on non empty variable only possible with old method, f.i.:
function delete_ok
( p_[pk_colnam] [datatyp][+pk_colnam2="
, p_"][pk_colnam2] [datatyp2][+pk_colnam3="
, p_"][pk_colnam3] [datatyp3]
) return boolean;
Weak alternative (using suffix): &
(not possible in multiple locations...)
3) $CLIPBOARD would be nice to have

Cheers! Jan

 

[Marco Kalter]

We'll check it out.