Command-Line Utility Program for Oracle

learn more… | top users | synonyms

15
votes
12answers
2k views

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 ...
13
votes
4answers
2k views

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 ...
8
votes
3answers
4k views

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 ...
8
votes
3answers
252 views

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?
8
votes
3answers
8k views

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 ...
7
votes
2answers
761 views

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; ...
6
votes
3answers
86k views

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 ...
6
votes
1answer
389 views

sqlplus consuming 99% CPU

Oracle's installed on one server, I'm running sqlplus on a different (application) server. I've recently encountered a problem getting sqlplus to do anything; it simply spins, consuming 99% CPU even ...
5
votes
1answer
476 views

Execution plan using B+ tree index, but also sorts

I'm using Oracle sqlplus. And I have the following query: SELECT fooID from foo MINUS SELECT fooID from bar; I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
5
votes
1answer
7k views

How to resolve ORA-01031: insufficient priviledges error on Windows installation?

I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database ...
4
votes
1answer
714 views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- ...
4
votes
1answer
855 views

sqlplus Setting the exit code of a script

How can I set the exit code of a script? The following doesn't seem to work WHENEVER SQLERROR EXIT 5 DECLARE retCode number := 0; BEGIN retCode := 30; END; / EXIT :retCode ; Gives the ...
4
votes
4answers
10k views

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 ...
3
votes
4answers
5k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
3
votes
2answers
4k views

Calling SQL Loader From SQL Plus

I was wondering if there was a way I can execute a SQL Loader script from SQL Plus. We are using Oracle 10g.
3
votes
1answer
3k views

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, ...
3
votes
1answer
118 views

Why is SQL*Plus' desc table not showing not null constraints after using dbms_redefinition

I have the following table that I want to redefine: create table tq84_redefinition ( id number primary key, ts1 timestamp not null, ts2 timestamp ); Please note the not null constraint on the ...
3
votes
2answers
261 views

How to create a user with a particular user_id?

Is it possible to specify the user_id of a user while creating it? SQL> create user user1 identified by user1; User created. SQL> select user_id, username from all_users where ...
3
votes
2answers
7k views

Oracle Enterprise Manager (OEM) locking SYSMAN account

Why does Oracle Enterprise Manager reset my SYSMAN account to LOCKED(TIMED) status? I've tried rebuilding Enterprise Manager, and re-installation of Enterprise Manager, which always works, until the ...
3
votes
1answer
311 views

How can I alter the output for the sql desc command?

When I do desc table, I get back the columns Name, Null? and Type. What I also wish to get back is a column like UNIQUE to tell me if the column is in fact unique. I realize that this is a ...
3
votes
1answer
967 views

halt on error in a sqlplus script

I am deploying pl/sql code using several sql files that are called with @@file. If a package got a compilation error the script continues to the end. Is there a way to stop on every compilation ...
3
votes
2answers
364 views

SQL*Plus default output format

I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query. Database 1: C:\Oracle\instantclient_10_2>sqlplus ...
3
votes
1answer
926 views

sql*plus 11.2 client connection to oracle timing out on Linux

One of our DBAs is seeing connection issues with his sql*plus client. Whenever he connects to the Oracle v10g server, the connection seems to time out for no apparent reason. We tried checking ...
3
votes
3answers
3k views

Login Problem as SYSDBA ORA-01031

I have the following problem: I can connect to an Orcale database as normal user or as sysdba: sqlplus myuser/myPw@myDB sqlplus "sys/sysPw@myDB as sysdba" But when another (Windows-User) is logged ...
2
votes
2answers
3k views

Why can't we specify group functions in Oracle in the where clause?

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
2
votes
4answers
453 views

Shell: How to time a script running in SQLPlus and kill it after x amount of minutes?

I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I ...
2
votes
1answer
171 views

Oracle start database with incorrect password

I have a question regarding starting oracle database with in correct sys password. I have provided incorrect password for sys user and I am still able to start database and execute statements. So ...
2
votes
2answers
7k views

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?
2
votes
2answers
240 views

Is there a setting to suppress C-Style comments appearing as DOC in Oracle Output?

Just again looking at some log from an oracle script, where the developer intended to comment out some part not to add documentation. I don't want that a script containing code like this /* select ...
2
votes
1answer
827 views

Can't return data from SQL/Plus and Toad but can from SQL Developer

This one has me stumped. We are running an Oracle 11g on Amazon EC2 and I can connect and query fine with SQL Developer but with Toad and SQL/Plus I can connect but when I try to do a simple select ...
2
votes
1answer
310 views

Invalid File Name on login to SQLPLUS

The moment I log into SQLPLUS I get an SP2-0556 error. I recently moved OS but am connecting to the same database so I am not sure if it's because I'm using my old TNSNames or what. I can connect and ...
2
votes
1answer
2k views

Oracle not available error with Oracle XE 11.2.0-1.0

I have downloaded Oracle XE 11.2.0-1.0 and installed it on a CentOS 6.3 virtual machine with 1.5GB memory and 3GB of swap. SYS and SYSTEM set to have password 123456. I have set ...
2
votes
1answer
1k views

Simple Oracle query hangs depending on WHERE clause

Not used to using Oracle, but we have a large database where a non-unique query like SELECT * FROM employees where department = 'HR' is working, results list up no problem. But when I do ...
2
votes
2answers
9k views

Oracle11.2g post-installation problem on Oracle Linux kernel

[question copied from stackoverflow] After installation of Oracle 11.2g in silent mode on the Oracle Linux kernel, I have the following error: [u01/app/oracle/product/11/bin] $ sqlplus sqlplus ...
2
votes
0answers
2k views

Encoding problems with sqlplus, Linux

I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page. select * from ...
2
votes
0answers
2k views

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 ...
1
vote
2answers
696 views

Remote to a remote oracle server and get running instances?

I need to make a shell script that takes a list of servers and and check the running instances then run sql script in each instance! now I do not want it to ask me for a password! it's redhat and ...
1
vote
1answer
165 views

Why can't I log in to SYSTEM without AS SYSDBA

When loging in to sqlplus as SYSTEM, why does the login fail with an invalid username/password error if I do not include "AS SYSDBA" C:\Windows\System32>sqlplus /nolog SQL*Plus: Release ...
1
vote
2answers
3k views

ORA-12154 TNS: could not resolve the connect identifier specified with SQLPLUS

I am using Toad to connect my database and its working fine for me . But when i try to connect with the same credential , i am getting the error message as shown below: ORA-12154 TNS: could not ...
1
vote
1answer
64 views

There is an equivalent of login.sql on PostgresSQL?

When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed. It's usefull to set preferences... There is an equivalent ...
1
vote
1answer
636 views

sqlplus not working on opensuse

take a look at this: > cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs ...
1
vote
1answer
2k views

Error calling stored procedure from trigger in Oracle Database 11g

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to ...
1
vote
1answer
3k views

Differences with using Bind variables in TOAD,sqlplus and SqlDeveloper

I have following example query. variable pStartDateBegin VARCHAR2(10); variable pEndDateFinish VARCHAR2(10); begin select '01-01-2000', '30-11-2011' into ...
1
vote
1answer
57 views

How does SQL*Plus connect to Oracle without a listener?

sqlplus can be used even if there is no listener in the system, or if it isn't running. How does SQL*Plus connect to Oracle without a listener?
1
vote
1answer
104 views

Appending data from one program to the other using batch commands

I have a database which sometimes crashes. I want to create a batch program that does this: Runs SQL*Plus. Makes SQL*Plus run a script from a plain text file. Gets the results back to the main batch ...
1
vote
0answers
65 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 ...
1
vote
2answers
361 views

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 ...
1
vote
0answers
197 views

SQLPlus times out when connecting to an Oracle XE database on another PC

I'm having the strangest issue. I have a very basic (I think) setup of an Oracle 11G database on one Hyper-V VM, and the Oracle 11G client on another VM. Although I'm able to access the database on ...
1
vote
1answer
477 views

How do I execute an Oracle SQL script without sqlplus hanging on me?

For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.: If the script contains any named substitution variable ...
1
vote
0answers
246 views

Which are the Oracle basics required for advanced Oracle.DataAccess usage? [closed]

Successful use of [Oracle.DataAccess.Client] is based on proper understanding of three separate parts of Oracle: SQL PL/SQL sql*plus Here on dba.exchange the following questions target this topic: ...