Questions tagged [sqlplus]

Command-Line Utility Program for Oracle

Filter by
Sorted by
Tagged with
2 votes
1 answer
53 views

How to Avoid SQLPlus output truncating

I have the following column in oracle database table. Column name : objectdata Data type : LONG RAW When I query this column via sqlplus output is like below. SQL> select objectdata from tableA ...
user avatar
  • 223
0 votes
1 answer
34 views

Cannot connect to a pluggable database via IDE

I have an Oracle XE database on computer1. When I try to connect to it via sqlplus from computer2, it works fine, for both cdb and pdb connections; e.g.: rlwrap sqlplus timon@//192.168.0.115:9121/...
user avatar
0 votes
0 answers
34 views

How to store data in loop and after assign data set to out ref cursor in Oracle

I tried to store date in loop and after that dataset set to OUT SYS_REFCURSOR. In this time I declared array variable and I tried to store data in loop by each iteration. and after, I want to assign ...
user avatar
0 votes
0 answers
57 views

How to get the functionality of sqlplus' "BREAK ON" in Postgres?

Is there a way to separate a query that has a group by attribute in Postgres in a fashion similar to sqlplus? Assuming I have branches of my company in every country, is there a way to have a separate ...
user avatar
  • 11
-1 votes
1 answer
482 views

Sql plus equivalent in psql

I'm tasked of migrating some code from sqlplus to psql In oracle, I have a bash script that excutes a stored procedure and based on the result it calls a sqlplus that spools multiple files to dump ...
user avatar
  • 11
0 votes
0 answers
144 views

Problems using catcon.pl

We are investigating the use of catcon.pl to launch scripts on multitenant Oracle DBs. In this case, with APEX upgrade scripts. For example, the apexins.sql script, is made to give it the arguments at ...
user avatar
1 vote
1 answer
128 views

The expected read speed of SQL*PLUS

The issue is many-fold but it boils down to this simple scenario: I have a Oracle Database Version 19.4.0.0.0 and SQL*PLUS Version 21.3.0.0.0 running on the same Red Hat Linux (I actually tried both ...
user avatar
0 votes
3 answers
820 views

Oracle: Simple way to compare all objects of a schema in two databases?

I'm attempting to compare all objects of two schemas located in two different databases. I know there may be ways to do this with TOAD and SQL Developer, but is there another way to do this by script ...
user avatar
  • 11
0 votes
2 answers
292 views

Oracle 19c - find primary DB information from a mounted standby

I have a (probably simple) query about how to find an Oracle 19c primary database's host by querying a mounted standby using sqlplus. I can get the primary name as follows: select ...
user avatar
  • 41
0 votes
1 answer
84 views

Trouble querying the correct info from gv$encryption_wallet

I'm trying to get the wallet status for the current instance of an Oracle 11g 2 node RAC DB (but ideally all RDBMS versions). The following command returns values for both nodes instead of one: SQL>...
user avatar
  • 41
0 votes
0 answers
59 views

SQL*Plus 18/19 on Windows: Cannot access SQL scripts

I have this very strange observation on my Windows 10x64 machine: Given the following script file (file name test.sql): DEFINE SYS_NAME = &1 DEFINE SYS_PWD = &2 DEFINE ORACLE_HOME = &3 ...
user avatar
  • 123
0 votes
2 answers
164 views

Insert query in Orcale database - not showing the row in a table

I am inserting a row in the Oracle database table. The query looks like as below - INSERT INTO <schema_name>.<Table_name> (SID, LABEL, DESCR, GTI_EXPTYP, GTI_MODUS, GTI_NAME) VALUES (22, '...
user avatar
0 votes
0 answers
122 views

Is it neccesary to shutdown the oracle database before uninstalling

I have a requirement of deleting/uninstalling a installed oracle 11g database on a Linux machine and I follow the below steps to uninstall the database. Kill all processes runs with user oracle. ...
user avatar
0 votes
1 answer
343 views

ORA-03114 Unable to connect to remote Oracle database

I have an Oracle Database installed on Oracle Linux Server which I'm trying to connect to from my PC. I have configured the listener in the following way [oracle@ol8ngs1 ~]$ lsnrctl status LSNRCTL ...
user avatar
0 votes
1 answer
52 views

How can I make this DECLARE PROCEDURE working?

I'm new to Oracle DB, coming from MS SQL Server. I created this script: DEFINE USER_NAME = &1 DEFINE PASSWORD = &2 DEFINE TABLESPACE = &3 CONNECT &DB_ADMIN/&DB_PWD@//localhost:...
user avatar
  • 123
0 votes
1 answer
44 views

Silence Sqlplus Login Message but Retain Echo

Running a series of automated checks on our databases for the security folks checking our STIG compliance. Mostly simple stuff like certain parameters turned off, etc. We're in the stone ages, so this ...
user avatar
0 votes
1 answer
119 views

Set Parameter in SPFILE still outputs the same error

for the installation of a SAP IDM system, I need to know how to use the command alter system set "_ORACLE_SCRIPT"=true SCOPE=SPFILE; command correctly. The installation creates a user and ...
user avatar
0 votes
0 answers
155 views

import .dmp in Toad : Invalid format of Import utility name Verify that ORACLE_HOME is properly set

When I tried to import a DMP file using TOAD (9.0.1) Import Utility Wizard I get an error Invalid format of Import utility name Verify that ORACLE_HOME is properly set Any suggestions please? I ...
user avatar
0 votes
1 answer
1k views

Connection to Oracle DB 19c fails when listener is set to the host IP address

I am new to Oracle DB and need help in solving the following issue. I am using sqlplus (SQL*Plus: Version 21.1.0.0.0) to connect to the Oracle DB 19c. Statement: I am able to connect to the Oracle DB ...
user avatar
0 votes
1 answer
178 views

Designing User & its Sub User Database Diagram Problem [duplicate]

There are some business category e.g. Travel Agency, Hotel, Car Rental etc. Roles: Management rolesSuper Admin, Staff Customer side roles are Owner, admin and sales counter May be etc. customer side ...
user avatar
0 votes
1 answer
295 views

Data deleted with sqlplus still showing up in SQL Developer [duplicate]

I wrote a script to run with SQLPlus which deletes the previous seven days of data in my Oracle XE database. I logged into the database with SQLPlus, ran the script, and it showed me the correct rows ...
user avatar
  • 3
2 votes
0 answers
141 views

SQL*Plus fail instead of prompting for substitution variable

Imagine a file test.sql with the following content SELECT '&myvar' AS greeting FROM dual; When we run that file in sqlplus. We will be prompted for the value of the variable &myvar since it's ...
user avatar
0 votes
1 answer
144 views

Insert file content (with white line and special characters ) in bash variable for insert in oracle table with sqlplus

I write this simpe test.ksh scipt: #!/bin/ksh TEST=$(cat $1 ) sqlplus -S *****/*****@*****<<- EOF set define off; SET SQLBLANKLINES ON; INSERT INTO DBADOC.TEST ( "TEST" ) ...
user avatar
0 votes
1 answer
142 views

Cannot login oracle database using other users

I'm currently using oracle xe c18. I can login using sqlplus / AS SYSDBA as oracle user. However, the command does not work for other users. It always shows an error ORA-01017: invalid username/...
user avatar
0 votes
1 answer
37 views

set autotrace on is just estimate plan?

I've made a test: SQL> set autotrace on; << autotrace on SQL> select * from danilo.testepart where nome = 'danilo3'; ID NOME ---------- -------------------------------------------...
user avatar
  • 508
1 vote
0 answers
130 views

oracle sqlcl and sqlplus - seperate login.sql on linux

I feel I'm probably missing something obvious here, but I can't see an easy way to have a separate login.sql for sqlplus and sqlcl This is problematic for me as the errors thrown up by sqlplus for the ...
user avatar
0 votes
2 answers
249 views

SQLPlus configuration problem or firewall issue

I need to connect to a remote Oracle DB, so I requested to open the firewall on the port 1521 to the DB and the replica. [root@ansible-test ~]$ telnet <oracle_db_active> 1521 Trying <...
user avatar
  • 101
0 votes
2 answers
2k views

Error 0xc000007b with Oracle 19c

As our (32-bit) application uses the Oracle client for communication with the Oracle server I have set up a test environment on a Microsoft Windows Server 2019 Datacenter. I have installed the Oracle ...
user avatar
  • 101
0 votes
0 answers
47 views

Number of Field in CSV File does not match with number of field in External Table

Hi is there a way to reject a record with excess csv field. For example: CREATE TABLE Table1 ( Field1 VARCHAR(255), Field2 VARCHAR(255) ) ORGANIZATION EXTERNAL ( TYPE ...
user avatar
0 votes
2 answers
67 views

Creating two databases in Oracle 11g

I am a student. I have assignments and projects simultaneously. I want my project database and the other simple databases separate. I have no option to use MySQL or others as my lecturer suggested to ...
user avatar
  • 11
1 vote
1 answer
7k views

How to install Oracle's sqlplus on a Mac? [closed]

I need steps how to install Oracle's sqlplus on a Mac.
user avatar
  • 153
0 votes
1 answer
80 views

How to quote a SQL*Plus identifier in a bash script?

I get "ORA-00904: "MEMORY_TARGET": invalid identifier" no matter how I try to quote the identifier in the following command: MEMORY_TARGET=$(su - orat01 -c " echo 'select ...
user avatar
1 vote
1 answer
136 views

Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server?

I am trying to have my entire remote DB backup made to my local server. Running the following command from my local server: expdp \"user/user@ip as SYSDBA\" full=Y directory=/backup/backup/ ...
user avatar
  • 13
0 votes
1 answer
559 views

Direct, simple SQL query results in "ORA-01406: fetched column value was truncated"

All the information I've seen on this issue is always in regards to running a query via some other utility (e.g. TOAD); however, I am seeing this issue when I simply try to select * from v$asm_disk; ...
user avatar
-1 votes
1 answer
180 views

Database not opening, after messing with datafiles [closed]

I was exercising some database recovery exercises First I had made a backup of my datafiles, then I moved them to another directory to test if I could recover them or not. When I was recovering them ...
user avatar
1 vote
0 answers
187 views

How to trim trailing spaces from headers in Oracle 12.1 SQLPLUS

I am creating a pipe-delimited file from a SQLPLUS query (Oracle 12.1), and the recipients are requesting that I trim the trailing spaces from the header entries. That is, the current output file ...
user avatar
0 votes
1 answer
162 views

Scheduled Job Does not work in oracle SQL*PLUS

I am just a beginner in Oracle and can't seem to get this job running :- I want to delete old rows from my database. And created a job as follows :- CREATE OR REPLACE PROCEDURE delete_trips AS ...
user avatar
1 vote
1 answer
19 views

The full contents of the bind-variable are not displayed in SQL Plus

I wrote a big query in bind-variable. I want to print it in SQL Plus, but the text doesn't show, only small part of it.
user avatar
  • 31
1 vote
1 answer
102 views

Big string in SQLPlus

I need to write a Select query in bind-variable and this query has more than 2500 symbols. Variable sql_txt clob; Exec :sql_txt := 'Select .....'; But SQL Plus support only <2499 symbols. How ...
user avatar
  • 31
0 votes
1 answer
423 views

Oracle: Connects with sqlplus but not from perl

I need to connect to an Oracle database from a perl script, it works in my tests and when using sqlplus, but not in production. This is a summary of the situation: Oracle 11g XE (11.2.0.2.0) : (Test ...
user avatar
  • 103
3 votes
1 answer
19k views

Cannot connect database as sys as sysdba using sqlplus

I am new to oracle server 18c (currently taking online course). My problem is sql*plus where I tried to use sqlplus then sys as sysdba (it did not work) and the error appeared below. However, if I ...
user avatar
-1 votes
1 answer
2k views

Oracle database - how to switch instance

I'm totaly new in oracle, so sorry for my probably stupid question... I have two instances on oracle server, when I'm connecting to the server via sqlplus in default opens connection to instance X but ...
user avatar
1 vote
2 answers
171 views

How can the TNS connect identifier passed to SQL*Plus be accessed from the run script for reconnecting?

I wonder how the TNS identifier can be used within a SQL script. In this example $ sqlplus MY_USER/pswd@TNSID @script.sql par1 par2 I'd like to get TNSID as a string in order to use it from script....
user avatar
  • 195
1 vote
2 answers
280 views

Can sqlplus fetch more than one row at a time?

We have a task running every day dumping results of a query into a CSV-file -- as the stdout of sqlplus running a query. The number of records is many millions (don't ask), and the job takes over two ...
user avatar
1 vote
1 answer
283 views

How to avoid split in output in sqlplus

I am running the following query to get list of all PDBs using SQLPLUS and calling each PDB NAME using for loop in shell script. Unfortunately, few PDB NAMEs are split/broken in the output(for example,...
user avatar
1 vote
0 answers
98 views

Equivalent in SQL*Plus of sqlite3's "line mode"

One of the output display modes supported by the default sqlite3 text client is the so-called "line mode". In this mode, the output of SELECT queries is displayed "one value per line". Here's an ...
user avatar
  • 331
0 votes
2 answers
417 views

SQL*Plus - interactive mode on windows

How can I issue commands through SQL*Plus on Windows? In Linux is like this: sqlplus -s /nolog<<EOF conn username/password@database EXECUTE some_proc; EXIT; EOF And Windows?
user avatar
  • 508
0 votes
1 answer
3k views

Oracle user password with space character in it

I connected to an Oracle 19c database and executed the following SQL: alter user sys identified by "a b" container=all; It executed successfully. However, after that I can't connect to Oracle using ...
user avatar
0 votes
0 answers
391 views

Don't echo a single command in SQL*Plus

If I have a script like this... SET ECHO ON SELECT 'I want the person reading the spool to see this whole query.' FROM DUAL WHERE interesting_logic_ = 'DEBUGGABLE'; SET ECHO OFF PROMPT I only ...
user avatar
  • 603
1 vote
3 answers
2k views

How can I disable the @ symbol in SQL scripts for sqlplus?

In Oracle's sqlplus tool, when the script contains an @ symbol, it loads an SQL script with that name and executes it. I'm inserting a VARCHAR value which contains some CSS code, so when it encounters ...
user avatar

1
2 3 4 5 6