Results 1 to 10 of 10

Thread: Login Script

  1. #1
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post Unanswered: Login Script

    Hi there !

    Guys, am falling in love with Oracle.. Being a kid, the more I devote time the more I wanted to play with it.. Anyway..

    I have a few queries regarding login script..

    1) Can I compose the following login script more systematically?

    Set time on linesize 200 wrap off
    Select
    Case When to_char(sysdate,'hh24') < 12 Then 'Good Morning Kid'
    When (to_char(sysdate,'hh24') > 13 and to_char(sysdate,'hh24') < 17) Then 'Good After Noon Kid'
    When to_char(sysdate,'hh24') >= 17 Then 'Good Evening Kid'
    Else 'Good Noon Kid' End " Message from Server" from dual
    /
    It appears little bit funny but... Is it possible that I can get greeting message only ? If I doesn't specify column alias it still shows column heading something like CASEWHENTO_CHAR..

    Using PROMPT command, I can prompt greeting message without any header but the message is static. I want dynamic message..

    2) Like login script, is it possible that one can compose 'exit' script. Like, when one exit from SQL Plus, it says 'Good Bye Kid', hold for few second and then close completely.

    Perhaps, question 2 is wired question but I guess there is nothing harm to ask...

    All suggestion are most welcome..

    TIA
    NewKid

    A Newkid in the block

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Before the query,

    SET HEADING OFF FEEDBACK OFF

    then set them on again after.

    There is no on-exit macro facility in SQL*Plus.

    btw, you could even save yourself some repetition with something like:
    Code:
    WITH greeting_info AS
        ( SELECT TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) AS time
               , 12 AS noon
               , 17 AS evening
          FROM   dual )
    SELECT CASE
               WHEN time < noon THEN 'Good Morning Kid'
               WHEN time BETWEEN noon AND evening THEN 'Good Afternoon Kid'
               ELSE 'Good Evening Kid'
           END
    FROM   greeting_info;
    Last edited by WilliamR; 12-17-04 at 20:15.

  3. #3
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post Thanks William..

    Thanks for your reply..

    Is it possible that I can get the informations/ report about all the users who are logged in before I did on the same day along with log-in time, logout time?

    Also, can you please let me know what exactly will happen when one issue EXIT or QUIT Command at SQL comman prompt ? Don't SQL Plus call stroed procedure having same name ?

    TIA
    NewKid

    A Newkid in the block

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As of your second question ... EXIT and QUIT command are used to exit the SQL*Plus environment.
    How do you mean, "call the stored procedure"? Did you try it? To call a procedure, you need to execute it (or put its name between a "begin" and "end"). Using "exit" (or "quit") as a single command will exit SQL*Plus.
    Code:
    SQL> create procedure exit is
      2  begin
      3    null;
      4  end;
      5  /
    
    Procedure created.
    
    SQL> execute exit;
    BEGIN exit; END;
    
          *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00376: illegal EXIT statement; it must appear inside a loop
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    SQL> exit
    Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production
    
    C:\
    Procedure named "quit" would, however, exit the SQL*Plus with usage message.

    BUT: I wouldn't advice you to name procedures by SQL command names; it causes too much confusion.

  5. #5
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post Hello Littlefoot

    Littlefoot, I guess, you didn't get my questions correctly...

    All I want to know what exactly will happen INTERNALLY when one fire 'Exit' or 'QUIT' command at command prompt ? As l know that it will kickoff from SQL PLUS Envirnoment. But what exactly the QUIT and EXIT are ? Are they EXE file which can't be altered ? OR Are they Stored Procedure which can be altered? Or something else..

    In other words, as soon as we connected to Oracle, Oracle fire LOGIN.SQL file in which we can mention whatever we want and that will be executed before we get the command prompt.

    Like LOGIN.SQL, do we have LOGOUT.SQL or something else ?

    Thanks Little foot !
    NewKid

    A Newkid in the block

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    No, EXIT/QUIT are just built into the sqlplus executable. There is no logout.sql.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, didn't get the point
    It's just like William told you ... you can't alter EXIT / QUIT commands. That sounds like "can I alter a CREATE TABLE command?"; of course you can not However, if I remember well, QUIT (I'm not sure about EXIT) can have parameters when used in batch job. I should check the literature to explain it better (if you need it).

  8. #8
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post

    Quote Originally Posted by Littlefoot
    Obviously, didn't get the point
    It's just like William told you ... you can't alter EXIT / QUIT commands. That sounds like "can I alter a CREATE TABLE command?"; of course you can not However, if I remember well, QUIT (I'm not sure about EXIT) can have parameters when used in batch job. I should check the literature to explain it better (if you need it).
    Please do.. If you can, Littlefoot..

    Also, Do you know when glogin.sql will fire ? What is the basic difference between glogin.sql and login.sql ?
    NewKid

    A Newkid in the block

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here is a page that'll explain all about SQL*Plus commands (EXIT is one among them).

    This document contains info about (g)login.sql file(s) you're interested in.

  10. #10
    Join Date
    Dec 2004
    Location
    Memphis
    Posts
    25

    Post Thanks Littlefoot..

    Littlefoot, Thanks for the URL's..
    NewKid

    A Newkid in the block

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •