Tagged Questions

Command-Line Utility Program for Oracle

learn more… | top users | synonyms

0
votes
2answers
9k views

“ORA-01017: invalid username/password” : Why is that?

What is the difference between the two : SQLPLUS / AS SYSDBA and SQLPLUS Enter user-name: SYSDBA Enter password: If I use the first command I get connected and see the prompt SQL> but upon ...
0
votes
1answer
5k views

Set encoding from within SQL*Plus

I have a large set of files with SQL commands that I run with a SQL*Plus script that uses the @@ operator. I run the script on third-party computers (or even send it by e-mail) so I'd like to make it ...
2
votes
2answers
7k 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 ...
2
votes
1answer
1k 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
480 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 ...
1
vote
1answer
80 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 ...
0
votes
1answer
887 views

Where to put login.sql in Windows environment to configure SQL Plus?

I installed Oracle 10g XE on my Windows Server 2008 R2. Now I would like to configure SQL Plus to use login.sql for login settings. I created a login.sql file and put it into a C:\users\myname ...
8
votes
3answers
303 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?
3
votes
1answer
1k 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 ...
2
votes
1answer
232 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 ...
3
votes
1answer
2k 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 ...
4
votes
2answers
12k 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
1answer
3k 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
2k 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
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 ...
1
vote
1answer
3k 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
2answers
981 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 ...
2
votes
0answers
3k 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
1answer
852 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 ...
7
votes
2answers
926 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; ...
5
votes
4answers
16k 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 ...
23
votes
3answers
156k 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 ...
2
votes
2answers
12k 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 ...
5
votes
1answer
564 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 ...
1
vote
1answer
4k 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 ...
3
votes
2answers
9k 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
3answers
4k 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 ...
3
votes
1answer
345 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 ...
2
votes
2answers
4k views

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

SQL> desc tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
2
votes
2answers
267 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 ...
4
votes
1answer
1k 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 ...
3
votes
4answers
6k 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
314 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
1answer
5k 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, ...
1
vote
0answers
266 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: ...
9
votes
3answers
5k 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 ...
4
votes
2answers
907 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 ----------------------------------------- -------- ...
3
votes
2answers
5k 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.
9
votes
3answers
10k 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 ...
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 ...
15
votes
12answers
3k 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 ...