I'm experimenting with a change capture architecture for ETL processing that is based on ora_rowscn, and have rebuilt the source tables with ROWDEPENDENCIES to isolate the SCN's to only those rows modified (as opposed to block-level tagging). I'm aware of the extra 6 bytes/row of space overhead, but it is not obvious to me what other im...
How to optimize the response time for the following query: SELECT /*+parallel */ cc.customer_id AS customer_id, cc.title1 AS title1, cc.forename1 AS forename1, cc.forename2 AS forename2, cc.surname1 AS surname1, cc.surname2 AS surname2, cc.company_flag AS company_flag,...
I have an oracle DB where a program is writing into two columns when it updates the table. The 2nd column is based on the value from the 1st column. Well over time people have hand edited the database and forgot to insert values into the 2nd column. I'd like to write a simple sql statement that updates all columns and syncs the 2nd co...
What I'm trying to do is automatically change the output format of dates in my sql queries without having to specify it (PHP/Oracle). I'm using to_char(pi.date_of_birth , 'YYYY/MM/DD') as date_of_birth to change the formatting of my queries. What I'd like to be able to do is apply this to any date returned by the '*' operator. My probl...
@GilShalit posted this comment a year ago: "Well, we have come to distrust ODP (.Net 2.0) after fighting a memory leak (in code we supplied to a customer) in GetOracleDecimal for over a year... Good luck!" – GilShalit Aug 27 '09 at 12:44 How did you solve it? We have a service that queries an Oracle database every few mi...
I have PL/SQL function that returns cursor that holds 28 columns and 8100 rows. When I execute that function from SQL Plus I got the results right away and in SQL Developer I'm running script that takes looong time (about 80 seconds). The same happen from Java code. When number of columns reduced to 2 then I got response in less than 4...
I need to find some records created in a range of quarters. For example, I'm looking for all records created between the 4th quarter of 2008 and the 1st quarter of 2010. I have this in my WHERE-clause: ...and r.record_create_date between to_date('2008 4','YYYY Q') and to_date('2010 1','YYYY Q') but Orac...
So i need to update some dates on an Oracle Database, the field is a datetime, but i only want the date updated and leave the time as it is... There query goes like this: update table SET field = to_date('07312010','MMDDYY'); But it's overriding the hours, minutes and seconds from the field, i want to update the date but i want th...
I have a query that concatenates (manually by ||) a handful of columns, one of which is an XMLTYPE field (calling getClobVal() on it) . If I spool the query output using sqlplus with the following options set: set long 30000; set pagesize 0; set feedback off; I get all of the content as desired, but with cr/lf's after 80 characters, ...
I need to query the orders table to get a count of all orders for yesterdays transactions, grouped by the ship date. Then I need to have an additional column to give the total orders for the ship date for all transactions. When I added this second column, time of processing grew exponentially (which was expected) to 109s. Is there any...
Hi, Im facing problem getting ORA object (SCHEMA.MYOBJ_TYPE) from database via procedure, lets say proc_1(?,?). Im getting invalid name pattern. Im using ORA objects and their equivalents generated by JPublisher from DB. The procedure works when I try it on database level (object is filled up with data). But when I call it from Java i...
Hi, I want to clone my existing oracle database structure without data including packages, users, tablespaces etc. I read some methods but they all copied data as well. Is there anyway out in order to do this? Thanks in advance. ...
I've got a hibernate query that returns a list of objects and I want to order by a title. This is a user maintained field and some of our customers like prefixing their titles with numbers, this isn't something I can control. The data is something like this: - 1 first thing - 2 second thing - 5 fifth thing - 10 tenth thing - 20 twe...
INFORMIX-SE: My users periodically run an SQL script [REORG.SQL] which unloads all rows from a table in sorted order to two separate files (actives and inactives), drops the table, re-creates the table, loads the sorted loadfiles back into it, creates a cluster index on the same column I sorted my unload files by, creates other supporti...
I have a domain object and annotated as follows @Entity @Table(name = "REQUEST") public class Request { /** * Unique id for this request */ @Id @GeneratedValue @Column(name = "EQ_ID") private long requestId; /** * */ @Column(name = "EMAIL_ID") private String emailId; /** * */ @Column(name = "REQUEST_DATE") private Date...
Why Insert Statemets Perform Slower on a Indexed Table? ...
All the demos of Visual Studio Lightswitch use SQL-Server, it is possible to access data from Oracle using Visual Studio Lightswitch? ...
Hi, does anyone know of a way, or even if its possible to call a stored procedure from within another? And if so how would you do it? Here is my test code: SET SERVEROUTPUT ON; DROP PROCEDURE test_sp_1; DROP PROCEDURE test_sp; CREATE PROCEDURE test_sp AS BEGIN DBMS_OUTPUT.PUT_LINE('Test works'); END; / CREATE PROCEDURE test_sp_1 ...
Can you guys show me how to get this ERD in 5th normal form? We did a class assignment yesterday where we had a real client come in and explain to us his need my group came up with this model I am trying to take it a step further and see if I can get it to 5th normal form so that I can understand it better as next week we will be doing a...
Here is the script I am running DROP SEQUENCE S_JobStatus; CREATE SEQUENCE S_JobStatus INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMINVALUE ; -- -- TABLE: JobStatus -- DROP TABLE JobStatus; CREATE TABLE JobStatus( Id NUMBER(10, 0) NOT NULL, ShortName NUMBER(10, 0) NOT NULL, Descripti...