OraFAQ Forum: SQL & PL/SQL » alignment of output in the dbms

Home » SQL & PL/SQL » SQL & PL/SQL » alignment of output in the dbms_output file ( Oracle Database 11.1.0.7.0 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

alignment of output in the dbms_output file [message #636901]

Tue, 05 May 2015 05:10

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi all,

create or replace function align(p_str in varchar2, p_justify varchar2, p_size integer)    
  return varchar2    
  is    
    l_str    varchar2(4000);    
    l_temp   number;    
    l_temp_1 number;    
    l_left   number;    
    l_right  number;    
  begin    
    case p_justify    
      when 'RIGHT' then    
        l_str := lpad(p_str, p_size);    
      when 'LEFT' then    
        l_str := rpad(p_str, p_size);    
      when 'CENTER' then    
        l_temp   := p_size - length(p_str);    
        l_temp_1 := l_temp/2;    
        l_left   := floor(l_temp_1);    
        l_right  := ceil (l_temp_1);    
        l_str := lpad(' ', l_left) || p_str || rpad(' ', l_right);    
      else    
        raise_application_error(-20001, 'Invalid parameter for p_justify specified -- ' || p_justify);    
    end case;    
    return l_str;    
end;  

 
select align(' " '||'00-000-000-0000-000-0000'||' " ','LEFT',29)||'  , '||align(' " '||'PURCHASE_PRICE_VARIANCE'||' " ','LEFT',30)||'  , ' from dual
union all
select align(' " '||' '||' " ','LEFT',29)||'  , '||align(' " '||'CHARGE'||' " ','LEFT',30)||'  , ' from dual

getting output as like below
 ----------------------------
 " 00-000-000-0000-000-0000 "  ,  " PURCHASE_PRICE_VARIANCE "    , 
 "   "                         ,  " CHARGE "                     , 

 Expected output
 --------------
 " 00-000-000-0000-000-0000 "  ,  " PURCHASE_PRICE_VARIANCE "    , 
 "                          "  ,  " CHARGE "                     ,

I used function for output alignment , but i have to get output (Expected output ) without using function which are created by me (function align)

or

can you please help me how to get Expected output using above code?

Thanks

Report message to a moderator

Re: alignment of output in the dbms_output file [message #636902 is a reply to message #636901]

Tue, 05 May 2015 05:37

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The function is correct, the problem is you wrongly use it.
See:

SQL> select ' " '||align('00-000-000-0000-000-0000','LEFT',29)||' " '||'  , '||' " '||align('PURCHASE_PRICE_VARIANCE','LEFT',30)||' " '||'  , ' from dual
  2  union all
  3  select ' " '||align(' ','LEFT',29)||' " '||'  , '||' " '||align('CHARGE','LEFT',30)||' " '||'  , ' from dual
  4  /
'"'||ALIGN('00-000-000-0000-000-0000','LEFT',29)||'"'||','||'"'||ALIGN('PURCHASE_PRICE_VARIANCE','LEFT',30)||'"'||','
------------------------------------------------------------------------------------------------------------------------
 " 00-000-000-0000-000-0000      "   ,  " PURCHASE_PRICE_VARIANCE        "   ,
 "                               "   ,  " CHARGE                         "   ,

2 rows selected.

[Updated on: Tue, 05 May 2015 05:59]

Report message to a moderator

Re: alignment of output in the dbms_output file [message #636903 is a reply to message #636902]

Tue, 05 May 2015 05:58

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Thank Michel , let me check with my requirement... Smile

Report message to a moderator

Previous Topic:	list of columns of a table
Next Topic:	need help in query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 07 12:02:37 CDT 2026