Command-Line Utility Program for Oracle

learn more… | top users | synonyms

2
votes
1answer
6k 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 ...
-4
votes
0answers
19 views

SQLPLUS QUERY HELP

Please help to create what might be a simple query for you; very tricky for me. Maybe a nested query?... or maybe a "For Loop". Thank you! Table name: WO_OPERATION Relevant Columns: ...
0
votes
1answer
2k views

sqlplus startup - failure in processing system parameters

I am trying to do the startup command this is what I have done set oracle_sid=ORCL sqlplus /nolog SQL> conn /as sysdba startup It shows this error message, failure in processing system ...
0
votes
1answer
24 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
28 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 ...
0
votes
1answer
132 views

Setting the delimiters for Oracle multiline Procedures Oracle sqlplus

how to set the procedure delimiters in Oracle sqlplus. I am new to Oracle SQLPlus. [oracle@Server1 bin]$ ./sqlplus ******/*********@1ipaddress:1521/test</home/oracle/user/test.sql SQL*Plus: ...
1
vote
5answers
15k views

Message file not found when starting SQLPlus

I've installed Oracle XE 11.2 on Fedora 18 but have trouble starting SQLPlus. It quits with the following error: Error 6 initializing SQL*Plus SP2-0667: Message file sp1<lang>.msb not found ...
0
votes
1answer
3k 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 ...
-1
votes
0answers
20 views

Oracle sqlplus report

I try to make a sqlplus report like that: A B C D --- --- --- --- 1 A Z 100 2 A Y 100 COUNT 2 TOTAL 200 ------------------------- 3 B ...
0
votes
1answer
272 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 ...
3
votes
4answers
8k 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 ...
0
votes
1answer
990 views

sqlplus is not starting

I have a strange error on a Red Hat machine. InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/ The user which installed the client was oracle:oinstall. My ...
0
votes
1answer
137 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 ...
1
vote
1answer
34 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
51 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. ...
0
votes
1answer
32 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: $ ...
2
votes
2answers
1k 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? I can connect any user using SQL*plus even with no ...
1
vote
2answers
1k 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 ...
1
vote
0answers
33 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 ...
2
votes
1answer
24k views

Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server

I installed 11g on Centos VM on my mac.I have everything up and working on server.I have included following command I ran on cenos. $lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production ...
0
votes
1answer
560 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: ...
1
vote
1answer
57 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
73 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 ...
4
votes
6answers
65k views

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 ...
0
votes
2answers
15k 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
0answers
49 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 ...
0
votes
2answers
85 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
55 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
49 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 ...
4
votes
3answers
63k 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 ...
0
votes
2answers
101 views

SQL*Plus save current COLUMN settings

In SQL*Plus, is there a way to save current COLUMN settings similar to STORE which saves SET commands in a script? COLUMN can be used to list the settings, but they are not in script format.
5
votes
2answers
46k 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 ...
1
vote
2answers
1k 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
2answers
103 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
3answers
88 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?
0
votes
0answers
441 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
202 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 ...
5
votes
2answers
1k 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 ...
27
votes
4answers
239k 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 ...
0
votes
1answer
33 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
55 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
votes
2answers
1k views

Write filename, servername/IP and date+time in spool file

I'm having some problems to verify the installation/execution of some oracle scripts in different production servers. I've created a launcher script which executes all the sql scripts in sqlplus and ...
4
votes
2answers
1k 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 ----------------------------------------- -------- ...
0
votes
2answers
46 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 ...
1
vote
2answers
10k 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
16 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
29 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
2k 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
600 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
53 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 ...