6 Replies Latest reply: Nov 1, 2013 2:28 AM by edef5949-237c-45ac-ac3c-e065e6996f40 RSS

    Formatting DBMS_OUTPUT.PUT_LINE

    edef5949-237c-45ac-ac3c-e065e6996f40

      Hi there!

       

      I'm new to PL/SQL and I'm having difficulty formatting the data printed by the DBMS_OUTPUT.PUT_LINE command.

       

      I'm using a procedure and the code is below:

       

      CREATE OR REPLACE PROCEDURE "HEALTHDBA"."PATIENTS_DISEASES" IS

       

      pid patients.patient_id%TYPE;

      fname patients.first_name%TYPE;

      lname patients.last_name%TYPE;

      disease1 diseases.disease_name%TYPE;

      disease2 diseases.disease_name%TYPE;

      disease3 diseases.disease_name%TYPE;

      disease4 diseases.disease_name%TYPE;

      cursor c_patients is

      select p.patient_id, p.first_name, p.last_name, d1.disease_name, d2.disease_name, d3.disease_name, d4.disease_name

      from patients p

      inner join diseases d1

      on p.disease_1 = d1.disease_id

      inner join diseases d2

      on p.disease_2 = d2.disease_id

      inner join diseases d3

      on p.disease_3 = d3.disease_id

      inner join diseases d4

      on p.disease_4 = d4.disease_id;

       

      BEGIN

       

      DBMS_OUTPUT.PUT_LINE(rpad('PATIENT ID', 25) || rpad('FIRST NAME', 25) || rpad('LAST NAME', 25) || rpad('DISEASE1', 25) || rpad('DISEASE 2', 25) || rpad('DISEASE 3', 25) || rpad('DISEASE 4', 25));

       

      open c_patients;

       

      loop

       

      fetch c_patients into pid, fname, lname, disease1, disease2, disease3, disease4;

       

      DBMS_OUTPUT.PUT_LINE(rpad(pid, 25) || rpad(fname, 15) || rpad(lname, 15) || rpad(disease1, 15) || rpad(disease2, 15) || rpad(disease3, 15) || rpad(disease4, 15));

       

      exit when c_patients%notfound;

       

      end loop;

       

      close c_patients;

       

      END;

      This is the output that i get :

       

       

       

      Could someone please help me out and tell me what I'm doing wrong?

       

      Thanks in advance!

        • 1. Re: Formatting DBMS_OUTPUT.PUT_LINE
          edef5949-237c-45ac-ac3c-e065e6996f40

          the output looks like this:

           

          PATIENT ID FIRST NAME LAST NAME DISEASE1 DISEASE 2 DISEASE 3
          DISEASE 4
          P1 JANE SMITH AIDS TB EPIPHORA DIABETES
          P21 SHANE KELLER CONSTIPATION COLITIS GLAUCOMA CHOLERA
          P22 SIMON MORRIS AIDS MUMPS CATARACTS CONSTIPATION
          P23 NATASHA BURTON CATARACTS GLAUCOMA CONSTIPATION AIDS
          P24 JULIA WEBB CHOLERA GLAUCOMA CONSTIPATION MUMPS
          P25 LUIS GONZALES GLAUCOMA THYROIDITIS CHOLERA DIABETES
          P26 BILLY WEBSTER CANCER DIABETES GLAUCOMA EPIPHORA
          P27 ALICE STEVENS CANCER TB COLITIS GLAUCOMA
          P28 ANITA WAGNER CANCER CATARACTS AIDS CONSTIPATION
          P29 GRACE JONES AIDS GLAUCOMA DIABETES THYROIDITIS
          P30 NATALIE HOLT TB GLAUCOMA CATARACTS AIDS
          P31 MELISSA ROYE CONSTIPATION TB GLAUCOMA CATARACTS
          P31 MELISSA ROYE CONSTIPATION TB GLAUCOMA CATARACTS
          PL/SQL procedure successfully completed.


          It's not formatted into columns as I'd like it to be.


          Please help?

          • 2. Re: Formatting DBMS_OUTPUT.PUT_LINE
            rp0428

            Why are you using PL/SQL and DBMS_OUTPUT to do something you can easily do using SQL?

             

            All of that complexity isn't even needed.

             

            Just query the data.


            • 3. Re: Formatting DBMS_OUTPUT.PUT_LINE
              edef5949-237c-45ac-ac3c-e065e6996f40

              It's for an assignment that I'm working on and it states that i have to use a stored query. The only way i figured i could achieve this, is by using a procedure.

              • 4. Re: Formatting DBMS_OUTPUT.PUT_LINE
                davidp 2

                Apart from the fact that you have padded the headings to 25 characters and the columns to only 15 characters, I get columns as output using SQLPlus on Oracle 11.2.0.3

                You may be using a client or environment that is "tidying up" the output.

                 

                As a PL/SQL hint,

                a) You might consider using a stored query returning a REF CURSOR  https://forums.oracle.com/thread/888365

                b) If you are doing all the work in your procedure it's much cleaner to use a cursor FOR loop. PL/SQL Static SQL

                E.g.

                begin

                  FOR rslt in (select p.patient_id, p.first_name, p.last_name from patients p order by p.last_name, p.first_name

                           ) LOOP

                    dbms_output.put_line(rpad(rslt.patient_id, 25) || rpad(rslt.first_name, 25) || rpad(rslt.last_name, 25) );

                  END LOOP;

                end;

                /

                • 5. Re: Formatting DBMS_OUTPUT.PUT_LINE
                  rp0428
                  It's for an assignment that I'm working on and it states that i have to use a stored query. The only way i figured i could achieve this, is by using a procedure.

                  Then use an OUT parameter and return a CURSOR. Then just print the cursor results in sql*plus.

                  -- proc returning ref cursor

                  CREATE OR REPLACE
                  PROCEDURE pr_print_result(
                      p_deptno dept.deptno%type,
                      p_result OUT sys_refcursor)
                  IS
                  BEGIN
                    OPEN p_result FOR SELECT * FROM emp WHERE deptno=p_deptno;
                  END ;

                  -- printing ref cursor in sql*plus

                  SQL> var emp_dtls refcursor

                  SQL> EXECUTE pr_print_result(p_deptno=>10,p_result=>:emp_dtls);

                  PL/SQL procedure successfully completed.

                  SQL> PRINT emp_dtls;

                       EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
                  ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                        7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                        7839 KING       PRESIDENT            17-NOV-81       5000                    10
                        7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

                  • 6. Re: Formatting DBMS_OUTPUT.PUT_LINE
                    edef5949-237c-45ac-ac3c-e065e6996f40

                    Thank you very much rp0428, your advice helped me a lot. I tweaked my procedure as follows:

                     

                    CREATE OR REPLACE PROCEDURE "HEALTHDBA"."PATIENTS_DISEASES" (

                    c_patients OUT sys_refcursor

                    )

                     

                     

                    IS

                     

                     

                    pid patients.patient_id%TYPE;

                    fname patients.first_name%TYPE;

                    lname patients.last_name%TYPE;

                    disease1 diseases.disease_name%TYPE;

                    disease2 diseases.disease_name%TYPE;

                    disease3 diseases.disease_name%TYPE;

                    disease4 diseases.disease_name%TYPE;

                     

                     

                    BEGIN

                     

                     

                    open c_patients for select p.patient_id, p.first_name, p.last_name, d1.disease_name, d2.disease_name, d3.disease_name, d4.disease_name

                    from patients p

                    inner join diseases d1

                    on p.disease_1 = d1.disease_id

                    inner join diseases d2

                    on p.disease_2 = d2.disease_id

                    inner join diseases d3

                    on p.disease_3 = d3.disease_id

                    inner join diseases d4

                    on p.disease_4 = d4.disease_id;

                     

                     

                    END;

                     

                    and then called it the following way in iSQL*Plus:

                     

                     

                     

                    set serveroutput on;

                     

                     

                    var :pat_dtls refcursor;

                     

                     

                    begin

                     

                     

                    patients_diseases(:pat_dtls);

                     

                     

                    end;

                    /

                    print :pat_dtls;

                     

                    it works 100%!

                     

                    thanks once again guys!