Command-Line Utility Program for Oracle

learn more… | top users | synonyms

0
votes
0answers
11 views

Trouble With Basic Query Searching [migrated]

Not sure if the this is the right place to ask but i have a question with beginner sql. I have the table dept and emp which include: SQL> desc dept; Name ...
0
votes
1answer
42 views

I don't want to have the error when I drop a table although the table doesn't exist [closed]

I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist. I did this set echo on set serveroutput on alter session set ...
0
votes
1answer
35 views

How can sqlplus knows where ORACLE_SID point?

As I understand, SID is unique value to identify an Oracle database instance. And I can set default database instance sqlplus use by changing ORACLE_SID environment variable. But how can sqlplus ...
4
votes
1answer
85 views

Result on execution of any SQL is number 2

After successful execution of any sql in oracle sql command line it shows 2 like i want to see the table or description of any table it show 2 instead of showing the table. SQL> select * from dual ...
2
votes
0answers
216 views

Install Oracle 10.2.0.4 on Windows Server 2012 R2

I have problem with installing Oracle 10.2.0.4 on Windows Server 2012 R2. We are running old 10.2.0.4 instance on Windows Server 2008 R2. Now I have to migrate it to the Windows Server 2012 R2. ...
0
votes
1answer
16 views

Problems to connect to an Oracle Database with SQLPLUS and URL-Variable, can somebody explain what I have to do?

I would like to connect to a ORACLE database via SQLplus. If I do it this way: sqlplus "user/pw@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=dbsrv01.desy.de)(PORT=1521))(ADDRESS=(PROTOCOL = TCP)(HOST = ...
0
votes
0answers
9 views

Setting Blank lines to ON before executing a SQLFILE in shell script

echo exit | sqlplus username/password@host_ip:port/Service_name @SQLFILENAME I want to execute set sqlbl on before executing The SQLFILENAME and also want that after executing SQLFILENAME it should ...
1
vote
1answer
222 views

Database gives “ORA-01109: Database not open” error when it IS open

Running Oracle Database 12c on Windows Server 2012 Whenever I attempt to do a log switch (alter system archive log current), I am given the error in the title. Searching online shows the simple ...
0
votes
2answers
41 views

IF condition in connect command in SQL*Plus

I want to fire SQL queries only when the user is connected to SQL*Plus. In my case even if the connection is not there, the rest of the commands are running in my SQL script. if "connect ...
1
vote
1answer
23 views

Find out where length semantics gets changed

I've code to create tables that doesn't specify length semantics: CREATE TABLE CHAR_TEST ( CHAR_TEST_ID NUMBER(*,0) NOT NULL, FOO VARCHAR2(6) NOT NULL, BAR VARCHAR2(6) NOT NULL, ...
2
votes
1answer
1k views

sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

There are similar questions asked and answered in this network related to the error message given in the question, however this problem is a little different from each. I have installed oracle 12c in ...
0
votes
1answer
29 views

Can not login user SEC_MGR in SQLPLUS

I can login user SEC_MGR when I use the TOAD tool. But, It has error when i login by SQLPLUS:
0
votes
0answers
94 views

Cannot log in sys as sysdba to Oracle 11g SQL*Plus with full ezconnect string

I am having problems with SQL*Plus on my RAC One ODA. I cannot use a full connect string to log in to any database (there are three on two separate ODAs) with sys as sysdba. I can duplicate this ...
1
vote
0answers
54 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 = ...
1
vote
2answers
132 views

How to erase in sqlplus with backspace?

Using SQL*PLUS, I cannot use the backspace button to erase content during a line command. When I hit backspace, instead of erasing it writes ^H Is there a way to configure backspace so it works as ...
1
vote
1answer
129 views

SQLPlus “no listener” only with a remote connection from Windows

The Oracle database is running on a linux server. The lsnrctl service is running. Locally, I can connect to database from sqlplus and sql developer. Remotely, from windows, I can't connect. sqlplus ...
0
votes
1answer
227 views

is there a way to connect to mysql using oracle sqlplus instant client

i am stuck while trying to connect to mysql using sqlplus client and i have already asked this question at askubuntu.com as ...
0
votes
0answers
40 views

Query on Sqlplus utility

I have a OS user 'test' who doesn't belong to dba group or oinstall group. I need to access the database using sqlplus but I'm unable to access the sqlplus even after setting oracle home ans path and ...
1
vote
1answer
309 views

SQL Spool FILE SQLPLUS BATCH FILE

*/ SET colsep ';' SET TERMOUT OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 800 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET HEADING OFF SET NUMW 20 column timecol new_value timestamp column ...
0
votes
1answer
59 views

sqlplus connect without typing the @local_SID when only have one instance

Every time I want to connect with a user in Sqlplus I have to specify the database|sid name too, SQL>connect scott/tiger@orcl I have installed another database for other purpose but I removed ...
0
votes
1answer
55 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 ...
1
vote
1answer
266 views

SQLPlus Command Line In-line Set Commands

I have the need to run linux command line direct query, including SET options. The query by itself runs fine. However, when I attempt to add set commands, it fails. (my alias 'db' connects to ...
0
votes
0answers
248 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. ...
1
vote
1answer
179 views

sqlplus not connecting to Oracle XE 11g

I installed Oracle XE 11g on my Ubuntu in vmware fusion. The oracle installation is all went fine but I am not able to connect the oracle database using sqlplus. I am getting this exception: $ ...
1
vote
0answers
94 views

How to make sqlplus more user-friendly anyone? [closed]

Note: I've tried using Toad from the Mac, but that's another fine mess so I'd rather bite the bullet and run commands through ssh. This is to edit and run interactive discovery SELECTs, not to run ...
1
vote
1answer
365 views

Compact Spool file from Sqlplus

I have scripts that I run in Oracle SQL Developer and then export the result to my pc as a csv. I now started using sqlplus with a .bat to run my scripts and spool the result to my pc. But I noticed ...
0
votes
0answers
372 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 ...
0
votes
0answers
216 views

Sql*PLUS: Output each field as a new line

Right now the configuration I have is the following: set pagesize 0; SET NULL *NULL*; SPOOL output/output.txt I am outputing the results to a text file in order to run a function on the file to ...
2
votes
2answers
5k views

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: ...
0
votes
2answers
339 views

Failed to connect to oracle SQL Plus [closed]

I tryed to connect to SQL Plus as the admin "System", I'm sure that the password I wrote was correct but the connection failed. I also tried to connect with Scott/tiger but it failed as well. What can ...
0
votes
0answers
119 views

sqlplus on linux not connecting when provided with IP port

I am running Oracle Database 11g R2 on Oracle Linux 5. I am able to connect to my SID from sqlplus if I use the command sqlplus system/password@orcl but when I provide the IP address and port: ...
0
votes
2answers
60 views

Tablespace Creation and Reassignment

It has been determined that user "SCOTT" should use a new temporary tablespace called TEMP2 size equivalent to TEMP tablespace (refer to Notes below for determining the size of TEMP). Create ...
0
votes
1answer
813 views

Spool in Shell script not working for me

#!/bin/sh echo "Started at" `date +%Y/%m/%d" "%H:%M:%S` sqlplus -s ${ORA_LOGIN_SYSADM} <<EOF 2>&1 > /dev/null SET SERVEROUTPUT ON SET FEED OFF spool ech.txt create BEGIN ...
1
vote
2answers
182 views

SQLPlus : default define settings

My workstation is Ubuntu 14.04. I have Oracle Express 11g installed. Whenever i run the define command in SQLPlus, is see the define _editor = "ed". Is there a way to change that permanently to gedit ...
0
votes
0answers
1k views

How to compare between two values in one field depending on another field in SQL* / Oracle Reports?

I have three fields in a table named table_family. One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son. Other field is "age" and it contains the ages of all the ...
1
vote
1answer
427 views

How to access apex data from sqlplus?

I have setup an oracle database in my Ubuntu machine. Now I have took the following steps to create tables 1) login to my ubuntu account 2) su oracle 3) sqlplus "sys as sysdba" //Connected to an ...
2
votes
1answer
16k views

How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?

I am not a professional, sorry. But perhaps you can help me anyway ... I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus ...
0
votes
1answer
44 views

sql script hangs in SQLplus on Solaris

Running an sql script in SQLplus on Solaris Any idea why script hangs in line 9 '/' and not executed? declare dummy number; begin select 1 into dummy from dual ; end; /
0
votes
2answers
100 views

glogin.sql supress error when Instance Idle

I have a pl/sql block in my glogin.sql file to show information from v$instance when I connect on the database server. Is there a way to suppress the ORA-01034 we would naturally get when the ...
1
vote
2answers
3k views

Using transactions in sqlplus

I'm coming to oracle from postgresql. In postgresql, I can use the command-line tool, psql, to execute a delete or update in a transaction: dbpool.production=> begin; BEGIN ...
0
votes
1answer
6k views

Can't connect to a Oracle db instance via SQLDeveloper

When I set the connecting information on the GUI in SQLDeveloper I get the following error (everytime!) Status : Failure -Test failed: IO Error: The Network Adapter could not establish the ...
0
votes
2answers
64 views

Filtering of sql output

I want to migrate the database. When I disable the constraints before adding the data to the output file I get extra lines: SQL> spool disable_constraints.sql; SQL> select 'alter table ...
0
votes
1answer
221 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 ...
0
votes
3answers
182 views

Prevent auto commit of DROP without SET AUTOCOMMIT OFF

I dropped my table and I lost my data and I can't roll it back because DROP statement automatically commits. How can I prevent auto commit action of DROP without using SET AUTOCOMMIT OFF?
1
vote
2answers
19k views

sqlplus: ORA-01017 logon denied from one host but OK from another

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain: 1) I have an Oracle 11.2 server running on a virtual host, say host1 2) I set up another VM, say host2 (CentOS ...
0
votes
0answers
17 views

sqlplus: ORA-01017 logon denied from one host but OK from another [duplicate]

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain: 1) I have an Oracle 11.2 server running on a virtual host, say host1 2) I set up another VM, say host2 (CentOS ...
0
votes
2answers
31 views

transaction not processing?

I'm trying to test out an update prior to running it on our production server, however when running my transaction in sqlplus, I don't seem to be able to commit the transaction or get any data back ...
0
votes
0answers
4k 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 | ...
1
vote
1answer
856 views

Pass the unix variable to the ddl command inside a pl/sql block

I would like to create a db link dynamically, using the input given in the unix session. i.e. the remote db name and db instance would be given by the user, in unix, which are VAR1 and VAR2. Now, from ...
0
votes
1answer
68 views

Using oracle easy connect does it really connect to the remote DB?

I tried connecting to remote host db using : sqlplus abc/abc@hostname1:1000/ABC And tried printing !hostname it still shows me local hostname from which I am running and not HOSTNAME1. So if ...