In oracle I can specify the columns, which should induce a firing of a trigger: create or replace trigger my_trigger before update of col1, col2, col3 on my_table for each row begin // the trigger code will be executed only if col1 or col2 or col3 was updated end; Now I want to do the following: I don't want the trigger to fire, whe...
This is the first time I've dealt with Oracle, and I'm having a hard time understanding why I'm receiving this error. I'm using Oracle's ODT.NET w/ C# with the following code in a query's where clause: WHERE table.Variable1 = :VarA AND (:VarB IS NULL OR table.Variable2 LIKE '%' || :VarB || '%') AND (:VarC IS NULL OR table.Variable3...
I have 3 very large stored procedure I need convert from SQL Server to Oracle, is that a converter out there that anyone has tried that would work for this? I really don't want to have to do this manually if there is another option. ...
Why isn't my PL/SQL duration function working correctly? In the query below, I manually calculate the 'hh:mm' the same way as in the function below. However, I get different results. Calling Query: WITH durdays AS (SELECT SYSDATE - (SYSDATE - (95 / 1440)) AS durdays FROM DUAL) SELECT TRUNC (24 * durdays) AS durhrs, MOD (TRUNC ...
Hey all, MySQL/MSSQL has a neat little inline if function you can use within queries to detect null values, as shown below. SELECT ... foo.a_field AS "a_field", SELECT if(foo.bar is null, 0, foo.bar) AS "bar", foo.a_field AS "a_field", ... The problem I'm running into now is that this code is not safe to run on an Oracle database,...
The following query (V_TITRATION_RESULTS) is a view uses a row-to-column pivot which returns about 20,000 rows: SELECT test.created_on as "Created On", r_titr as "Titrator", r_fact as "Factor" FROM (SELECT test_id, MAX(CASE WHEN result_tmpl_id = 2484 THEN result END) r_titr, MAX(CASE WH...
I'm unsure how someone would break my SQL if I simply replace all incoming single quotes with double quotes. Can someone enlighten me for both Oracle and SQL Server examples? Thanks. string sql1 = "select * from users where user_id = '" + "O'Reily".Replace("'", "''").Replace("\", "") + "'"; ==> "select * from users where user_id = 'O...
How to create a partition table in oracle10g? ...
How do you exclude a set of values when using a left outer join? Consider the following query: SELECT i.id, i.location, area.description FROM incident_vw i, area_vw area WHERE i.area_code = area.code(+) AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P') The query executes, yet none of the NULL area code...
I have to study and modify a big and poorly documented codebase written in pl/sql. I use Oracle SQL Developer to navigate through it, but it become tedious because there is not a "go to declaration" nor "find usages" option so I have to go manually to the referred package to find the function or procedure I want to understand, or grep an...
Hi All, Im Using toad for Oracle to run a query which is taking much too long to run, sometimes over 15 minutes. The query is pulling memos which are left to be approved by managers. The query is not bringing back alot of rows. Typically when it is run it will return about 30 or 40 rows. The query needs to access a few tables for its...
I just read this question, and a solution states that: The fact that you don't know you got the NO_DATA_FOUND exception suggests that you have made one of the biggest errors PL/SQL developers ever make: EXCEPTION -- Never do this in real code!!! WHEN OTHERS THEN NULL; END; Could you explain me what is the error in th...
I am trying to concatenate some fields to return a single string for each row from an oracle table. This is in 10g. Here is my query: SELECT t.value || '|' || t.label || '|' t.label_abbrv || '||' "mylist" FROM list_value t WHERE t.value BETWEEN 195001 AND 195300; I'm getting the "FROM keyword not found where expected" error. Th...
Here's the background: Version: Oracle 8i (Don't hate me for being out of date. We are upgrading!) SQL> describe idcpdata Name Null? Type ----------------------------------------- -------- --------------------------- ID NOT NULL NUMBER(9) DAY ...
I am receiving the following error: ORA-12406: unauthorized SQL statement for policy I am executing a stored procedure that executes the following two Oracle Label Security stored procedures: SA_COMPONENTS.CREATE_GROUP SA_LABEL_ADMIN.CREATE_LABEL In the same transaction, but not the same procedure, I am trying to insert into a tabl...
I just installed the Oracle Instant Client for Mac OSX with the SQLPlus extension and have the following problem. I can connect to a database instance and schema and I can do queries but when I try the "describe command" it just hangs. For example: SQL> SELECT COUNT(*) FROM APPROVABLETAB; COUNT(*) ---------- 8 SQL> desc APPROVABLE...
I have some one-many normalized data that looks like this. a | x a | y a | z b | i b | j b | k What query will return the data such that the "many" side is represented as a CSV string? a | x,y,z b | i,j,k ...
I've been asked to convert this statement from PL/SQL to SQL Server: UPDATE pdi_nb_process_complete pdi_end1 SET (pdi_end1.adp_issue_date_time, pdi_end1.adp_print_date_time) = (SELECT DISTINCT pdi_end1.completion_date + 2 ,pdi_end1.completion_date FROM cl100 cl WHERE cl.polref = pdi_end1.policy_reference AND ...
I have a parent-child relationship in an Oracle 9i database-table like: parent | child 1 | 2 2 | 3 2 | 4 null | 1 1 | 8 I need to get the absolute parent from a given child. Say, I have child 4, it has to give me parent: 1 I already looked to CONNECT BY , but I can't find the solution. ...
Do you need to create an index for fields of group by fields in an Oracle database? For example: select * from some_table where field_one is not null and field_two = ? group by field_three, field_four, field_five I was testing the indexes I created for the above and the only relevant index for this query is an index created for fie...