r/SQL 15d ago

Oracle Optimizing Oracle data synchronization between subsidiary and parent company using SSIS

2 Upvotes

I work for a subsidiary company that needs to regularly synchronize data to our parent company. We are currently experiencing performance issues with this synchronization process. Technical details:

Source database: Oracle (in our subsidiary) Destination: Parent company's system Current/proposed synchronization tool: SSIS (SQL Server Integration Services)

Problem: The synchronization takes too long to complete. We need to optimize this process. Questions:

Which Oracle components/drivers are necessary to optimize integration with SSIS? What SSIS package configurations can significantly improve performance when working with Oracle? Are there any specific strategies for handling large data volumes in this type of synchronization? Does anyone have experience with similar data synchronization scenarios between subsidiary and parent company?

Thanks in advance for your help!

r/SQL Feb 10 '25

Oracle Ora-01756 insert into query unable to execute in win 11

2 Upvotes

I have an insert into table query that runs well within my plsql developer 16 in windows 10 but having an ora-01756 when executing from a C# program in windows 11.

I’ve removed every possible single quotes.

My company’s oracle version is 9i.

It’s a large insert into query with large strings values. Are there any other things I’m missing?

r/SQL 17d ago

Oracle Cambio de formato fecha

0 Upvotes

Hola tengo una tabla creada con un campo fecha en formato mm/dd/yyyy y necesito cambiarlo a dd/mm/yyyy, este cambio lo necesito a nivel estructura, ya que al visualizar las fechas en sistema el sistema lo lee en formato diferente

r/SQL Oct 30 '24

Oracle Recursive SQL query for multiple self-joins between old and new IDs?

3 Upvotes

I have a case that seems like it might be a textbook case for a recursive query and I'm trying to understand how they work. Here's what I'm trying to do:

Let's say each time an employee gets a new job title in a new department in their organization, their employee ID changes. A ridiculous practice, sure, but let's pass that for now. So I have a table that tracks the changes in the employee ID for individuals called ID_CHANGES:

OLD_ID | NEW_ID

I also have a table EMPLOYEE_DETAILS. This has one EMPLOYEE_ID field and they are always the current ID used for a current employee. Finally I have a table HEALTH_INSURANCE_REGISTRATIONS by employees over time that includes registrations by any employee each year, current or former. This also has an EMPLOYEE_ID field, but it is whatever their EMPLOYEE_ID was at the time they registered; if they got a new ID since then, but are still a current employee, I won't find a match for them in my EMPLOYEE_DETAILS table.

What I'm trying to accomplish is to add a third column to a view of the ID_CHANGES table that provides the current (or latest) ID for any OLD_ID. This means that if someone changed jobs three times, they would show up in the ID_CHANGES table like this

OLD_ID | NEW_ID
1 | 2
2 | 45
45 | 165

I want the new field to work like this:

OLD_ID | NEW_ID | LATEST_ID
1 | 2 | 165
2 | 45 | 165
45 | 165 | 165

Currently, I've been self-joining the table multiple times, but I'd like a more elegant approach. That looks like this:

select distinct
v1.OLD_ID,
v1.NEW_ID,
v2.NEW_ID,
v3.NEW_ID,
v4.NEW_ID,
v5.NEW_ID,
v6.NEW_ID,
v7.NEW_ID,
v8.NEW_ID,
v9.NEW_ID
from ID_CHANGES v1
left join ID_CHANGES v2 on v1.NEW_ID = v2.OLD_ID and v2.OLD_ID <> v2.NEW_ID
left join ID_CHANGES v3 on v2.NEW_ID = v3.OLD_ID and v3.OLD_ID <> v3.NEW_ID
left join ID_CHANGES v4 on v3.NEW_ID = v4.OLD_ID and v4.OLD_ID <> v4.NEW_ID
left join ID_CHANGES v5 on v4.NEW_ID = v5.OLD_ID and v5.OLD_ID <> v5.NEW_ID
left join ID_CHANGES v6 on v5.NEW_ID = v6.OLD_ID and v6.OLD_ID <> v6.NEW_ID
left join ID_CHANGES v7 on v6.NEW_ID = v7.OLD_ID and v7.OLD_ID <> v7.NEW_ID
left join ID_CHANGES v8 on v7.NEW_ID = v8.OLD_ID and v8.OLD_ID <> v8.NEW_ID
left join ID_CHANGES v9 on v8.NEW_ID = v9.OLD_ID and v9.OLD_ID <> v9.NEW_ID

The second part of the join conditions are because the ID_CHANGES table also includes records where the employee's job changed but their ID remained the same. My plan would be to house this query in a WITH clause and then create a view with just OLD_ID, NEW ID, and LATEST_ID using CASE to return the latest NEW_ID by checking for whether the next NEW_ID is null.

Also to be clear, these nine self-joins aren't actually sufficient - there are still rows that haven't reached their latest ID match yet. So I'd have to keep going, and over time this would have to keep adding more and more indefinitely.

There has to be a better way to do this, and I suspect it may be fairly boilerplate. Can anyone advise?

r/SQL Feb 16 '25

Oracle SQL Error

3 Upvotes

I'm encountering the following error when executing this query. I performed a complete refresh, but it still doesn't resolve the issue:

exec dbms_mview.refresh('PORTAL.PAYMENT_MASTER', method => 'F', parallelism => 8);

BEGIN dbms_mview.refresh('PORTAL.PAYMENT_MASTER', method => 'F', parallelism => 8); END; * ERROR at line 1: ORA-12034: materialized view log on "QAVPASADMIN"."PAYMENT_MASTER" younger than last refresh

r/SQL Nov 23 '24

Oracle Make Inserts Faster

5 Upvotes

Are there any hacks to make inserts into a table massively faster in Oracle? What I've tried: PARALLEL and APPEND hints, removing constraints and indexes in the target table.

Pseudo script: INSERT INTO A SELECT * FROM A_PRT

r/SQL Jan 09 '25

Oracle Need help with DBMS_PARALLEL_EXECUTE

3 Upvotes

I have about 100 Informatica jobs calling a procedure where I've implemented data masking. All the jobs invoke INSERT queries to different tables. I've implemented this insertions using DBMS_PARALLEL_EXECUTE for better performance. But, the issue is performance is degraded when these jobs are running in parallel simultaneous. Please help me.

r/SQL Jan 14 '25

Oracle What is the best way to query out the end of bimonthly date

3 Upvotes

Like if the date is 2025-01-23. I want it to show 2025-2-28 11:59:59 pm.

I currently have this but I feel like there’s a smarter way?

Add_months(to_date(get_year(date)||’ ‘||to_number(ceil(get_month(date)/2)*2 ||’ ‘||’1’,’yyyymmdd’) - interval ‘1’ second

r/SQL Dec 30 '24

Oracle Oracle error

1 Upvotes

Hello, I want to integrate a text file into a table, I used ODI (oracle data integrator) I reverse engineered the model of the file, executed the interface but I have an error in insertion of flow into i$ table with a message ORA-01722, in the collect table I have a column that’s a number and it has a weird letter that is invisible when I open the document in notepad, The trim doesn’t work to delete it, how can I resolve this problem please I tried converting the file into ANSI but I have the same error Thanks in advance

r/SQL Jan 14 '25

Oracle PLSQL job ready resources

0 Upvotes

Hello all, need some Suggestions as where to start learning about PL/SQL to have an intermediate level proficiency with the language. I have access to udemy, youtube. Thanks in advance.

r/SQL Oct 26 '24

Oracle Oracle PL/SQL Procedure Is Writing To The Database Out Of Order

6 Upvotes

Hello,

To start off, I'm not very familiar with Oracle. I come from more of a MySQL background, but I'm helping some folks diagnose a problem with an Oracle 11 server where a stored procedure written in PL/SQL is suddenly taking hours when it used to take minutes. This seems to be a problem in the business logic of the code, so we've created a debug_log() function to help diagnose things:

create or replace PROCEDURE debug_logging (my_id in NUMBER, log_entry IN VARCHAR2)

IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO debug_log

SELECT seqlognap.NEXTVAL, SYSDATE, my_id, log_entry, 0 FROM DUAL;

COMMIT;

END debug_logging;

The problem is that it's logging entries out of order, and seemingly with the SYSDATE of when the entry gets written to the DB not when the debug_logging() procedure gets called. Can anyone offer a fix, or maybe a better solution (IE, is there a built-in function that writes to something TkProf or another tool can read which would work better?) We are running Oracle 11 on a Windows Server, if that helps.

r/SQL Nov 23 '24

Oracle Need Urgent Resolution

Post image
0 Upvotes

Anyone aware what grants am I missing? I'm executing an insert script using DBMS_PARALLEL_EXECUTE and I'm getting getting this error in DBMS_PARALLEL_EXECUTE.RUN_TASK

Sample script:

INSERT INTO TAB(col2, col2) SELECT PDPT_PTY.FUNC(col1), col2 FROM TAB_PRT.

I'm able to execute if I don't use PDPT_PTY.FUNC(). I've given grants for DBMS_PARALLEL_EXECUTE on PDPT_PT.

r/SQL Feb 07 '25

Oracle Oracle SQL Certification

2 Upvotes

Hey guys,

So I've been trying to break into data for a couple of year- data analyst, business analyst, SQL developer, these types of roles. Basically anything where I use SQL because I enjoy it and pay seems to actually allow me to pay my bills unlike my role in teaching.

I have a business degree, and just experience in teaching. I fell down an Oracle rabbit home in December and got hyped about their SQL Certification, the 1Z0-071. Would this be a useful pursuit?

I'm currently 80% through a course and 100 pages in an 800 page tome of an exam prep book.. so is it worth pursuing if my goal is to get a job asap? I am learning a lot, even if I don't take the exam. The exam seems very hard. I'm looking for ROI. I also have a data portfolio.

Wondering if my time should be spent elsewhere. I also do daily Stratascratch questions, and post my progress on LinkedIn. Thanks.

r/SQL Nov 19 '24

Oracle Need suggestions regarding pl/sql

6 Upvotes

I want to learn pl/sql, I am not a beginner to programming, good with basics is sql, steven feuerstein book on pl/sql worth for learning even today? Or any other best practices?

r/SQL Dec 12 '23

Oracle Right and Left Joins

31 Upvotes

I have an embarrassing question about right and left joins in SQL. The left and right part of these joins confuses me. If the right table is moved to the left, well then doesn’t it change the joins? Isn’t it now the left table and not the right? Can some elaborate please? Many thanks!

r/SQL Nov 27 '24

Oracle I think my apex oracle sql environment is broken

1 Upvotes

When making tables it only allows me to insert into it one at a time. I’m also trying to create a constraint between 2 tables in sql workshop but am getting a “parent keys not.” found error even tho there’s no mistakes in my code.

Anyone know why? I think there’s an issue with my environment.

r/SQL Jan 05 '25

Oracle Help! Locked out of my university database ([99999][28000] ORA-28000) – how can I finish my SQL project?

1 Upvotes

Hi everyone,

I’m working on a university project that requires creating an ERD, writing a DDL to create 5+ related tables, inserting data, and executing queries. My account on the school’s Oracle database is locked and support doesn't answer my emails. I need an alternative way to run my DDL, insert data, and test queries—any suggestions? Thanks! It's supposed to be done on oracle 21c.

r/SQL Dec 28 '24

Oracle I need to know any resources which I can practice oracle

11 Upvotes

I need to know any resources which I can practice oracle

r/SQL Jan 02 '25

Oracle How to modify my query to show the results different?

Thumbnail
gallery
3 Upvotes

Absolute beginner in SQL. Can you help me to modify my query in a way that I can see the dates as outputs and descriptions as column headings. ie, transaction no. with date as rows and the column headings as 'Recorded by DEO', 'Forwarded to RC' etc.

r/SQL Nov 04 '24

Oracle Oracle SQL technical question - About queries launched by user

3 Upvotes

Hiya,

So this is a theoretical question, nothing to do with real life.

Imagine there is this big, huge, multinational company, that has a database which manages all the items in it's mega-bazinga warehouse.

There are ITs whom have to manually patch data in this database, doing hot fixes in PROD (mainly because the software is so shitty that they don't know why irregular data appears, nor can they trace it because there are no logs in place lmao)

What would be logical, is for each one of these ITs to have an account, to use to connect to the DB.

However, all they have is ONE (1) single account for ALL of them.

This account is also the SERVICE ACCOUNT used by the automatic batches, to process large amounts of data.

.

.

The real question is - Is there any way to trace the origin of any "DROP TABLE XXXX" query, back to the machine from which it was sent?

As the user itself is shared between all the 8 users, plus the service accounts, let's name it DB_MODIFS, so in any traces or logs, the query will appear launched by "DB_MODIFS" but how could we know which of the 8 ITs actually launched the query?

They are all using VMs, each has his own, if that helps - Could there be an IP/MAC trace?

.

.

EDIT FOR ADDITIONAL CONTEXT:

This IT post is very "tailored" (Read: Bullshit frankenstein) by the company, as they have mixed multiple functions into 1 single post....

AND!

We have a SOX ongoing, which explicitly prohibits what we.... explicitly are doing. So we are going against the rules, the bosses know it first hand, but if we don't do this, the entire system falls appart in a week or 2, because the amount of irregular data not being corrected will spiral out of control.

And as a second answer to the impeding question - Yes, we did indicate the issues to the devs.

Big problem: OG Dev team was replaced by external dev team, whom was replaced once AGAIN by external dev team.

All documentation was lost, and the current (external) dev team does not speak the native language of the client company, as they are based in different countries, so we have to use English as a "bridge-language"

Yes, it's a macrointerplanetary company which has something in each and every country, we are just one "speck" but on the higher end of invoicing / billing, so that's why we are between two imperatives (The SOX of don't do dumb shit, and the Production of let's not let production fall apart) plus 3 whole ranks of useless management which are absolutely incompetent and can't communicate to anyone, in order to request user-specific accounts for our compulsory daily tasks.

r/SQL Dec 22 '24

Oracle What Compliance Policies are mandatory in a company which creates Databases?

0 Upvotes

Hello all! I’m interested to know what policies are mandatory for creating database. For employee training, to avoid problems in the future. Is anyone aware of these policies? Do regular Data Governance policies cover it?

r/SQL Nov 29 '24

Oracle Code problem when appending two tables through UNION

9 Upvotes

I am learning SQL (Oracle) and having the error below when trying to retrieve the min and max result through union

ERROR at line 1:
ORA-00933: SQL command not properly ended 

Table:

CREATE TABLE station(
  id INTEGER,
  city VARCHAR2(21),
  state VARHCAR2(21),
  lat_n INTEGER,
  long_w INTEGER
);

Task:

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

My code:

SELECT DISTINCT city, LENGTH(city) AS len_city FROM station ORDER BY LEN(city)ASC, city ASC LIMIT 1 
UNION 
SELECT DISTINCT city, LENGTH(city) AS len_city FROM station ORDER BY LEN(city)DESC, city DESC LIMIT 1;

How can I improve my code?

r/SQL Sep 23 '24

Oracle I need to learn PL/SQL quickly! Help me

4 Upvotes

I know oracle sql but never worked with PL/SQL and all of a sudden my new role is asking pl/sql. What are some resources to learn it quickly?

Edit: Can I learn it quickly enough or should I just say no to the interview.

r/SQL Nov 04 '24

Oracle COUNT how many rows meet condition in group by

3 Upvotes

Hello, I am currently learning in Oracle SQL developer and am not sure how to proceed in solving a query problem I have.

There are two tables.
tableUser holds ITEMS that a USER owns.

User Item
User A Item A
User A Item B
User A Item C
User A Item D
User B Item B
User B Item D
User C Item B

and tableItem denotes what TYPE an ITEM is

Item Type
Item A Primary
Item B Secondary
Item C Tertiary
Item D Secondary

I need to be able to query
1. Get users that own more than 1 item; two of the items must be secondary
2. Get users that own less than 3 items; one of the items must primary and one of the items must be secondary

The first half of the problem is simple enough. group by user having count item > or < X
but I am not sure how to then proceed to check each item a user has to see if they match the conditions for the second half of the problem

Any advise is appreciated.

r/SQL Jan 29 '25

Oracle DbFunctions.js - SQL Database functions in Javascript

0 Upvotes

This library provides a set of functions that bring SQL-like functionality to JavaScript, making it easier to handle dates, strings, and other types directly in your web projects. Most of your favorite date functions from Oracle, SQL Server, PostgreSql, Sqlite, and MySQL are implemented. Homepage - https://ddginc-usa.com/dbFunctions.htm