4 Replies Latest reply on Oct 1, 2014 11:16 PM by Gary Graham-Oracle

    How to SPOOL &_something like in SQL*Plus

    BrunoVroman

      Hello,

       

      in SQL*Plus I can generate the name of a spool file with something like

       

      COLUMN logname NOPRINT NEW_VALUE _logname

      SELECT TO_CHAR( sysdate, 'YYYY_MM_DD_HH24MISS' ) || '.log' logname FROM dual;

      SPOOL &_logname

       

      I can't make this work in SQL Developer... (SQL Developer prompts me for "_LOGNAME?")

       

      Could anybody help me?

       

      Thanks in advance,

       

      Bruno Vroman.

        • 1. Re: How to SPOOL &_something like in SQL*Plus

          in SQL*Plus I can generate the name of a spool file with something like

           

          COLUMN logname NOPRINT NEW_VALUE _logname

          SELECT TO_CHAR( sysdate, 'YYYY_MM_DD_HH24MISS' ) || '.log' logname FROM dual;

          SPOOL &_logname

           

          I can't make this work in SQL Developer... (SQL Developer prompts me for "_LOGNAME?")

           

          Could anybody help me?

          Yes - RTFM!

           

          Of all people to ask a basic 'doc' question - I am surprised. You are one of the frequent contributors to the forums and have tried to help MANY others. So you know that many times a reply been either or both of: 1) RTFM or 2) what is your VERSION.

           

          The sql developer user guide for version 4 (and previous versions) has the answer:.

          http://docs.oracle.com/cd/E12151_01/doc.150/e12152.pdf

          http://docs.oracle.com/cd/E39885_01/appdev.40/e38414.pdf

          Read section 1.7.1 of the sql developer user guide it will explain that for you.

           

          1.7.1 SQL*Plus Statements Supported and Not Supported in SQL Worksheet

          Since the sql developer app is ALWAYS being improved it is possible that support may be added for newer versions but, sadly, you didn't post your version.

          • 2. Re: Re: How to SPOOL &_something like in SQL*Plus
            BrunoVroman

            Hello RP0428,

             

            I had already looked at this kind of info, no need to send me a RTFM in the face. Maybe I should have added in my question a more clear statement "I can do this in  SQL*Plus <...>; how can I do an equivalent in SQL Developer?" Then  sending the list of what is supported and what is not would have been irrelevant.

             

            But I realize that I had over-simplified my question... We can do a direct & substitution, but what I want to do is to have this done via my "login.sql" file.

            If we do directly

            col x new_value _x

            select 'c:\temp\aaa.log' x from dual;

            spool &_x

            select * from dual;

            spool

            spool off

             

            this works as expected (file C:\temp\aaa.log created, and containing

            > select * from dual

            DUMMY

            -----

            X  

            c:\temp\aaa.log

             

            But in fact what I would like is to achieve the "spool ..." done via a kind of "login.sql". For example: when I run the script

            connect scott/tiger@orcl1

            @login.sql

            select user from dual;

            spool

             

            with "login.sql" containing:

            SET TIMI ON ECHO ON TERM ON

            COLUMN logname NOPRINT NEW_VALUE _logname

            SELECT 'c:\temp\' || user || TO_CHAR( sysdate, 'YYYY_MM_DD_HH24MISS' ) || '.log' logname FROM dual;

            SET ECHO OFF HEA OFF TIMI OFF

            SPOOL &_logname

            SELECT 'script run with account ' || USER || ' on ' || TO_CHAR( sysdate, 'Dy DD-MON-YY' ) ||

                   ' at ' || TO_CHAR( sysdate, 'HH24:MI:SS' )

              FROM dual;

            SET ECHO ON HEA ON TIMI ON

             

            the session prompts me for "_LOGNAME?", and for example the output is:

            > connect scott/tiger@orcl1

            Connected

            > @login

            > set TIMI ON

            > set TERM ON

            > COLUMN logname NOPRINT NEW_VALUE _logname

            > SELECT 'c:\temp\' || user || TO_CHAR( sysdate, 'YYYY_MM_DD_HH24MISS' ) || '.log' logname FROM dual

             

            Elapsed: 00:00:00.015

            old:SPOOL &_logname

            new:SPOOL c:\temp\poor.txt

            script run with account SCOTT on Wed 01-OCT-14 at 19:41:01                                         

             

            > set HEA ON

            > set TIMI ON

            > select user from dual

            USER                        

            ------------------------------

            SCOTT                    

            Elapsed: 00:00:00.000

            > spool

            c:\temp\poor.txt

            Connection created by CONNECT script command disconnected

             

            (of course c:\temp\poor.txt is the value that I have given when prompted for _logname)

             

            About the version, yes, 4 is OK, I didn't put it in my question as I thought that this wouldn't change the solution (but of course anybody might tell me "in SQLDeveloepr >= 3.2 you can do like this", or "you need the version 4 to do ..."). ((in version 4 I can define the file "bruno.sql" as "to be run after each connection" but this is a detail))

             

            Best regards,

             

            Bruno

            • 3. Re: How to SPOOL &_something like in SQL*Plus
              BPeaslandDBA

              I'm not sure that this is possible in SQL Developer...at least not yet. I did do some searching of the SQL Dev documentation today but didn't find anything to help you out.

               

              IIRC, it was That Jeff Smith that made a comment that a future version of SQL Developer will support all of the SQL*Plus commands. Not sure if this would be part of that or not and we probably won't know until that future version comes out.

               

              Cheers,
              Brian

              • 4. Re: How to SPOOL &_something like in SQL*Plus
                Gary Graham-Oracle

                Running the same test case on SQL*Plus versus SQL Developer, then running the "col" command, we get...

                COLUMN   logname ON

                NEW_VALUE _logname

                NOPRINT

                whereas with SQL Developer 4.0.3..

                COLUMN  'logname' ON

                noprint

                So while the SQL Developer documentation says COL is not supported, it is more accurate to say it is not fully supported. If Worksheet's version of COL supported NEW_VALUE it would probably get the OP close to what he wants.  Since _logname is never populated, we must prompt for it as for any uninitialized &value.  As you note, there does seem to be an effort toward improved SQL*Plus support in the product, and I logged a bug.