Oracle is a relational DBMS (Database Management System) created by Oracle Corporation.
5
votes
4answers
354 views
Simple Oracle connection using JDBC
I'm trying to continue the learning process in Java, and I've made a simple yet useful class which will allow me to retrieve the data inside a table from an Oracle DB, using JDBC.
What I'm especially ...
4
votes
2answers
41 views
Script for updating an Oracle database
I have the following piece of Perl script for updating an Oracle database. It's working perfectly fine, but I want to know how I can simplify this script.
...
4
votes
1answer
42 views
Identify the installed Oracle driver, Scan the .NET GAC for Incompatible Assemblies
My day job is supporting a piece of software that just started using the DevArt Oracle drivers, which utilize the Oracle.DataAccess .NET drivers.
However, the ...
0
votes
1answer
24 views
Check user changed his password recently or not in specific period
I am working on Oracle SQL. What I am trying to do is:
Query a record with ID
If the record does not exists use user input value
If the record exists, compare ...
1
vote
1answer
78 views
Create multi-dim array from cursor
I'm executing a cursor. I have cut off the code how the procedure is called and executed. This part is efficient. At last I have a small cursor. I'm calling the procedure, which returns this cursor ...
1
vote
1answer
107 views
Oracle trigger to keep a copy of deleted records in another table
I am using this trigger to keep a copy of the deleted record from table Orders_Details in table Orders_Details_Deleted.
I am ...
3
votes
1answer
64 views
8
votes
1answer
76 views
Extract data from a DBMS, create a CSV dump and transfer it to an FTP server
First of all, my code does work as expected at least to some extent. The intention of the program is to extract data from a DBMS in order to create a CSV dump and transfer it to an FTP server.
My two ...
0
votes
0answers
30 views
Using OCI functions
I've never used Oracle with PHP before, what I'm doing is calling a stored procedure that returns a refcursor. Which is also a first. I use frameworks like Yii etc and don't seem to be able to use the ...
1
vote
1answer
75 views
Convert MS SQL datetime to Oracle varchar(32) interval
I have a query against an MS SQL database that takes the resulting dataset and stores it in an Oracle database table which then gets joined to another Oracle table using the column ...
4
votes
1answer
63 views
Oracle Database Communication/Operation
I am creating an application that will move specific data from one Oracle database and moving it to a MySQL database. I created this class to help keep myself organized and this class holds all of the ...
3
votes
1answer
75 views
Highest andLowest in PLSQL
I have made a procedure to display the highest and the lowest popular Items for a particular time of a given date. The procedure works with no errors or exceptions ...
2
votes
1answer
46 views
Generating business summary stats in SQL dynamically, based on an inputted date
I have the following Oracle SQL query to generate some system-stats. I am interested in making it a more efficient and readable query.
...
0
votes
2answers
72 views
1
vote
1answer
218 views
Oracle query to split 1 row into two where conditions are applicable
The scenario question:
"I'm having difficulties with splitting a single row into two individual ones."
My test schema is:
...
8
votes
2answers
204 views
Checking for Duplicates, Except
I have a query that returns records that have duplicate names within a given DBSTATUS. I used to do this with a direct connection to the remote oracle database ...
3
votes
1answer
87 views
Refactor (DRY, KISS) QueryStatus event for VS Addin
I have complex logic in QueryStatus event (CommandTarget) for a VS Addin (VS 2010).
I'm a newbie using C#.
I would like elegant, easily maintainable code that follows DRY and KISS principles. Maybe ...
3
votes
3answers
310 views
Counting business days in a month, ignoring holidays
This table simply contains date information. I went ahead and used what I currently have since this is a one-time operation. However, since I only recently started working with Oracle, I would like to ...
6
votes
2answers
91 views
Out of one Oracle into another
A project that I have been working on required data to be transferred from an Oracle database into MySQL. The process I devised for that transfer involved a query (included below) on the Oracle ...
8
votes
1answer
3k views
Filter out date records which don't fall within or span a date range
I have records which have specific time spans. The need is to select records which adhere to the following three rules (read them as or conditions) concerning a ...
6
votes
1answer
59 views
Trying to avoid a double full scan with aggregates in query
I have a statistics query I'm trying to run against the below table:
...
2
votes
1answer
409 views
Refactor Oracle Entity Framework lambda query
I had a ugly problem and wrote an ugly query.
Query creates a cache that is used to get person id when I have {full_name, id_code?, birth_date?}. By question mark I mean that it is perfectly valid ...
1
vote
1answer
129 views
Slow delete query on table with composite index
after profiling my application, it turns out that a single method is taking 3 minutes to run, which is about a third of the total runtime.
The method deletes approx. 400.000 rows from each table (<...
5
votes
1answer
122 views
Tuning a query for performance
I am running this query and it takes several minutes to complete. How can I tune it (unfortunately I don't have the privileges to run an SQL tuning advisor)?
...
5
votes
1answer
3k views
Length limited listagg for muliple m:n relations in a view
I have a few views that all follow the same pattern. They select a primary key from some main table and then a few semicolon seperated strings of captions of related rows over many-to-many relations. ...
3
votes
2answers
853 views
1
vote
1answer
809 views
SQL double nested query
This query produces the results I want.
Is there a less wordy or more effective way to write this query?
...
3
votes
2answers
125 views
Searching through tables for a value
I have a PL/SQL stored procedure which searches for a row in 3 different tables (not all columns are the same).
The logic goes as follows:
...
10
votes
2answers
434 views
Comparing client lists with Cross Joins
I've written a query to compare the clients in our database with the people in a list that I've received. It needs to check if anyone from the list is one of our clients. I've created a temporary ...
4
votes
1answer
3k views
Creating a reusable C#.NET Oracle Query Builder
I write a metric ton of queries for an Oracle environment every day. These are reports that often require multiple separate queries that no end of joins and sub-queries will satisfy, hence lots of ...
4
votes
3answers
1k views
3
votes
1answer
76 views
Oracle nested query
Essentially I'm attempting to select the top read (based on read_date), and check if it has a ...
1
vote
0answers
91 views
3
votes
1answer
1k views
Getting list from an Oracle database and storing it in an object
I have two admin groups - VCS, and VPN. For each group, I need to get the list of all the people in that group, along with their IDs.
What is the best way to do this? Could you please suggest if it ...
9
votes
1answer
898 views
Compare Oracle Table with SQL Server Table and Update/Insert
Below is the current code which is used to update/insert records from Oracle view to SQL Server table using Dapper. There is not a field to check last record updated date in the oracle view so I have ...
1
vote
1answer
648 views
5
votes
2answers
267 views
Query for building a view
I am currently trying to improve on a query that is being used to build a view. The query is in PL/SQL, in an Oracle database. There are 3 different types of reports (100,200, and 300) that are ...
3
votes
1answer
125 views
XML schema for Database Unit Tests
I am attempting to develop an XML schema (XSD) to represent a set of unit tests for some code. (Specifically, I am testing Oracle PL/SQL stored procedures.)
I would like to have something that ...
3
votes
1answer
187 views
Self Join Exercise. Have I over-complicated it?
This my 3rd question on the same exercise, but by no means a duplicate. The two previous questions were posted on StackOverflow here and here.
Now I'm posting my Oracle solution (below) that works. ...
2
votes
1answer
527 views
Merging CSV data into database
Base functionality:
Reading a CSV file and inserting in database after replacing values with web macro.
Reading values from CSV @ first header information NO,NAME next to that, then read one by one ...
0
votes
1answer
398 views
Simplify Oracle SQL Unions
I have the following SQL query I am trying to use (save/make easy future modifications to) that is essentially the same query over and over and over, UNIONed each ...
4
votes
4answers
260 views
Simplify Oracle SQL: Treat null date as 'N' and non-null date as 'Y'
This works, but nvl AND case seem redundant.
Basically, if null then 'N' else 'Y.
CHICKEN_HATCH_DATE is a DATE data type.
...
0
votes
2answers
343 views
How to optimize this SQL delete
I want to optimize the performance of this SQL query. If I populate this hashtable with one million keys the query will take around minute. How I can optimize this Java method for faster execution?
<...
3
votes
1answer
323 views
Parsing Oracle errors with a ksh function
Some time ago I wrote a small routine to run some quick n' dirty queries (and with that I mean it is not used for large queries) against an Oracle DB, but also wanted to do it a bit easier to parse ...
3
votes
1answer
155 views
Oracle to MSSQL data migration is slow on INSERT
I wrote a method that gathers data from an Oracle server, formats and encrypts the data then inserts it into a MS SQL server. The method moves about 60000 records and takes a bit long and is a little ...
3
votes
2answers
1k views
Oracle PL/SQL decryption function breaking index?
I have a package called pkg_crypto.
My select looks like this:
...
1
vote
2answers
7k views
5
votes
2answers
2k views
Strategy Pattern for Oracle Database?
I'm trying to implement the Strategy Pattern into my current code base. I would like to know if i'm going in the right direction on this?
IOracleDB.cs:
...
4
votes
1answer
36k views
Returning total number of rows in query
Oftentimes I find myself wanting the total number of rows returned by a query even though I only may display 50 or so per page. Instead of doing this in multiple queries like so:
...
