Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
SQLPLUS COLUMN POSITION

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
Answers
-
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).
-
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
-
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>
-
You need to set your NLS settings properly. The database understands that some languages are written from right to left.