Toad World® Forums

Automation task - how to retrieve SID of active session

Hello.

I would like to make automation task that export DDL to specific directory. I would like to run it against 4 databases;

I tried to use variables %ACTIVESESSIONDB% but it return the same value as database for which I build script (even if I run it with option - Run with connections…)

Have you solved similar problem ?

I make it on Toad For Oracle 12 64bit.

Here’s a script containing a technique that I use – it creates a script titled sid_name_parms.txt:

set term off

COL sid NOPRINT new_value sid

SELECT instance_name sid from v$instance;

set term on

set trimout on

set trimspool on

set linesize 256

set pagesize 999

set feedback off

col name format a40 heading ‘Name’

col value format a132 heading ‘Value’

spool &sid._parms.txt

select NAME, VALUE

from v$parameter

where value not like ‘%%’

and value not like ‘%/%’

and name not like ‘log_archive_dest%’

order by name;

spool off

I was going to say right-click on the action and create a parameter file then build out your list of comma-delimited connection names but a quick glance at
the code shows that export ddl doesn’t support the connection key value in the parameter file.

It’s not immediately apparent to me why it doesn’t.

Some of them do. Execute script, for instance.

Maybe on some actions which have a lot of version specific parameters we made the assumption that it wouldn’t likely work across databases. But even in that
case it would be of value for the same database but different schemas/connections. I think there’s room for us to build those out more in time.

From: przemyslaw.roznowski [mailto:bounce-przemyslawroznowski@toadworld.com]

Sent: Friday, September 13, 2013 5:24 AM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Automation task - how to retrieve SID of active session

Automation task - how to retrieve SID of active session

Thread created by przemyslaw.roznowski

Hello.

I would like to make automation task that export DDL to specific directory. I would like to run it against 4 databases;

I tried to use variables %ACTIVESESSIONDB% but it return the same value as database for which I build script (even if I run it with option - Run with connections…)

Have you solved similar problem ?

I make it on Toad For Oracle 12 64bit.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

But even in that case it would be of value for the same database

but different schemas/connections.

I need it for example to compare code from 4 databases.

To compare I use WinMerge so I need to export source code of packages and compare it.

So for me it will be big help to have ability to parametrize connection string in parameter file

I wish I will be in TOAD in future (I hope in the near future)javascript:void(0);

Here is how to retrieve several values, available to any user, connected to database, regarding DB version:

SELECT user,

 (SELECT sys_context('USERENV', 'INSTANCE') FROM dual) || ' ' ||

 (SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual) instance,

  dbms_debug_jdwp.current_session_id sid,

  dbms_debug_jdwp.current_session_serial serial#,

 (SELECT sys_context('USERENV', 'SESSIONID') FROM dual) AUDSID

FROM dual;

USER INSTANCE SID SERIAL# AUDSID


SYS 1 xe 221 137 4294967295

I installed new version of TOAD 12.6

I hoped that it would becorrected. But no.

In my opinion TOAD Automation for ORACLE is bad tools.

I’m not able to make apps that for multiple connection exports DDL for the same packages but to different folders.

One Actions work with variables(Export Dataset) another not (export DDL).

I only waste a time for something that is described as “Saving You Hours”

In my opinion TOAD Automation for ORACLE is bad tools.

This is not nice … even thought you do not know how to use it.

Now, I do not understand now what is your problem?

:slight_smile:

Imagine that you have to problem to solve. I need to export some packages from 4 databases by one click (to save time - I need do this multiple times). Packages from databases should be separate directories called as SID of database… I found tool called automation designer and action Export DDL in TOAD. I read something about variables and want to use it in “export DDL” because it was that I need (I thought that). There was no examples about using variables in documentation but i found examples for using variables in Export Dataset action .

So I tried use the same it in Export DDL.

I construct loop in automation designer

LOOP (over 4 connection strings)

SID = SID(DB1)

Export DDL c:\temp$SID\

end loop

And I started to test It. I have to make many attempts and reconfiguratios to realize that Export DDL can’t be use in loop over connection string and can’t use variables in PATH to export .

So I wasted my time because there was no information about using (or not using) variables and parametrized connection strings in Export DDL.

Imagine that you have to problem to solve. I need to export some packages from 4 databases by one click (to save time - I need do this multiple times). Packages from databases should be separate directories called as SID of database… I found tool called automation designer and action Export DDL in TOAD. I read something about variables and want to use it in “export DDL” because it was that I need (I thought that). There was no examples about using variables in documentation but i found examples for using variables in Export Dataset action .

So I tried use the same it in Export DDL.

I construct loop in automation designer

LOOP (over 4 connection strings)

SID = SID(DB1)

Export DDL c:\temp$SID\

end loop

And I started to test It. I have to make many attempts and reconfiguratios to realize that Export DDL can’t be use in loop over connection string and can’t use variables in PATH to export .

So I wasted my time because there was no information about using (or not using) variables and parametrized connection strings in Export DDL.

I noticed that this is an old thread, so I started at the top. I was able to reproduce your problem using %ACTIVESESSIONDB% and “Run with Connections”. This is fixed for next beta.