Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ted.

Asked: January 31, 2001 - 7:37 pm UTC

Last updated: November 24, 2008 - 11:10 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

thomas,

how do i create a logon trigger to set sqlprompt to sid> ? so, when connect from one instance to another the prompt will tells me where am i ?

I tried execute immediate 'set sqlpromt to sid>' and it does not work.



and Tom said...

you cannot. SQLPlus is a tool to access the database. Its commands are implemented by it. The database is the database -- there is no way for the database to set a SQLPlus setting as the database is wholly unaware of SQLPlus.

What I do is use a login.sql file to set the prompt upon login. I've trained myself to use:

SQL> @connect

instead of

SQL> connect

where connect.sql is:

connect &1
@login

in that way, the prompt is accurate. If you are ever confused, just run

SQL> @login

to clear up any confusion.



Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

how about login.sql

A reader, July 18, 2001 - 5:18 pm UTC

But Tom how to create this login.sql.

How to set sysdate as sql prompt?

Chidambara Barathi A, April 04, 2002 - 3:10 am UTC

Hi Tom,

I want to set sysdate as sql prompt. How will i do it?

Thanx,


Tom Kyte
April 04, 2002 - 11:49 am UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:284615949318 <code>

shows how to set sqlprompt based on a query result.

If you only want time...

Andrew, April 04, 2002 - 1:17 pm UTC 

If you only want time then this will do.
SQL> set time on
10:17:27 SQL>

How to change Sysdate?

Alan, January 02, 2003 - 12:50 pm UTC

Tom,
I remember reading in this forum a way you showed to change the sysdate. I am trying to debug some queries which ran fine two months ago and are failing today. The queries use a lot of sysdate and I was wondering if I could change the sysdate on a development system and test if it runs.

Thanks

Tom Kyte
January 02, 2003 - 1:03 pm UTC

search for

"fixed_date"

on this site.

formatting _date variable

Gerardo, July 18, 2007 - 12:41 pm UTC

Dear Tom,

In 10g, how can I change the "_date" format before having the instance opened?

I wrote in login.sql:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD_HHMISS';


however when connecting to an idle instance I got an error
ORA-01034: ORACLE not available

I need it -or some other solution- in order to create a startup log file with the date and time in a custom format:

spool STARTUP&_DATE..log
STARTUP
spool off


Thanks in advance,
Gerardo

Tom Kyte
July 18, 2007 - 1:10 pm UTC

well, frankly, this is a waste - all of this is already recorded in the alert log - regardless of who starts the database or how (if someone decides not to use your script - what then???)

chicken and egg problem here - in order to alter your session, you need a database instance up and running.

$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII; export NLS_DATE_FORMAT=yyyymmddhh24miss; sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Jul 18 13:07:58 2007

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> prompt &_DATE
20070718130802


might work for you - set your NLS_LANG (correctly, for what YOU need it to be, not what I used necessarily) and NLS_DATE_FORMAT in the environment, then run sqlplus.

Setting SQLPrompt to SYSDATE

Ed, November 19, 2008 - 4:40 pm UTC

Your response here wasn't clear to me:

>> Followup April 4, 2002 - 11am US/Eastern:
>>
>> see
>>
>> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:284615949318
>>
>> shows how to set sqlprompt based on a query result.

It seems you can only set the prompt to a static date this way.

I tried by using the _date variable but I can only get the format I want if I run the command AFTER logging in - other wise it only shows the date, not timestamp:

#################################################

ME@MYDB> !cat login.sql
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE _DATE>"

ME@MYDB> @login
ME@MYDB 2008-11-19 15:15.03>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-bash-3.00$ sqlplus me

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 19 15:15:10 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ME@MYDB 19-NOV-08>

Tom Kyte
November 24, 2008 - 11:10 am UTC

that response - from 2002 - was written way before _date was available.

before you log in, there is no session. Hence there are no session settings. Hence the default format is used.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library