6 Replies Latest reply: Mar 13, 2012 5:32 PM by 706771 RSS

    TTITLE not displaying in spooled file

    706771
      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
          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
            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
              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
                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
                  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
                    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