This discussion is archived
6 Replies Latest reply: Mar 13, 2012 3:32 PM by 706771 RSS

TTITLE not displaying in spooled file

706771 Newbie
Currently Being Moderated
Dear Members :

I have sql script which starts with <Whenever Sqlerror ...>, then spooling the rest which includes - TTITLE and couple of SQL statements.
The problem is if all SQL statements execute correctly then TTITLE is displayed in the spool file , but if anything goes wrong, I mean in any of those SQL statements the errors
are spooled but not the <TTITLE>.

Is this natural behavior or missing something - could you please tell ?

Thanks in advance.
Atanu
  • 1. Re: TTITLE not displaying in spooled file
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Atanu,

    TTITLE tells SQL*Plus what to print on each page of query results . The title is not printed on other occassions. For example, PROMPT statements don't cause the title to print, neither do error messages.
    I can't reproduce the problem where an error causes titles ot to print. For eample, the script below has 2 queries that produce results (each less than a full page long):
    WHENEVER  SQLERROR  CONTINUE ROLLBACK
    
    SPOOL     &output_folder\fubar.lst
    
    
    TTITLE     CENTER "***** Testing TTITLE *****"     SKIP 2
    
    
    PROMPT     =====  Statement #1 works  =====
    
    SELECT     *
    FROM     scott.dept;
    
    PROMPT     =====  Statement #2 fails  =====
    
    SELECT     firbolig
    FROM     scott.dept;
    
    PROMPT     =====  Statement #3 works  =====
    
    SELECT     SYSDATE
    FROM     dual;
    
    
    SPOOL     OFF
    and it prints the title 2 times, as expected:
    =====  Statement #1 works  =====
    
                               ***** Testing TTITLE *****
    
    DEPTNO DNAME          LOC
    ------ -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
    
    =====  Statement #2 fails  =====
    SELECT     firbolig
               *
    ERROR at line 1:
    ORA-00904: "FIRBOLIG": invalid identifier
    
    
    
    Rollback complete.
    
    =====  Statement #3 works  =====
    
                               ***** Testing TTITLE *****
    
    SYSDATE
    -----------
    12-Mar-2012
  • 2. Re: TTITLE not displaying in spooled file
    706771 Newbie
    Currently Being Moderated
    Thanks Frank and exactly so. So if in my script all SQL statements fail TTITLE will never print, as is happening in my case.

    Could you please help me understand rationally - what "Whenever SqlError ..." to do with TTITLE ? Is TTITLE a sql statement ? Isn't it expected that TTITLE will do it's job that is prinitng one time and let "Whenever SqlError ..." take care of sql statements and do it's job ?

    Thanks very much.
    Atanu
  • 3. Re: TTITLE not displaying in spooled file
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Atanu,
    user11184124 wrote:
    Could you please help me understand rationally - what "Whenever SqlError ..." to do with TTITLE ?
    I don't think "WHENEVER SQLERROR ..." has anything to do with TTITLE. Post a script that shows what you think is the relationship.
    Is TTITLE a sql statement ?
    No, it's a SQL*Plus statement. Whenever you want to know if something is a SQL statement or not, look it up in the SQL language manual.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/index.htm
    Isn't it expected that TTITLE will do it's job that is prinitng one time and let "Whenever SqlError ..." take care of sql statements and do it's job ?
    Yes, that's what I expect, and, as I said before, that seems to be exactly what it's doing. If there's something you don't understand about TITTLE, or about WHENEVER SQLERROR, or about both together, then post an script that people can run to see exactly what you mean.
  • 4. Re: TTITLE not displaying in spooled file
    706771 Newbie
    Currently Being Moderated
    Hi Frank :

    Thanks for your time.
    Your example will be good one, as mine is similar. Now the questions, based on your example.

    [1] When I write a batch like as you shown, I expect TTITLE to print just once (per page) along with output of sql statements execution (#1, #2 and #3), as long they fit in a page.

    [2] In case all 3 (#1, #2 and #3) fail (in my case), TTITLE will never print (as you have shown for #2 failure) - but I still want TTITLE to print "label_YYMMDD_HHMISS" and errors beneath. That way I would know that the batch produced with title [xyz_datetime_at_time_of_execution] was in error.

    [3] yeah, "Whenever ..." clause is nothing to do with TTITLE, as I was thinking earlier.

    In nut shell -
    I expect TTITLE to print just once what it's asked to, whether sql statement(s) fail or succeed. Is this possible ?

    Thanks very much.
    Atanu
  • 5. Re: TTITLE not displaying in spooled file
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Atanu,
    user11184124 wrote:
    ... In nut shell -
    I expect TTITLE to print just once what it's asked to, whether sql statement(s) fail or succeed. Is this possible ?
    TTITLE was designed to print automatically with query results. If you want something that prints when you tell it to, then don't use TTITLE.
    PROMPT prints something whenever you tell it to. Why not use PROMPT?
  • 6. Re: TTITLE not displaying in spooled file
    706771 Newbie
    Currently Being Moderated
    Hi Frank :

    Yeah PROMPT is there for printing. I was trying TTITLE for it's flexibility in terms of formatting, positioning, skipping etc.
    I referred - http://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm#1014115 and never found there, that TTITLE is tied up with every single SQL command execution and that it prints depending on success or failure of sql statement.

    Thanks very much.
    Atanu

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points