Command-Line Utility Program for Oracle

learn more… | top users | synonyms

0
votes
2answers
30 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
26 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
29 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 ...
1
vote
1answer
42 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
20 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
41 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
132 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
1k 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
44 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
87 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
35 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
3k 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
15 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
27 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
1k 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
346 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
43 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 ...
1
vote
1answer
10k 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
98 views

Why is it necessary to type “host” before the imp command when I'm using SQL*Plus to import a dmp file into Oracle?

In SQL*Plus, I'm connected to an Oracle 11g database. When I type imp help=y It gives me SP2-0042: unknown command "imp help=y" - rest of line ignored. But when I type host imp help=y it ...
0
votes
1answer
53 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.
1
vote
1answer
526 views

Oracle 11gR2 + RedHat 6.5 cannot start sqlplus

I cannot start sqlplus I've read everything I found, tried everything and still I am getting "command not found" I have a user oracle, I did export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 ...
1
vote
4answers
15k 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 ...
2
votes
1answer
113 views

How to Substitute Variables in PostgreSQL

The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL? INSERT INTO control_threshold ( threshold_id, group_name, description, sql, ...
2
votes
1answer
124 views

How to make sqlplus prompt longer than 50 chars?

I've set a prompt in my sqlplus glogin.sql a little bit long, mainly because of some internal variables and control sequence. Starting sqlplus I get the following error: String beginning ...
0
votes
1answer
32 views

Delete all types created by a user

In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;
2
votes
1answer
664 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 ...
0
votes
3answers
129 views

How to mirror the privileges of an user to another in Oracle Database? [duplicate]

I need to grant all the privileges of a user (say 'AAA') to another user (say 'BBB'). Is there such an Oracle command (11.2.0.3.0)?
1
vote
0answers
1k 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 ...
0
votes
1answer
231 views

Why don't HOST comands appear in the spool?

spooltest.sql: SPOOL test.log CLEAR SCREEN SET VERIFY OFF PROMPT Queries will appear in spool select sys_context('USERENV', 'INSTANCE_NAME') from dual; PROMPT Output from the HOST command will not ...
0
votes
1answer
42 views

In SQL*Plus, how do I run @https://here.is.url/script.sql behind a proxy?

In SQL*Plus, I try to run a script that is stored at a certain URL. From where I am currently, I can http(s) connect with the internet only via a proxy. So, if in SQL*Plus, I try ...
1
vote
1answer
47 views

SQLPlus Padding in Query Results

I have several Oracle servers that exhibit a behavior I do not understand. I have identified a two node RAC in which one node exhibits the behavior, but the other node does not. RAC is not involved ...
0
votes
1answer
142 views

Connection with SQL*Plus to Oracle

I've installed Oracle Database Express 10g on Ubuntu, but the following error occures when I try to connect to sql plus using the sysdba as username and it's password which I gave during installation ...
0
votes
1answer
597 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
660 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
657 views

ORA-12899 when inserting from sqlplus, but ok with jdbc

Inserting 'ééééé' into a column defined as varchar(5) runs fine from squirrel sql client (jdbc). But doing the same insert from sqlplus fails with ORA-12899 (too long value). NLS_CHARACTERSET is set ...
-1
votes
2answers
711 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 ...
3
votes
1answer
419 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 ...
1
vote
1answer
1k 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 ...
0
votes
3answers
22k 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
1answer
504 views

SQL output formatting

I want this: "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" But I get ...
0
votes
1answer
91 views

Execute a script with SQLPlus containing html entity mappings

When executing a script with SQL PLus, I was prompted to enter value for lt, gt, and apos. Here are some code examples in this script. v_qry := replace(v_qry, '&lt;', '<'); v_qry := ...
3
votes
2answers
531 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 ...
0
votes
3answers
431 views

Oracle won't start

While it was working fine, I had to stop the server once. When trying to start Oracle using a script we have, I got the following error: SQL> Connected to an idle instance. SQL> ORA-01081: ...
0
votes
5answers
8k 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 ...
1
vote
0answers
398 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 ...
6
votes
1answer
580 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 ...
1
vote
1answer
1k views

How to limit select output from sqlplus?

I stop using SQL Developer as found more convenient to use SQL Plus inside Emacs SQLi mode. SQL Developer on selects with large amount of rows show only few rows. But SQL Plus tend to dump all rows ...
2
votes
4answers
699 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 ...
1
vote
1answer
127 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 ...
0
votes
1answer
839 views

SQLPlus: Cmd vs PowerShell

From cmd.exe, when I run: C:\oracle\client\BIN\sqlplus.exe username@server then type my password, I get in. However, if I run the same command from PowerShell and type the same password I get: ...