For reasons beyond my control, I need to join two tables and I need null values to match. The best option I could think of was to spit out a UUID and use that as my comparison value but it seems ugly SELECT * FROM T1 JOIN T2 ON nvl(T1.SOMECOL,'f44087d5935dccbda23f71f3e9beb491') = nvl(T2.SOMECOL,'f44087d5935dccbda23f71f3e9beb491') ...
I often write queries wherein I pivot data and end up with NULL values that I want to collapse. E.g. data like the following: id time_in time_out 1 2009-11-01 1 2009-10-30 2 2008-12-15 2 2009-02-03 I then do an outer query like so: SELECT id, MIN(time_in) AS time_in, MIN(time_out) A...
Here's a good one for any Oracle gurus out there. I'm working on a web page that dynamically configures Oracle DB backup settings in a closed environment. Right now, I have everything set up to generate scheduled jobs that run pre-determined RMAN scripts that already exist on the Database server's disk. This works, but I want to go a ste...
I started using the jOra eclipse plugin. The plugin seems pretty robust and I'm hoping to stop using SQLDeveloper for 95% of my database needs. Many of our tables have columns of type TIMESTAMP with LOCAL TIME ZONE. I can connect to the oracle DB using a jdbc string and the plugin seems to function very well. However, when I try to...
I know that this may be a trivial question but I really cannot find the work folder in Oracle BEA weblogic 10.3. Unlike the Apache Tomcat where it is clearly in the work directory usually under WEB-INF. I already researched it on JavaRanch and it said that the directory is at: \bea\user_projects\domains\YOUR_DOMAIN_NAME\servers\AdminSe...
Hi guys: We can use RAISE to fire an exception. What particular situations do we need to use RAISE_APPLICATION_ERROR? Thanks. ...
Hi, We're getting this error once a day on a script that runs every two hours, but at different times of the day. ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "PACKAGE.NAME" has been invalidated ORA-06508: PL/SQL: could not find program unit being called: "PACKAGE.N...
We are in the process of moving from the .NET Microsoft oracle driver to the ODP.NET driver. One of the problems we have had is this error: ORA-12705: Cannot access NLS data files or invalid environment specified We were able to stop the error by modifying the registry and changing the setting (see this question) In our case we chang...
Why does this simple query return 'ORA-00936: missing expression' (the database is Oracle as you can tell): SELECT * FROM transactions WHERE id NOT LIKE '1%' AND NOT LIKE '2%' I feel silly, but what am I doing wrong? ...
How can i run multiple sql files from one main sql files in postgres. For example in oracle Inside Main.sql i can specify n number of @Child.sql , and then i can run Main.sql to run all child.sql 's . How can i do this in the postgres. Thanks! Pratik ...
Is there an equivalent for Oracle's decode() in Access (or Jet, for that matter). The problem I am facing is: I should sort (order) a resultset based basically upon a status and a date (with all records having status = 2) at the end. In Oracle I'd go something like select ... from ... where .. order by decode(status, 2, 0, 1)...
I have a little question about creating a table using another tables. my code: create table TB_OLAP_TELEFONIA as select ID AS LOG_ID, HORAFIM AS LOG_HORA, DURACAO AS LOG_DURA, TO_CHAR( HORAFIM, 'D') AS LOG_DIA_SEMANA, TO_NUMBER( SUBSTR( NUMEROA, 3, 1 ) ) AS LOG...
A trigger in an Oracle 10g generates upsert and delete messages for a subset of rows in a regular table. These messages consist out of two fields: A unique row id. A non-unique id. When consuming these message I want to impose an order on the deque process that respects the following constraints: Messages must be dequeued in insert...
I am using the Oracle Java interface for AQ and want to dequeue messages. These messages consist out of two fields: A unique row id. A non-unique id. I successfully decoded messages using a single RAW payload like this: AQDequeueOption option = new AQDequeueOption(); option.setDequeueMode(AQDequeueOption.DEQUEUE_REMOVE); AQMessage ...
Sounds easy, right? I have a job that is running that I'd like to stop (it's been running for way to long, and there is clearly a problem with it). Well, when I try to stop the job, I get this message: *Cause: An attempt was made to stop a job that was not running. However, when I try to drop the job entirely, because I REALLY don't...
I'm wondering if you have a table that contains 24 million record, how does that impact performance (does each insert/update/delete) take significantly longer to go through? This is our Audit table, so when we make change changes in other tables we log then on to the Audit tale, does it also take significantly longer to carry out these...
Good morning, everyone. I'm currently working on a new web application and it's being developed using Oracle APEX. It's basically a ten page wizard-style web form that gets filled out and submitted. In nearly all cases, only three or four of these pages will be used and the other exist to present special-case information to the user. ...
Hi, I am trying to see the amount of redo generated by different insert statements. I see that for the first insert in the transaction , the redo size is being shown as zero. The very next insert generates a redo of 2664 bytes (probably for the last two inserts). All subsequent inserts generate the expected number of redo. The database...
Working with Oracle 11g here. I'm trying to figure out how to write a specific query against a sample table below: ID TYPE PRIORITY STATUS DATE ---------------------------------------------------- 1 Q A NEW 01-OCT-2009 1 Q A NEW 01-OCT-2009 1 Q A NEW 01-OCT-2009...
This is a database modeling question. I normally model one-to-many with a standard parent-child table setup, and I normally model many-to-many with an association table between the 2 tables. In this one case, the current requirement calls for a one-to-many relationship. But the client was talking about some potential future requiremen...