SQLPLUS COLUMN POSITION

Ibrahim Alzaidi

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

Frank Kulash

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

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

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.

https://community.oracle.com/tech/developers/discussion/comment/16811628#Comment_16811628

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