Stack Overflow
Stack Overflow

Questions tagged [sqlplus]

SQL*Plus is a command-line interface for the Oracle RDBMS product. SQL*Plus questions should probably be also tagged with oracle.

SQL*Plus is a command-line interface for the Oracle RDBMS product. SQL*Plus can be installed as part of the Oracle database product, or as a separate client, as well as as an Instant Client.

SQL*Plus questions should probably be also tagged with .

While SQL commands can be run through SQL*Plus, the tag should be used for questions that relate specifically to the SQL*Plus tool (e.g., connection or formatting issues) and not for SQL statements where the use of SQL*Plus is incidental.

When asking questions regarding SQL*Plus, it is advisable to state the version of the client as well as the database version.

Useful links:

3440 questions
326
votes
31 answers

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly…
Jacques
  • 6,936
  • 8
  • 43
  • 102
225
votes
9 answers

When do I need to use a semicolon vs a slash in Oracle SQL?

We have been having some debate this week at my company as to how we should write our SQL scripts. Background: Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order to deploy our scripts to production. Now, we had…
amischiefr
  • 4,750
  • 2
  • 29
  • 22
179
votes
11 answers

Escaping ampersand character in SQL string

I am trying to query a certain row by name in my sql database and it has an ampersand. I tried to set an escape character and then escape the ampersand, but for some reason this isn't working and I'm uncertain as to what exactly my problem is. Set…
Slater Victoroff
  • 21,376
  • 21
  • 85
  • 144
165
votes
16 answers

How do I spool to a CSV formatted file using SQLPLUS?

I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS. How do I do it?
Daniel C. Sobral
  • 295,120
  • 86
  • 501
  • 681
134
votes
8 answers

DBMS_OUTPUT.PUT_LINE not printing

When executing the following code, it just says the procedure is completed and doesn't print the infomation i want it to (firstName, lastName) and then the other values from the select query in a table below. CREATE OR REPLACE PROCEDURE…
dexter
  • 1,347
  • 2
  • 9
  • 7
120
votes
7 answers

How do I ignore ampersands in a SQL script running from SQL Plus?

I have a SQL script that creates a package with a comment containing an ampersand (&). When I run the script from SQL Plus, I am prompted to enter a substitute value for the string starting with &. How do I disable this feature so that SQL Plus…
JoshL
  • 10,737
  • 11
  • 55
  • 61
92
votes
5 answers

How to display databases in Oracle 11g using SQL*Plus

With help of this command show databases; I can see databases in MySQL. How to show the available databases in Oracle?
Nubkadiya
  • 3,285
  • 13
  • 40
  • 45
91
votes
2 answers

When or Why to use a "SET DEFINE OFF" in Oracle Database

I'm watching a Script in Oracle and I see something I don't recognize REM INSERTING into database1."Users" SET DEFINE OFF; Insert into database1."Users" ("id","right") values ('1','R'); I'm looking for documentation about "set define off" and it's…
Enrique Benito Casado
  • 1,914
  • 1
  • 20
  • 40
88
votes
12 answers

Webrick is very slow to respond. How to speed it up?

I have a Rails application that I'm running on my server. When I go to a remote desktop and attempt to load the application, the server takes a good 3-4 minutes to respond with a simple HTML page. However, when I load up the page locally on the…
Crypto
72
votes
11 answers

how to increase sqlplus column output length?

I have some queries to find out the ddl of some objects from a schema. The result columns I am getting are truncated in the middle of the queries. How can I increase the width of the column? I tried with SET SERVEROUTPUT ON SIZE 1000000; SET…
Mohamed Saligh
  • 12,029
  • 19
  • 65
  • 84
63
votes
6 answers

Favorite SQL*Plus tips and tricks

So many times I just need a quick connection to an Oracle DB, where SQLPLUS handles the job. I imagine when people start using Oracle, the first thing they are told to do is to install Toad or SQLDeveloper. Even so, sometimes you don't want to wait…
ericp
  • 611
  • 2
  • 11
  • 17
58
votes
5 answers

sqlplus statement from command line

Is it possible to do something like this? $ sqlplus -s user/pass "select 1 from dual" or $ echo "select 1 from dual" | sqlplus -s user/pass I know I can put select 1 from dual in a file and do this: $ sqlplus -s user/pass @myFile.sql but I'm…
andersonbd1
  • 5,266
  • 14
  • 44
  • 62
55
votes
2 answers

Maximum length of command line argument that can be passed to SQL*Plus?

I am calling SQL*Plus from Linux C Shell: sqlplus username/password @file.sql var1 var2 var3 If I pass a string as var1, how long can this string be? Is it governed by the OS? In this case: Linux version 2.6.9-100.ELsmp…
Umber Ferrule
  • 3,358
  • 6
  • 35
  • 38
55
votes
6 answers

How can I issue a single command from the command line through sql plus?

Using SQL Plus, you can run a script with the "@" operator from the command line, as in: c:\>sqlplus username/password@databasename @"c:\my_script.sql" But is it possible to just run a single command with a similar syntax, without a whole separate…
JosephStyons
  • 57,317
  • 63
  • 160
  • 234
52
votes
6 answers

How to kill a running SELECT statement

How can I stop a running SELECT statement by killing the session? The command is continuously giving me output based on the SELECT statement, I want to stop it in between.
Ashish Anand
  • 3,531
  • 6
  • 34
  • 45
1
2 3
…
99 100