?
Solved

How to use TTITLE/BTITLE/LEFT/RIGHT(reporting commands) in PL SQL program

Posted on 2012-08-15
2
Medium Priority
?
1,921 Views
Last Modified: 2012-08-16
How to use TTITLE/BTITLE/LEFT/RIGHT(reporting commands) in PL SQL program.

I tried using in pl/sql program top title/btitle is working or coming up.
When used with sql every thing is working as expected.

PL/SQL program used
===============
set serveroutput on
set head on
TTITLE CENTER "THE XXX LIMITED" SKIP 3 -
LEFT "TEST" -
RIGHT "ANOTHEAR TEST" SKIP1 -
LEFT "======================"
declare
        empno emp1.emp_no%type;
        name varchar2(20);
        sal1 number;
        begin
                select emp_no,emp_name,sal into empno,name,sal1 from
                emp1 where emp_no='125';

                if (sal1>2000.00) then
                        update emp1 set sal=sal1*0.2 + sal1 where emp_no=125;
                else
                        update emp1 set sal=sal1 where emp_no=125;
                end if;
                dbms_output.put_line('Final Number : '|| name);
        end;
/

SQL PROGRAM USED
=================
SET LINESIZE 61
TTITLE CENTER "THE XXXXXXX D" SKIP 3 -
LEFT "TEST" -
RIGHT "ANOTHEAR TEST" SKIP1 -
LEFT "======================"
select * from dual;
0
Comment
Question by:umesh4exp
2 Comments
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 400 total points
ID: 38295697
There are the SQL*PLUS formatting commands/options for the reporting right, so they might not work well with pl/sql.

But what is your issue here ?

What is the output you need and what is the current output you are getting ?
0
 
LVL 21

Accepted Solution

by:
flow01 earned 1600 total points
ID: 38296344
So you have to create your own  "title"  code  in pl/sql

begin
 dbms_output.put_line('                       THE XXXXXXX D   ....'  || chr(10) || chr(13) ||  chr(10) || chr(13) || 'TEST');
END;
/

(add an chr(10)  or chr(10 || chr(13)  to skip a line)
or  

show the title by means of sqlplus before executing the pl/sql block

SQL> column dontshow noprint
SQL> select null dontshow from dual;

or
insert the results in a (temporary) table to make them available to sql*plus

create global temporary table temp_results (nr_line number, rsl_line varchar(255));
set head off


begin
    delete from temp_results;
    ....
    insert into temp_results  value ( 1, 'Final Number : '|| name);
end;
/


select rsl_line from temp_results order by nr_line;
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question