Skip to main content

Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

48 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3 votes
1 answer
6k views

sqlplus is not starting

I have a strange error on a Red Hat machine. InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/ The user which installed the client was oracle:oinstall. My ...
AlexWerz's user avatar
  • 131
2 votes
0 answers
593 views

SQL*Plus fail instead of prompting for substitution variable

Imagine a file test.sql with the following content SELECT '&myvar' AS greeting FROM dual; When we run that file in sqlplus. We will be prompted for the value of the variable &myvar since it's ...
user1387786's user avatar
2 votes
0 answers
487 views

How to turn on the expanded mode in Oracle SQL*Plus?

As in the switch \x of PostgreSQL. For more information on the PostgreSQL expanded mode, fetch https://serverfault.com/questions/618642/psql-expanded-mode-equivalency-for-mysql. Both PostgreSQL and ...
48347's user avatar
  • 86
1 vote
0 answers
192 views

oracle sqlcl and sqlplus - seperate login.sql on linux

I feel I'm probably missing something obvious here, but I can't see an easy way to have a separate login.sql for sqlplus and sqlcl This is problematic for me as the errors thrown up by sqlplus for the ...
Dave Smylie's user avatar
1 vote
0 answers
308 views

How to trim trailing spaces from headers in Oracle 12.1 SQLPLUS

I am creating a pipe-delimited file from a SQLPLUS query (Oracle 12.1), and the recipients are requesting that I trim the trailing spaces from the header entries. That is, the current output file ...
Silverlock's user avatar
1 vote
0 answers
178 views

Equivalent in SQL*Plus of sqlite3's "line mode"

One of the output display modes supported by the default sqlite3 text client is the so-called "line mode". In this mode, the output of SELECT queries is displayed "one value per line". Here's an ...
kjo's user avatar
  • 341
1 vote
0 answers
873 views

Conditional flow in sqlplus

I have script for SQL plus, which I use to create user and get arguments for username and password. I want the script to do more things when it runs on oracle 12c, but I'm trying to avoid writing ...
SHR's user avatar
  • 874
1 vote
0 answers
569 views

SQLPlus TNS:protocol adapter error

i have two Oracle 12c instances. currently both are shutdown. when I try to connect with sqlplus / as sysdba it says tns protocol adapter error contents of tnsnames.ora are DB12102 = (DESCRIPTION = (...
Amit Jaiswal's user avatar
1 vote
0 answers
2k views

Why is there a difference in CSV Export Results of Oracle SQL Developer and SQL Plus Spool

When I export data from SQL Developer GUI, setting format: csv, left enclosure: ", right enclosure: ", and Encoding: UTF8, I get data exactly the way that I need it for processing in Python. However,...
user avatar
1 vote
0 answers
3k views

Export Oracle Database User data from client

I am working on client system. There is a database on server contains many users. I have the access to database with a userid and password. my userid is "ncon" and Database name is 'HYDPPD'. We are ...
user avatar
1 vote
1 answer
167 views

My subdirectories under $ORACLE_HOME are missing

Im using oracle 11g. I cannot access the software products since they are missing. When I cd to $ORACLE_HOME, there are no files under it. They are missing. I cannot access the sqlplus. What to do in ...
beginner_01's user avatar
1 vote
1 answer
1k views

Check Oracle Service is Started from Batch Command

Uses: Oracle9i Standard Ed; I have a schedule which performs a Dump Import and afterwards the System Reboots itself, which is happening without any problem. After the System had rebooted, I have ...
hiFI's user avatar
  • 179
0 votes
1 answer
11 views

sqlplus column header misaligned

In wide sqlplus reports, column headers get misaligned from the data The code that led to this output was: set linesize 32767 set trimspool on col z format a1 alter session set nls_date_format='...
Brian Fitzgerald's user avatar
0 votes
0 answers
27 views

sqlplus connects to my Oracle server only on "second try"

My Oracle DB used to work fine but lately I have the following problem: When I try to connect with sqlplus with a non-interactive command as the following, from the very machine I deployed my DB on, ...
Andrea Montalbani's user avatar
0 votes
0 answers
63 views

listener issues in oracle 19c

These are my listener.ora and tnsnames.ora files listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = dr_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/19.0.0/...
datascinalyst's user avatar
0 votes
0 answers
38 views

Unix Powershell script stops after running SQLPlus command within the script

We are trying to create a Unix Powershell (not shell script) script which first runs a SQL to update a couple of tables, psprcsque and psprcsrqst. The function which runs the SQL update gets variables ...
David's user avatar
  • 1
0 votes
1 answer
44 views

Oracle colsep is adding newlines. Why?

So, I have a script: set heading off; set feedback off; set linesize 10000; set newpage NONE; set colsep '~' whenever sqlerror exit 1; WHENEVER OSERROR EXIT 255; select TO_CHAR(je.key_date, 'YYYY-MM-...
Christian Bongiorno's user avatar
0 votes
0 answers
16 views

Geting ORA-01017 after copying oracle home directory to different linux server

-- Oracle V 12c I have two different servers. I have copied the entire oracle directory from one server to another, and now I am using made relevant changes in .bash_profile. when executing sqlplus, ...
datascinalyst's user avatar
0 votes
1 answer
660 views

Can't log into Oracle 19c database running inside a container from the host

I have a server running Oracle 19c inside a Docker container, and I would like the ability to log into the database from the host using sqlplus. Inside the container, I am able to run this command ...
slightly_toasted's user avatar
0 votes
2 answers
144 views

ignore column width in sqlplus

I'm getting the follow SQLPLUS> create table source(aa nvarchar2(2), bb nvarchar2(100), cc nvarchar2(10)); insert into source values ('aa',null,'aa'); insert into source values (null,'aa','aa'); ...
Nir's user avatar
  • 533
0 votes
1 answer
64 views

How to return SQLPLUS Null as \N

I have a script that moves that from one DB to another by a SQL. I'm querying from one side and load it into another. TSV style. I'm expanding that script now to support Oracle as a source. For that ...
Nir's user avatar
  • 533
0 votes
0 answers
122 views

What’s wrong with provided apex instance administrator password

1 I have installed Oracle Database 21c Enterprise Edition on Oracle windows 10 I am now trying to install Apex 22.2 . Got to the chapter "5.4.2.3 Running apxchpwd.sql" to set the instance ...
Prabhali Pawar's user avatar
0 votes
0 answers
268 views

problem with services in automatic restart

I am working on Oracle 19c. I have a problem with the services I created via: alter system set service_names=' exemple1, exemple2' scope=both; I have many services to create and I am limited to 255 ...
c3oz's user avatar
  • 1
0 votes
1 answer
550 views

How to we get history of sql statements of sysdba?

We have been running a list of commands in cli: sqlplus / as sysdba We did not have "history on" and we logged out of the session. How do we get the list of sql statements we ran before as ...
Kristi Nanza_'s user avatar
0 votes
1 answer
161 views

How to use CLOB datatype for storing limitless characters in column each rows?

I want to use CLOB datatype instead of varchar2(4000) and long, in which I want store limitless characters . However, its easy to declare the string based column to CLOB but the main problem is when I ...
Faisal.softech's user avatar
0 votes
0 answers
912 views

sqlplus is not working, it is frozen

When I type sqlplus / as sysdba It simply gets frozen, the cursor stays there, and I can type more character but after pressing enter nothing happens. Neither CTRL + C. My pmon service is running, ...
Raul Mercado's user avatar
0 votes
0 answers
371 views

How to store data in loop and after assign data set to out ref cursor in Oracle

I tried to store date in loop and after that dataset set to OUT SYS_REFCURSOR. In this time I declared array variable and I tried to store data in loop by each iteration. and after, I want to assign ...
Neo4242's user avatar
0 votes
0 answers
154 views

How to get the functionality of sqlplus' "BREAK ON" in Postgres?

Is there a way to separate a query that has a group by attribute in Postgres in a fashion similar to sqlplus? Assuming I have branches of my company in every country, is there a way to have a separate ...
Majd's user avatar
  • 111
0 votes
0 answers
163 views

SQL*Plus 18/19 on Windows: Cannot access SQL scripts

I have this very strange observation on my Windows 10x64 machine: Given the following script file (file name test.sql): DEFINE SYS_NAME = &1 DEFINE SYS_PWD = &2 DEFINE ORACLE_HOME = &3 ...
AxD's user avatar
  • 133
0 votes
0 answers
249 views

Is it neccesary to shutdown the oracle database before uninstalling

I have a requirement of deleting/uninstalling a installed oracle 11g database on a Linux machine and I follow the below steps to uninstall the database. Kill all processes runs with user oracle. ...
Ashish Mishra's user avatar
0 votes
1 answer
167 views

Silence Sqlplus Login Message but Retain Echo

Running a series of automated checks on our databases for the security folks checking our STIG compliance. Mostly simple stuff like certain parameters turned off, etc. We're in the stone ages, so this ...
Jeff Bauersfeld's user avatar
0 votes
1 answer
933 views

Direct, simple SQL query results in "ORA-01406: fetched column value was truncated"

All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; ...
user3150146's user avatar
0 votes
0 answers
3k views

Import .dmp file into Oracle Database

Guys. When I tried to import a .dmp file into a fresh oracle database (sqlplus), I got this error. dump file "exp_tbs_gxplt01282019.dmp" may be an Data Pump export dump file My step is: SQL> ...
miaj's user avatar
  • 1
0 votes
1 answer
54 views

How to add table to database inventory in sqlplus oracle 12c

Problems How to create database Inventory database and inside it table Items in sqlplus oracle 12c . Details Im beginner on Oracle 12c release i setup oracle and i create database Inventory Now ...
abeer shlby's user avatar
0 votes
3 answers
2k views

Pass multiples values to script into sqlplus in Oracle

I want to execute the same sql script passing one text every time but I don't know how to it. I want to do it into sqlplus. With this select I have the values but I don't know how to pass this ...
estonolose's user avatar
0 votes
0 answers
2k views

WHENEVER OSERROR suppresses error messages

$ sqlplus <connstring> 2>&1 <<! WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK; SET ECHO ON; @nonex.sql; ! Produces SQL*Plus: Release 11.2.0.1.0 ...
ivan_pozdeev's user avatar
0 votes
0 answers
11k views

execute .sql script inside an anonymous pl/sql block which is invoked by a shell script

I got a shell script which connects to SQLPLUS and executes the .sql file like below: read a x=`echo $a | cut -d , -f 1` y=`echo $a | sed 's/.*,//'` c=`echo $USER | cut -c -2`"OPR"`echo $USER | ...
SSS1's user avatar
  • 11
0 votes
1 answer
2k views

ORA-03114 Unable to connect to remote Oracle database

I have an Oracle Database installed on Oracle Linux Server which I'm trying to connect to from my PC. I have configured the listener in the following way [oracle@ol8ngs1 ~]$ lsnrctl status LSNRCTL ...
dontMashMe's user avatar
0 votes
1 answer
385 views

Insert file content (with white line and special characters ) in bash variable for insert in oracle table with sqlplus

I write this simpe test.ksh scipt: #!/bin/ksh TEST=$(cat $1 ) sqlplus -S *****/*****@*****<<- EOF set define off; SET SQLBLANKLINES ON; INSERT INTO DBADOC.TEST ( "TEST" ) ...
Michele M's user avatar
0 votes
1 answer
977 views

Don't echo a single command in SQL*Plus

If I have a script like this... SET ECHO ON SELECT 'I want the person reading the spool to see this whole query.' FROM DUAL WHERE interesting_logic_ = 'DEBUGGABLE'; SET ECHO OFF PROMPT I only ...
durette's user avatar
  • 625
0 votes
1 answer
528 views

Sqlplus not using NLS_LANG env variable

I am trying to login to an Oracle 10g database vía sqlplus. In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish. In the database server my NLS_LANG is ...
A. Cifuentes's user avatar
0 votes
1 answer
176 views

How to use SQL*plus from standard user in Windows Vista to run sql query to Oracle10g on my server?

i have a bach file it contain: @echo off sqlplus x/y@z @run.sql for execute this code correctly i have to execute this file from administrator user from my client, for some reason i need to run ...
user3121138's user avatar
0 votes
2 answers
2k views

Create tablespace "error in creating database file, access denied (OS 5)"

I try to create a database file in this location I've checked the route and everything, I've tried several times to create the tablespace like this... ...but as you can see, I was just lead to ...
Lena's user avatar
  • 1
0 votes
2 answers
6k views

ORA-32000: write to SPFILE requested but SPFILE is not modifiable

I'm not able to alter the SORT_AREA_SIZE in my database.
ebatinstitute's user avatar
-1 votes
1 answer
457 views

import .dmp in Toad : Invalid format of Import utility name Verify that ORACLE_HOME is properly set

When I tried to import a DMP file using TOAD (9.0.1) Import Utility Wizard I get an error Invalid format of Import utility name Verify that ORACLE_HOME is properly set Any suggestions please? I ...
developer's user avatar
-1 votes
1 answer
287 views

How to use mariadb instead of sqlplus using sqlnet configuration files

currently I can access a database on a remote server using Oracle's instant client "sqlplus" The connection is dependent on a couple of configuration files in: $ORACLE_HOME/network/admin which are ...
aghsmith's user avatar
-1 votes
1 answer
231 views

Error while updating a date column using SQL*Plus variable

I am not able to update the date time which I have already stored in a variable and I am trying to update the another record with date time present in that variable. last_update_time=`sqlplus -s user/...
Ankit Gupta's user avatar
-1 votes
1 answer
5k views

Oracle database - how to switch instance

I'm totaly new in oracle, so sorry for my probably stupid question... I have two instances on oracle server, when I'm connecting to the server via sqlplus in default opens connection to instance X but ...
tommy__123's user avatar