#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    3
    Rep Power
    0

    Formatting in pl/sql


    i was wondering how u use sql plus report formatting commands (TTITLE, COLUMN etc...) inside a pl/sql script.

    E.G. creating a credit card like statement using pl/sql to get all the information and using sql plus to format the results.

    is this actually possible, or should i just use either pl/sql or sql plus

    cheers, waz.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    U could use htp.p() statements to write the output to a web-viewable HTML document.
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    You can only use the environment command within a pl/sql to set your current SQL*Plus environment. You cannot use other command like 'TTITLE' or 'COLUMN' commands (although you can write it in your PL/SQL script but it doen not format your output).
    You can make your own format, example as follows:

    SQL>create table emp_t as select deptno,empno,ename,sal from emp;
    SQL>set serveroutput on
    SQL> DECLARE
    v_emp emp_t%rowtype;
    TB constant varchar2(1):=CHR(9); -- equal to TAB
    CURSOR c_emp is
    select deptno,empno,ename,sal
    from emp_t
    order by deptno;
    BEGIN
    OPEN c_emp;
    dbms_output.put_line('Dept'||TB||'Emp'||TB||rpad('Employee',15,' ')||TB||' Salary');
    dbms_output.put_line('No.'||TB||'No.'||TB||rpad('Name',15,' '));
    dbms_output.put_line(rpad('-',42,'-'));
    LOOP
    FETCH c_emp INTO v_emp;
    exit when c_emp%notfound;
    dbms_output.put_line(v_emp.deptno||TB||
    v_emp.empno ||TB||
    rpad(v_emp.ename,15,' ') ||TB||
    to_char(v_emp.sal,'$999,999'));
    END LOOP;
    END;
    /

IMN logo majestic logo threadwatch logo seochat tools logo