Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

SQL*Plus provides basic command-line interface to Oracle instances. Programmers and DBAs commonly use it as the default available fundamental interface in almost any Oracle software installation, which includes running SQL and PL/SQL commands interactively or from a script.

Commands run from sqlplus fall under three(3) categories:

  • SQL Statements
  • PL/SQL Blocks
  • SQL*Plus internal commands

For more information see the 11g SQL*Plus User's Guide and Reference (pdf).

261 questions
93
votes
4 answers

How to use sqlplus to connect to an Oracle Database located on another host without modifying my own tnsnames.ora

I want to connect to an oracle database located on another host using sqlplus. This page suggested adding an item on my tnsnames to conenct to that database local_SID = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port=…
Louis Rhys
  • 1,125
  • 2
  • 10
  • 8
60
votes
3 answers

How to make sqlplus output appear in one line?

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read. What I'd rather like is either a horizontal scroll bar to appear or somehow send the output to less I run following statements in SQLPlus…
Kshitiz Sharma
  • 2,897
  • 8
  • 27
  • 33
57
votes
7 answers

Connect to sql plus from command line using connection string

Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c. So how do I need to connect correctly? I used many options like: sqlplus…
Edvinas
  • 571
  • 1
  • 5
  • 3
21
votes
14 answers

Comfortable sqlplus interface?

I found sqlplus'interface is rather outdated. It's quite nice to have some commands or keywords at disposal, but for example no "arrow-up" key for the previous history entry is available. What is a good replacement / extension for sqlplus? Could be…
Sebastian Roth
  • 1,336
  • 3
  • 15
  • 23
16
votes
4 answers

What is the significance of a semicolon at the end of SQL*Plus commands?

Some statements like create table, insert into etc take a semicolon at the end: CREATE TABLE employees_demo ( employee_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn_demo…
Lazer
  • 3,251
  • 15
  • 38
  • 53
16
votes
4 answers

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple statements each with blank lines, semicolons,…
Leigh Riffel
  • 23,534
  • 16
  • 75
  • 147
16
votes
3 answers

How to differentiate between SQL and PL/SQL?

I know the question might sound too stupid, but I never understood this part. SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for loop, is it not SQL anymore? PL/SQL is an extension…
Lazer
  • 3,251
  • 15
  • 38
  • 53
15
votes
3 answers

Is there any difference between Rem and -- comments for SQL*Plus?

On SQL*Plus prompt, both Rem and -- qualify as comment indicators: Rem this is a comment -- this is also a comment create table emp ( id number primary key, name cvarchar2(40)); Is there any difference at all between the two commenting techniques?
Lazer
  • 3,251
  • 15
  • 38
  • 53
14
votes
3 answers

How to set an Oracle user's default schema?

I created a few new users in Oracle. However, when running sqlplus, they all need to fully qualified the table names in query. What's the best way to set a default schema for these new users?
Will K
14
votes
1 answer

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, Item2 NUMBER ); For some reason I have to add…
bernd_k
  • 11,741
  • 23
  • 73
  • 108
13
votes
7 answers

"ORA-24454: client host name is not set" when connecting from Ubuntu instance on AWS EC2

Some background: I am attempting to connect to an Oracle database. I have a username, password, IP address, port, and service name. I only have access to this database from an Ubuntu EC2 instance on AWS because the Oracle database is located inside…
Karl Johnson
  • 351
  • 1
  • 2
  • 6
11
votes
3 answers

Do I have to retype information every time I make a mistake in SQLPlus

If I make a mistake in SQLPlus while inputting an 'insert into' command, is there a way I can bring this information back up in order to edit it?
Bambi Bowman
  • 111
  • 3
10
votes
1 answer

SQL*Plus, @, and relative paths

Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot. For example, under x:\some\where I have the following directory…
René Nyffenegger
  • 3,623
  • 5
  • 29
  • 39
7
votes
2 answers

Sql*Plus ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

I installed Sql*PLUS on a cloud Oracle Linux Environment in order to access an remote database. I used the installation instructions provided as an answer here: https://stackoverflow.com/questions/23488394/how-to-install-sql-plus-client-in-linux My…
beckah
  • 201
  • 1
  • 2
  • 5
7
votes
2 answers

If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why?

I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database: create or replace function tail_f return varchar2_tab pipelined as n number; begin loop exit when ... select…
René Nyffenegger
  • 3,623
  • 5
  • 29
  • 39
1
2 3
…
17 18