SQL Developer

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.

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

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

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.