Forum Stats

  • 3,827,386 Users
  • 2,260,768 Discussions
  • 7,897,223 Comments

Discussions

SQLPLUS COLUMN POSITION

Ibrahim Alzaidi
Ibrahim Alzaidi Member Posts: 6 Blue Ribbon

Dear All

I would like to print out the column per position in my spool file

Like I need to get the username in position(5) not (6)

set trimspool on

SET TRIMSPOOL ON

SET TRIMOUT ON

set echo off

set heading off

set feedback off

set verify off

col INS a4

col USERNAME format a20

col USER_ID format a10

set COL USERNAME RIGHT

spool on

Select '0001' as INS,USERNAME from dba_users;

spool off

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,070 Red Diamond

    Hi, @Ibrahim Alzaidi

    Sorry, it's not clear what you want to do,

    Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data.  (If the problem involves a data dictionary view, such as dba_users, then post CREATE TABLE and INSERT statements for a table that has the same relevant columns. You can call it something like test_dba_users.)

    Always post your complete Oracle version (e.g. 18.4.0.0.0) and also the version of any other relevant software (such as SQL*Plus).

  • Ibrahim Alzaidi
    Ibrahim Alzaidi Member Posts: 6 Blue Ribbon

    Sorry I was asking how to remove spaces among columns , and I did it with (set colsep off)

    Now I'm facing an other issue it's about column data I need it as Right to left

    and Thank you for your quick reply

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Oct 11, 2021 1:44PM

    The code you posted in your first post has bugs in it. You also define a COL definition that you do not select. Your purported fix is broken. "set colsep off" is invalid.

    Also I do not understand your statement "it's about column data I need it as Right to left" as you do not actually show your desired output.

    Here is a working example taking some of your code and fixing it and demonstrating it on just Oracle mangaged accounts, excluding user accounts, done using sqlplus:

    SQL> $type user_fu.sql
    set colsep ""
    set heading off
    set trimspool on
    set trimout on
    set feedback off
    col ins format a4
    col username format a30
    Select '0001' as ins, username
    from  dba_users
    where oracle_maintained = 'Y'
    and   rownum < 6
    order by 2
    /
    
    SQL> @user_fu
    
    0001GSMCATUSER
    0001OJVMSYS
    0001ORACLE_OCM
    0001SYSKM
    0001XS$NULL
    SQL>
    
  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    You need to set your NLS settings properly. The database understands that some languages are written from right to left.