Automation task - how to retrieve SID of active session

Follow / 13 Sep 2013 at 10:23am

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.

Replies

  • Follow / 13 Sep 2013 at 11:41am
    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
     
  • Follow / 13 Sep 2013 at 5:31pm
    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
     
    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.


    Image removed by sender.
  • Follow / 16 Sep 2013 at 7:27am

    >>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);

  • Follow / 17 Sep 2013 at 7:31pm

    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

  • Follow / 21 Nov 2014 at 1:21pm

    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"

  • Follow / 28 Nov 2014 at 2:31pm

    >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?

    :-)

  • Follow / 13 Feb 2015 at 12:44pm

    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.

  • Follow / 13 Feb 2015 at 12:44pm

    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.

  • Follow / 13 Feb 2015 at 4:49pm

    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.