3,477 questions
Advice
0
votes
2
replies
43
views
Spool failure that breaks up the excel due to few columns that contain paragraphs worth of data
So I have been tasked to automate numerous reports which i succeeded mostly apart from this one case, I tried many recommendations from chatgpt but most were busted, the broken excel file started ...
1
vote
1
answer
61
views
error when access oracle server (with docker) from host sqlplus
[Q] How to fix ORA-29024: Certificate validation failure when connecting from host to Oracle in Docker?
my docker image: container-registry.oracle.com/database/adb-free:latest-23ai
ubuntu 24.04.1, ...
- 67
0
votes
1
answer
52
views
REST working in SQLPlus but not workin in PLSQLDeveloper [closed]
I have a procedure run_rest. Executing this procedure in sqlplus works perferctly - i get response from external system. But executing this in plsql developer throws 400: Bad request.
Does someone has ...
- 702
0
votes
1
answer
45
views
SQLPlus Spool Issue
Simple problem. For audit purposes, a new set of steps was initiated when we sign into an Oracle DB using SQLPlus. At the first SQL> command prompt, we immediately activate spool to a file with a ...
- 1
1
vote
0
answers
173
views
Problem with MSLSA to use TGT session key from ticket Kerberos and use sqlplus or sqldeveloper
I would like to change in my file sqlnet.ora from OSMSFT:// to MSLSA: to integrate Microsoft security.
The problem come when I try to open a database in with authentification Kerberos like :
sqlplus /@...
- 59
-1
votes
1
answer
50
views
Is there any possible way for a compound key to have one OPTIONAL PK?
(Context: I have literally only 30+ hours on SQL & its concepts; Just started fiddling with Oracle, mysql workbench)
A screenshot that depicts what I said below
Currently, I have an ERD that has ...
1
vote
1
answer
51
views
Find time intervals where signal > value
I am using matlab to fetch data from our process database (AspenTech infoplus.21 historian).
I constantly find myself looking for interesting time intervals. Usually I do this in matlab after first ...
- 67
1
vote
2
answers
58
views
Pass BLOB to stored procedure from SQL*Plus
How to call a stored procedure from SQL*Plus with blob input parameters?
Below a sample of the SP to be called:
procedure p_dummy_proc (sessionId varchar2, accountId integer, lobData blob, oId out ...
2
votes
1
answer
105
views
How to write multi-line headers in html output file of oracle sqlplus?
The output I need (I need to add multi-line headers):
The output I am getting:
My Query:
-- Enable SQL*Plus HTML Markup
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
SPOOL "C:\...
- 23
0
votes
1
answer
88
views
SQL*Plus HTML Markup: Preventing Raw HTML Tags from Appearing in Output File
Output needed in this format:
I am using SQL*Plus to generate an HTML report from an Oracle database using SET MARKUP HTML ON. However, the output file contains raw HTML tags as plain text instead of ...
- 21
1
vote
1
answer
19
views
Data commits to database without explicit commit
I have shell script which inserts/updates data into oracle tables, what I don't understand is there is no explicit COMMIT in the script but data is committed to database, how is this possible?
I have ...
- 149
0
votes
1
answer
88
views
PLS-00357: Table,View Or Sequence reference not allowed in this context when execute immediate inside block
I'm trying to use variables in a ALTER SEQUENCE statement inside a BEGIN END block.
When hard coding the Schema name and the sequence name it works perfectly. But when I try to use variables instead ...
- 76
1
vote
0
answers
48
views
Sql*Developer: i/o error: The Network Adapter could not establish the connection
When trying to connect to the database in sql*developer, I get the following line:
An error was encountered performing the requested operation:
I/O Error: The Network Adapter could not establish the ...
- 11
0
votes
1
answer
423
views
Python : Not able to connect to oracle db ORA-12537: TNS:connection closed
I am trying to connect to oracle database in mac system.
My team generally works on Java so for development. we generally do this to connect to db in java. We connect to ssh tunnel which allows us to ...
- 45
0
votes
2
answers
74
views
How to input Oracle query statements in Unix shell variables
I am trying to input an Oracle query statement into a variable in a Unix shell.
I want to remove the header of the SQL result value, so I want to include "set heading off" in the QUERY ...
0
votes
0
answers
58
views
Trying to open a sqlplus file via a python script is giving an error "SP2-0310: unable to open file". The file opens correctly when not using python
I am using
import os
# Specify the directory you want to change to, take input from keyboard
directory_path = r"C:\code\xyz\deployment\tbd\example"
# Change the directory
os.chdir(...
0
votes
0
answers
21
views
sqlplus cannot grant select v_$session right to a user
I want to grant sys.v_$session to user c##DSLI01 and so run the following command in sqlplus:
SQL> show user
USER is "SYS"
SQL> SELECT privilege, table_name FROM ALL_TAB_PRIVS WHERE ...
- 1,448
0
votes
1
answer
44
views
Oracle SQLPlus taking exclusive session and not allowing to run update statements from .Net application
I connected via sqlplus like this:
sqlplus user/password@SID
I have a .NET application which also connects to the same database using the same user and password. But once sqlplus is connected, it was ...
- 3,477
0
votes
0
answers
91
views
Inserting a file into a BLOB column (Oracle) from a ksh script using sqlplus for passing parameters (file)
I would like to insert my file (csv) into a BLOB column (Oracle), I use a Korn Shell script to call an insert sql script via a SQLPLUS statement. my problem is that I don't know how to pass my file as ...
0
votes
1
answer
58
views
Oracle SPOOL Not Returning Same Number of Rows
I have a simple SQL query which returns 21 rows of data when I run it in Toad.
However, when I SPOOL the results to a CSV, I always get less than 21 rows of data, e.g. 16 rows one run, then 10 rows ...
0
votes
1
answer
132
views
ORA-03113: end-of-file on communication channel - SQL Plus - Oracle Express Edition
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Nov 13 09:30:39 2024
Version 18.4.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
ERROR:
...
- 9
2
votes
0
answers
93
views
How to Replace IN Operator with JOIN in Dynamic Criteria Using JPA Specification?
I have a requirement to build a dynamic filter criteria based on filters I receive. Initially, I used the Specification approach, and it is working as expected in production. However, the new ...
- 507
1
vote
1
answer
236
views
sqlplus: how to execute a query spanning multiple lines
I am using Oracle 19c. I can get the following to work:
variable max_id number;
exec select 41 into :max_id from dual;
begin
dbms_output.put_line(:max_id);
dbms_output.put_line(:max_id+1);
end;
...
- 313
0
votes
1
answer
45
views
How to merge headings from user_constraints and user_cons_columns
I am trying to output my constraints using both data dictionary tables. I want the output to include CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME COLUMN_NAME POSITION LAST_CHANGE, but I am not ...
0
votes
1
answer
33
views
Escaping symbols in Python when running SQL statement with Oracle sqlplus via SSH
I would like to run SQL statements using Oracle SQLplus via SSH using Python. So far, I have the following code:
import subprocess
sql = """
SET TIME OFF
SET TIMING OFF
SET HEADING OFF
...
- 730
0
votes
1
answer
51
views
view names of all tables in my database or oracle cli [duplicate]
if we write code in oracle sql command line like
SELECT COUNT(*) FROM user_tables; (it will give us the total number of tables in database).
if we write
SELECT table_name FROM user_tables; ( it is ...
- 21
0
votes
2
answers
937
views
plsql script is raising 'ERROR at line 1 DECLARE', I don't see the reason
Good afternoon community,
I have a PLSQL script executed against ORACLE 19 that is displaying an error that I don't understand.
The script drops any existing index before recreate them.
The objects ...
- 333
0
votes
1
answer
75
views
Equivalence to SQLCODE & SQLERRM, but for OS Errors?
I'm trying to catch an OS Error message just like when we catch SQL Error Messages.
An example of this error could be SP2-0310: Unable to open file: "file name".
I would normally just use ...
- 543
0
votes
1
answer
179
views
(PLSQL) Oracle is running an old version of the executed procedure
I had a problem with Oracle. I have solved it already (I am going to show how) but I would appreciate any explanation about the reason for the malfunction.
THE CONTEXT
I've declared a package of ...
0
votes
1
answer
54
views
Oracle SQL developer - do not detect line breaks
I am using oracle sql developer . Whenever we run any table / view sql with blank lines in between, the sql developer ignores it and executes the query perfectly. The feature is good, problem is the ...
-1
votes
1
answer
354
views
ORA-01741: illegal zero-length identifier with sql plus
Hello i m trying to execute this query with sql plus and i get the error ORA-01741: illegal zero-length identifier. Can you please help with it.
INSERT INTO FCCR_DATA_MAPPING(ID_API,DATA,COLUMN_NAME,...
0
votes
2
answers
699
views
EXPDP doesn't recognise user names
I'm trying run EXPDP on an Oracle 19 database but it doesn't recognize any of the schema names apart from sys.
select * from dba_users;
when run in Oracle SQL Developers returns all the users I'd ...
- 3
2
votes
1
answer
397
views
Oracle Instant Client download zips do not have the mesg folder
I am trying to install sqlplus without the rest of the oracle client onto a laptop with windows 11. I installed it from the instant client zip but when I run it I get the message SP2-0667: Message ...
- 21
1
vote
0
answers
58
views
Oracle PL/SQL setting spool commands. I need to clear output from variable values
I have a test table "bbb".
COLB
bba
aa1
bbb
aaa
bbb
bb1
and do following commands:
variable a varchar2(20);
begin
:a:='bbb';
end;
/
set heading off;
spool C:\Temp\test.txt;
set feedback ...
- 11
2
votes
2
answers
295
views
Issue selecting table in database using oracledb python module and local Oracle 21c database
I installed a local database and used SQL Developer to create a test table.
Database connection works, also the table is created and populated;
example here
Running this example code in Python throws ...
0
votes
1
answer
122
views
How to declare a constant in Oracle SQL PLUS?
I know how to declare a variable in Oracle sql plus but not sure with the constant declaration , Pls guide with that :
An example of variable declaration :
DEFINE v_target_table_name = '...
- 175
0
votes
1
answer
129
views
In Oracle, does a DDL script need commit statements when run with sqlplus?
DDL scripts do not need COMMIT statements as per the docs.
If I run these statements in SQL Developer, no commit is required:
CREATE TABLE dummy_table(ID NUMBER(38,0) NOT NULL PRIMARY KEY);
CREATE ...
- 1,756
2
votes
1
answer
155
views
Command-line sqlplus does an unwanted change directory [closed]
Normally starting the command-line version of sql*plus (sqlplus.exe) would retain the current working folder, allowing a script that is stored in that same folder to be run with the @ command.
D:\...
- 81
0
votes
2
answers
79
views
SQL Oracle/Concat and then use it in select * from
I want to define a variable for further use in the query.
Something like this:
--does not work--
define Name = 'Table_name'
define tableNm1 = concat(&Name, '_001')
define tableNm2 = concat(&...
- 11
0
votes
1
answer
133
views
sqlplus send two commands to the command line
I have tried both of these methods for sending two commands to the commandline with sqlplus. Neither are working. What am I doing wrong? I thought one of them would work.
sqlplus username/password@...
0
votes
1
answer
140
views
nested foreach in tcsh
I need to act over several database clients, these client have a static name APPUSR with numbers (1,11,12,13 2,21,22,23 3,31,32,33 ...) Id. APPUSR1,APPUSR12, APPUSR3, APPUSR32,....
I thought loop ...
- 3
0
votes
0
answers
58
views
sqlplus does not use column separators for specific output
i am trying to build an sql plus script that extracts some values out of my database to do a log search with them afterwards. It looks like this, amended to not use real data:
IFS='' read -r -d '' SQL ...
0
votes
2
answers
46
views
Oracle output to DUAL with double Quotes
I'm running a SQL job from Linux. I'm trying to grab the start time and end time of the job so I'm using the following SQL to grab SYSdate
select to_char(sysdate - 5/24, 'YYYY/MM/DD HH:MI:SS') "...
- 3
1
vote
1
answer
79
views
How to write a SQL statement that spools the result of select, replace and order by with custom delimiters to a csv file
I am trying to do exactly what the question says: selecting from a table, replacing special characters from a column, order the result, then spool to a .csv file, with a custom delimiter.
I can only ...
- 207
0
votes
1
answer
44
views
Comparing a table of invalid objects through aliases using the time and date?
I recently posted a question using DBLinks and monitoring invalid objects. Now I've got to make a table and register by date all invalid objects and make comparisons between runs of this code and if ...
- 27
1
vote
1
answer
185
views
SQLPlus dynamic spool filename from Table in a FOR Loop
I am very new to Oracle Develop PL/SQL language (so please excuse limited understanding). I am trying to use a spool command to dynamically create a filename based upon a table row values.
Below is ...
- 1,079
0
votes
2
answers
184
views
How to return different columns from the same table based on Oracle database version? (banner vs banner_full in v$version)
I need to return the BANNER_FULL column from v$version if the database version is 18+, otherwise return the BANNER column. Both exist in all versions, but banner is deprecated.
column v_dbversion ...
- 41
0
votes
0
answers
56
views
sqlplus request on multiple servers
I can't seem to be able to write my script correctly.
problem is I don't realy understand how pipes or bash work yet.
I have a script to execute on multiple servers an get the result of each back to ...
1
vote
1
answer
118
views
Need to monitor invalid objects from different DBLinks
I have to make a monitor that detects invalid objects for different DBlinks.
I've tried with a code in different files for each DBLink
-- Enable logging of results to a specific file
SPOOL C:\route
...
- 27
0
votes
0
answers
598
views
ORA-12547 when trying to login to sqlplus
After database installation (Fedora Workstation 39) I am able to login to oracle database using oracle system user using following command:
sqlplus system/manager
I have another user called user that ...
- 63