Questions tagged [oracle-sql-developer]
Oracle SQL Developer is a free tool for SQL & PL/SQL development and reporting similar to Quest's Toad or Allround Automations' PL/SQL Developer.
263
questions
1
vote
4
answers
39
views
Loop through 30 columns in Oracle DB to check if a value exists
I have a table which contains an ID in column one and that ID can have up to 30 different codes and dates associated with it.
ID | CODE_1 | CODE_1_DATE | CODE_2 | CODE_2_DATE | CODE_3 | CODE_3_DATE
...
- 13
0
votes
0
answers
22
views
find Sqldeveloper 19.2 download link
Im trying to find a link to download and install this version but it impossible. Dont appear on oracle.
Someone knows any link or place to install it?
thanks!!
0
votes
1
answer
37
views
How to connect / auth users to Oracle pluggable DB using SQL developer
I am struggling connecting to a pluggable DB for which I created local users.
I have an Oracle 21c XE on my machine.
The CDB is XE.
I logged into SID=XE with sys as sysdba.
There I created a PDB as ...
- 103
0
votes
1
answer
48
views
SqlDeveloper - Real Time SQL Monitor doesn't log statements even though I can see them in Sessions
I'm trying to trace an application that is running on my machine.
In SQL Developer, I go into Real Time SQL Monitor and run the tool. I can see various queries being logged, but none from the ...
- 111
1
vote
1
answer
97
views
I have data with column names as dates and I need to unpivot this columns into Dates column and values. How can i get this in oracle SQL developer
so my input_table is
This is to be unpivot the columns of dates into Date and Values as in the output
What is the oracle SQL developer code I need to write to achieve this output table. Please help ...
0
votes
2
answers
631
views
Truncate tables in other schema
I have logged into a dev02 schema with a user and password and another schema dev08 with a different user/password in the Oracle database. Now from dev02, I run the command: truncate table dev08....
- 101
1
vote
2
answers
114
views
How to view data of a failing row in a view
I've the following situation:
I've a view where there's a row that somehow generates incorrect data and when it's attempted to be visualized or exported it shows an error message like this:
00000 - &...
- 145
0
votes
0
answers
58
views
Database Copy Fail to Copy Multiple Tables
I'm using Oracle SQL Developer v20.x and v22.x. I am trying to copy the data in two tables
from one schema to another but I keep getting the below error. Both tables have less than
100 rows. I'm ...
- 157
0
votes
1
answer
425
views
Connect to remote Oracle database from local SQL Developer client
I installed Oracle Linux 21c on the remote machine, tested it in Linux shell, and it worked as expected (I can query the testing table I created). But I am stuck when trying to connect to the database ...
- 3
0
votes
2
answers
54
views
Converting string with DIN 1421 pattern to human-readable
In the Oracle DB there is a table that stores "DIN"s attribute (VARCHAR2(1200 BYTE)), see a table/image below.
Each string follows the DIN 1421 pattern.
+------------------+
| DIN ...
- 177
0
votes
0
answers
633
views
Wrong number or types of arguments in call to procedure
I am getting Below error message in the catch exception:
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'WS_GET_WEB_TREE_NODES' ORA-06550: line 1, column 7: PL/...
0
votes
1
answer
21
views
Is it safe to export and then import encrypted database information in Oracle SQLdeveloper?
I'd like to perform some tests in a database by deleting the content of certain tables and then restoring the content they previosuly had once the test is finished.
But problem is that these tables ...
- 103
-1
votes
2
answers
28
views
Find in what table I can find multiple collumns
I am trying to write a query that will show me the tables that contains all the following columns: ProductGroup, ProductClass and ProductID.
But I am not very successful at it. Can somebody help me ...
- 1
0
votes
1
answer
256
views
schema ownership/permissions in MS-SQL vs Oracle
Hopefully I'm using the write terms here. I'm pretty good with concepts usually but don't always know the technical terms to describe it correctly. Here goes...
I'm used to working in MS SQL Server ...
- 173
1
vote
1
answer
71
views
Get explain plan for query with inline function
I have a query that has an inline function:
with function with_f(p_text in varchar2) return varchar2 is
begin
return p_text;
end;
select with_f(dummy) from dual
Is there a way to get the ...
- 1,437
0
votes
1
answer
64
views
How to get DML SQL of uncommited statements in Oracle 19c?
I'd like to know if there is a way to get the DML (insert, delete) SQL of uncommitted statements in Oracle 19c in my own session?
For example, if I have typed some statements and I haven't committed ...
- 203
-1
votes
1
answer
42
views
Trying to understand this quiz question on INSERT
Why will there be 2 rows and not 1? The rollback is meant to take it back to the last COMMIT function which in this case is inputted after one INSERT
- 39
-2
votes
1
answer
30
views
Why will this query only produce 1 row and not 2? And why does it have a value of 11 and not 1? [closed]
What do I need to change to make this query output 2 rows, first value 1 and second value 10?
CREATE TABLE cars (car_id NUMBER, top_speed NUMBER);
CREATE SEQUENCE car_counter START WITH 1 INCREMENT ...
- 39
-4
votes
1
answer
37
views
How do I line the SELECT LIST with the rules of of VIEW creation in this query? [closed]
I'm trying to understand exactly where I went wrong in this query
- 39
0
votes
2
answers
102
views
How to request enhancement to Oracle SQL Developer
Regarding Oracle's SQL Developer tool:
Is there a way to request that Oracle make an enhancement to the tool?
For example, I want to ask Oracle to add this functionality: Generate WITH clause from ...
- 1,437
0
votes
1
answer
87
views
sqldeveloper export tool ignores filter by schema
I'm trying to export an Oracle schema to an .sql file. It has no tables, but it has some custom types and packages.
During the steps "specify objects" and "specify data" I click &...
- 263
0
votes
1
answer
402
views
How can I get SQL Developer to compile code that includes code within the DDL?
I am preparing SQL scripts that can deploy a set of code to a target environment with minimal fuss. I want the executing DBA to just sign on to the target database and execute "install_release....
- 125
0
votes
0
answers
311
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 ...
- 1
0
votes
1
answer
199
views
SQL Developer: View only permissions
I am using SQL Developer, generally to assist in writing reports against various databases but there is one application that is run on an annual basis and at the end of that processing (about a 3 hour ...
- 11
0
votes
2
answers
274
views
Oracle Lag() on date output wrong data [closed]
Oracle DB 19 - Sql developper 19 - NLS DD-MON-RR
I have a table
ID NAME EFFDT NODE PARENT
SHA DEPT 01-JAN-01 A10200 TOP
SHA DEPT 04-JAN-11 A10200 X10200
SHA ...
- 29
-2
votes
1
answer
2k
views
How to get the top record from each set of group [duplicate]
This question is for Oracle SQL DEV
What I am trying to accomplish is to pull the top record for each person (last name + First name + Dob), using their most recent travel date. To help illustrate my ...
- 11
0
votes
1
answer
1k
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 ...
2
votes
3
answers
106
views
Ordering streets by their names and polysyllabic numbers in SQL query
In Oracle DB there is a table with some data:
LAG
HNR
STREET
00020
44
Aachener Straße
00020
44/2
Aachener Straße
00020
44/1
Aachener Straße
00020
46
Aachener Straße
00020
46/6
Aachener Straße
...
- 177
0
votes
1
answer
4k
views
Case statement in update statement
I am trying to update a table. I want to use a case statement to decide what column to update. I keep getting the error message, "syntax error at or near "case"." Can someone ...
-3
votes
1
answer
990
views
Cannot connect new users to pluggable database
I'm new to Oracle DB (coming from MS SQL Server).
For a start, I downloaded a Docker container for Oracle Database 18c XE.
When I run that container, after creating a pluggable database, I don't seem ...
- 133
-1
votes
1
answer
344
views
Oracle SQL: How to get order of operations on multiple AND and OR conditional groups in this query
I am not very familiar with Oracle operators precedence, my question is how do ORACLE process the order of operations in multiple AND and OR blocks, and how can I properly use the parentheses so the ...
- 99
0
votes
1
answer
75
views
Counting failed attempt
Here is my code:
create table failed_login_attempts(
login_id number(5) primary key,
date_created timestamp default systimestamp,
email varchar2(110),
ip varchar2(24),
mac varchar2(18),
attempt number(...
- 13
0
votes
3
answers
2k
views
REGEXP_SUBSTR to split delimited values
I have a field called FREQUENCY and it has values like '10,0;30'. How do I write a regular expression to get the result like this:
FREQUENCY
10
0
30
3
votes
4
answers
672
views
Concatenating only desirable values with LISTAGG
In the Oracle DB there is a table with data "YEARS", see a table/image below
+-----+-----------+------------------+
| FID | I0 | BJA |
+-----+-----------+-----------------...
- 177
-1
votes
1
answer
788
views
How do I log in to SQL Developer for the first time?
I just installed Oracle 19c and SQL Developer. I open SQL Developer and try to establish a new connection but I get
"Listener refused the connection with the following error:ORA-12505, TNS:...
- 125
0
votes
2
answers
18k
views
Where can I find old versions of SQL Developer (4.x)? [closed]
I need a version that works with 11.2.0.4 driver or lower.
In Oracle site there is the current version 20.4.1 and a link to the previous one. Each previous version you click has a link to the previous ...
- 143
0
votes
1
answer
467
views
Export mutliple query output into one excel sheet using Redshift and SQL Developer
I am connected to Redshift from SQL Developer 19.2.1.
I have a requirement to export multiple query output into a single excel sheet, one query output should be in one Tab. Please advise on how to ...
0
votes
1
answer
473
views
How can I use COUNT in the same query with ROUND() and AVG()?
I need to write a query where I need to first count the tickets sold for spectacles in a theater, then calculate the average number of tickets sold in each theater and to be round it to two decimal ...
user221450
0
votes
1
answer
1k
views
Cannot connect to 19c with SQL Developer though database is mounted and opened, error ORA-01034
I have recently had a server crash that is hosting both an 11g and 19c databases. Upon restart by IT staff, I discovered that one of the two 19c instances was idle. The 11g instances are all mounted, ...
2
votes
1
answer
174
views
Where can I find SQL Developer Database examples (scripts) to add data to practice?
Where can I find SQL Developer Database examples (scripts) to add data to practice?
I require SQL scripts that will add tables and data on those tables in order to practice with Power Bi.
2
votes
2
answers
784
views
identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared [closed]
I am using oracle 11g express edition and I am getting the below error when trying to create a change set.
Error report:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_CDC_PUBLISH....
- 131
-1
votes
2
answers
819
views
Why would a scheduled job fail in SQL Developer?
I am trying to create a scheduled job in Oracle SQL Developer that would run once a month and I am testing the Scheduler/Jobs feature where I have created a simple SQL query to test the ability to do ...
- 111
0
votes
1
answer
613
views
How do you change the storage parameters of a already made table (sql developer)?
Summarize the problem
I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change ...
- 107
0
votes
1
answer
2k
views
Oracle: ORA-00942: "table or view does not exist" in SQL Developer
in SQL Developer when i get session information he return me this error message: (ORA-00942: "table or view does not exist"). Can i resolve this with add grant to privilege of my user? Wich ...
- 3
1
vote
2
answers
53k
views
How to solve Vendor Code 17002 when connecting to a remote Oracle database?
I have an Oracle database 12c standard edition release 12.2.0.1.0 running on a Linux instance of AWS ec2. When I try to connect to it remotely from my machine (macOS) using SQL Developer I get an IO ...
- 193
1
vote
0
answers
271
views
Unit Testing ORDS - Oracle Rest Data Services
I've been wanting to develop some unit tests for my ORDS functions as part of my push for better CI/CD - I am running ORDS functions on an Oracle Autonomous Database 19c, and while I'm developing a ...
- 121
0
votes
2
answers
351
views
Aggregate Query in SQL Developer
Below is my query. i get an error msg. it works when i remove the fields without the sum. I have added a screen shot of the data. Thanks!
SELECT
S.PRODUCT_ID,
SUM(DISTINCT S.AWARD_AMT),...
- 1
0
votes
2
answers
65
views
Filtering based on data in excel
I was given an excel file with account numbers in them and told to write a query that returns the account numbers in the list that meet a certain criteria. While I was able to write the query in ...
- 1
1
vote
0
answers
150
views
SQL Developer - can't create DB objects in mysql
I have SQL Developer Version 19.2.1.247 on Debian Linux, with Java version "13.0.2" 2020-01-14 (I know, not supported, but it seems to work), and JDBC installed from mysql-connector-java_8.0.19-...
- 231
0
votes
1
answer
901
views
Unable to get the DBMS Logs in Oracle SQl Developer [closed]
I am using Oracle SQL Developer (Version 18.4.0.376) and Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production "CORE 10.2.0.1.0 Production" TNS for ...
- 113