Can We Format Number Output in SQLD?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Can We Format Number Output in SQLD?

  1. #1
    Join Date
    Mar 2010
    Location
    North Carolina, USA
    Posts
    18

    Can We Format Number Output in SQLD?

    Hi Folks,

    We are using 11.1.0.7 Standard Edition on Linux CentOS release 5.4 here.

    I am used to using sqlplus, but now I'm trying to use SQLDeveloper. Heh. But I don't know how to tell SQLD to format numeric output.

    -- with this query:

    select s.USERNAME,
    sum(p.pga_used_mem / 1024576) as pga_mgs_used
    from v$process p, v$session s
    where p.addr=s.paddr
    and s.username is not null
    group by s.username
    order by s.username;

    -- I get this:

    CLINICA 1.92638711037541382972078206009119870073

    -- and with this:

    select s.username,
    round(sum(p.pga_used_mem / 1024576)) as pga_mgs_used
    from v$process p, v$session s
    where p.addr=s.paddr
    and s.username is not null
    group by s.username order by s.username;

    -- I get this:

    CLINICA 2


    -- but what I want is this:

    CLINICA 1.93


    It's easy in sqlplus, all you have to do is type:

    column pga_mgs_used format 999.99

    And then run your query. Can we do something equivalent in SQLD?

    Thanks in advance for any advice.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Something like...
    Code:
    select  round(1.92638711037541382972078206009119870073,2)
    from    dual
    ;
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2010
    Location
    North Carolina, USA
    Posts
    18
    Ah yes, formatting it in the code does work.

    This:

    select s.username,
    round(sum(p.pga_used_mem / 1024576),2) as pga_mgs_used
    from v$process p, v$session s
    where p.addr=s.paddr
    and s.username is not null
    group by s.username order by s.username;

    results in this:

    CLINICA 1.92

    So I guess what we're saying is, there's no way to tell the application itself (SQLD) to format number columns in a particular way - we just have to do it in the code.

    Thanks Sir, I appreciate your input!

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by oraclegirl View Post
    So I guess what we're saying is, there's no way to tell the application itself (SQLD) to format number columns in a particular way - we just have to do it in the code.
    Exactly. SQL Developer supports a subset of SQLPlus commands, actually it supports whatever SQLPlus commands are supported by SQL Worksheet which is in charge of interpreting commands and sending them against the target database.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width