Modifying SQL Queries Before Execution

Jul 30, 2012

We have the following case: an application modifies a table in an Oracle db (10.2.0.3.0).

Unfortunately the update SQL statements from the application always use the condition "where Column1 = 'some given value'" which is wrong (never mind why).

It should be instead "where Column1 = 'some value' and Column2 = 'val for Column2'. The 'val for Column2 will be taken from the very SQL query being issued (we can make the application do an update for Column2 even if the value in it never changes).

So all the update queries from the application look at the moment like that:

"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value'".

We would like to capture them and somehow on the fly modify them to look like that:

"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value' and Column2 = 'val for Column2'".

Can a trigger "before update" do it? For some reason we cannot at the moment ask the vendor to change the hard code of the application so we are looking for a temporary workaround.

View 3 Replies


ADVERTISEMENT

Queries Running For Hours Because Of Execution Plan

Feb 1, 2011

I am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

I have 2 schemas in my application.

1. Application schema
2. EOD(End of day) schema.

End of day schema is populated from Application schema whenever user runs EOD process. The tables are pulled like this.

1. Master tables : Always deleted and reinserted at each EOD process
2. Log tables for each transaction table: Delta between the last EOD and current EOD data is pulled and are used for populating transaction tables
3. Transaction tables: These are populated from log tables pulled from previous step. The logic is like this

Now based on these tables about 30 reports are generated in EOD schema. Please note that each transaction table will have a EOD_ID and any report generated uses the where condition <transaction table>.EOD_ID = <current EOD_ID>

My log table contract_log and transaction table is contract in EOD schema.

contract_log table has data like this

contract_number contract_date customer_idqtyrateeffective_date

11/Jan/2010CUST-0110101/Jan/2010

1NULLNULLNULL112/Jan/2010

EOD on 1st Jan 2010 constructs contract table as

contract_numbercontract_datecustomer_idqtyrateeod_id

11/Jan/2010CUST-011010EOD-1

(Since the change of rate 11 is not visible on 1st Jan 2010 because it is effective on 2nd Jan 2010)

EOD on 2nd Jan 2010 constructs contract table as

contract_numbercontract_datecustomer_idqtyrateeod_id

11/Jan/2010CUST-011011EOD-2

(Since the change of rate 11 is visible on 2nd Jan 2010)

This logic is working fine. But we run more than 20-30 EODs the processing time increased to 10-15 hours.

It took some time to figure out the issue as a single query when run from toad or pl sql developer runs in few seconds but as a part of the whole package it takes 2-3 hours(each query).

The problem found was that oracle execution plan gets corrupted when the process starts. So what we did was to analyze the tables
after they are pulled. This perfectly solved our problem. Currently the whole process is taking only about 12-13 minutes where about 3 minutes is lost on analyze tables and indexes. I know this is a temporary solution as I need to get out of online analyze of tables and indexes.

My code for table and index regeneration is as below

PROCEDURE sp_gather_table_index_stats(pc_table_name VARCHAR2) IS
CURSOR cur_ind IS
SELECT index_name
FROM user_indexes
WHERE table_name = pc_table_name;
BEGIN
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_table_stats(user,' || '''' ||
pc_table_name || '''' || '); end;';
FOR cur_ind_rows IN cur_ind LOOP
EXECUTE IMMEDIATE ' begin DBMS_STATS.gather_index_stats(user,' || '''' ||
cur_ind_rows.index_name || '''' || '); end;';
END LOOP;
END;

View 1 Replies View Related

PL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time

Feb 6, 2013

Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.

View 2 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.

this is the error message:

The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.

SELECT productID,productName FROM products order by productName;

select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3

SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'

SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1

View 6 Replies View Related

Modifying Table Through A Procedures?

Aug 28, 2007

whats the best way to modify a table via a procedure for example if i have a table with records: id, fn, ln, address, tel, dob

how would i go about creating a procedure that modifies a record. The problem is what param needs to go in? i could certainly have different procedures that modify different things like one for modifying tel, one for modifying address, but is there a way to combine it all into ONE procedure so that if user wanted he could put in exec modifyTable(2, 099898787) to modify tel and then modifyTable(2, 'a new address') to modify address... ?

View 6 Replies View Related

SQL & PL/SQL :: Modifying NUMBER (38) Column

Dec 30, 2005

In chapter 17 of the 'Oracle Database 10g, The Complete Reference' it says "You can increase or decrease the number of decimal places in a NUMBER column at any time". When trying to modify a column from NUMBER(38) to NUMBER(38,3) I get the ORA-01440 error. Is there a way around this when you have the maximum number of digits (precision = 38).

View 2 Replies View Related

Modifying Fields With Huge Data

Aug 26, 2011

I have 2 questions, because they can be inter-related I am posting it in a single post. These queries are related to Oracle(PL\SQL).

1. I am trying to increase the size of a field in a table which has almost 2 million records and the query for alteration runs for almost and hour and rollsback, wondering is there a better way of doing it.

2. I have modified the size of a field in a table from Varchar2(10) to Varchar2(20), now when I tried to rollback the modification it is not letting me to change the size from Varchar2(20) to Varchar2(10). No data has been inserted after the modification.

View 1 Replies View Related

SQL & PL/SQL :: Modifying Existing Constraint On Table

Apr 1, 2013

Can we modifying the existing constraint on table?I have table level UNIQUE constraint on 3 columns of table.I need to modify the UNIQUE constraint to 2 columns? Instead of dropping/recreating the constraint, is there any option to modify the existing constraint

Ex

CREATE TABLE TEST_CONST(NUM1 NUMBER , NUM2 NUMBER , NUM3 NUMBER , UNIQUE (NUM1 ,NUM2,NUM3)); ;

SELECT * FROM USER_CONS_COLUMNS UCC WHERE UCC.TABLE_NAME LIKE 'TEST_CONST';

ALTER TABLE TEST_CONST MODIFY CONSTRAINT SYS_C0025132 UNIQUE(NUM1,NUM2);

ORA-00933: SQL command not properly ended

Third statement is not executing fine .

View 1 Replies View Related

Real Application Clusters :: Java Null Exception While Modifying Scan Listener?

Nov 5, 2013

Getting below error while including TCPS protocal at 1523 port .

 bash-3.2$ srvctl modify scan_listener -p TCP:1521/TCPS:1523Exception in thread "main" java.lang.NullPointerException        at oracle.cluster.impl.scan.ScanFactoryImpl.modifyScanListener(ScanFactoryImpl.java:978)        at oracle.cluster.scan.ScanFactory.modifyScanListener(ScanFactory.java:510)        at oracle.ops.opsctl.ModifyAction.executeSCANListener(ModifyAction.java:1600)        at

[code]...

View 3 Replies View Related

Forms :: Modifying Form On UNIX Server Using Builder Installed On Windows Desktop

May 20, 2010

We are using Oracle Applications hosed on UNIX servers. There is a custom form in XXX_TOP/forms/US folder that I need to modify.

My Windows desktop has Forms Builder 10g installed. I ft pied fmb file over from UNIX server to my Windows desktop and tried opening it. I get an error FRM-18108:

Failed to load the following objects: APPSTAND, etc etc.

Obviously I am not doing it right. What is the correct way of modifying a form file sitting on UNIX server, using Forms Builder installed on Windows desktop? Do I need to copy all dependent objects to Windows desktop along with fmb file?

View 2 Replies View Related

PL/SQL :: Error In Modifying Object Value In Triggers Defined On Object Tables

Sep 26, 2012

While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.

create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........

I executed the below insert statement, and I got the following error.

SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......

SQL> insert into person_obj_tab values (10,'Object','Original');

1 row created.Question:

1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?

View 1 Replies View Related

UNION ALL Vs 2 Queries

Nov 12, 2012

is there any difference between

- returning from the procedure 2 ref cursors containing result set of 2 queries
- returning from the procedure 1 ref cursor containing result set of that 2 queries as one (with UNION ALL)?

Will 2nd option be faster or similar to 1st?

View 1 Replies View Related

Different AWR For Same Execution?

Mar 12, 2013

we are executing a load activity every day through .NET Application, we taking a time solt for Database to ensure nobody is using at that time.But the AWR reports showing different issues on different days.

View 11 Replies View Related

Multiple Sub-queries In View

Jun 16, 2009

I have a table similar to the following,

USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...

which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.

select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user

However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:

user, latest_home_number, latest_work_number

View 3 Replies View Related

Tuning Aggregate Queries?

May 1, 2011

I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run

kumar[size="4"][/size][color="#0000FF"][/color]kumardba

View 5 Replies View Related

How To Convert SQL Queries To Oracle

Nov 16, 2006

I have to change some queries from SQL to Oracle but I couldn't convert these queries because they use some system tables in SQL that I don't know the equivalent Oracle tables. Following are SQL Queries

1. SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name

2. SELECT tS.name FROM sysobjects AS tS WHERE (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'U') AND xtype ='U') OR (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'V') AND xtype ='V')

3. SELECT o.name as TableName, c.name as FieldName, c.colid as Field_Ordinal, t.name as FieldType, c.length as FieldLength, c.prec as FieldPrecision, c.scale as FieldScale, c.isnullable, c.iscomputed, CASE WHEN c.status & 0x80 > 0 THEN 1 ELSE 0 END AS isidentity, columnproperty(o.id, c.name, 'IsRowGuidCol') as isrowguidcol FROM (sysobjects o JOIN syscolumns c ON o.id = c.id) JOIN systypes t On c.xtype = t.xtype WHERE o.xtype IN ('U', 'V') AND (t.xtype = t.xusertype)

View 2 Replies View Related

Process Queries Parallel?

Feb 2, 2005

On a tab page should be displayed the result of four indifferent queries, each based on a stored procedure.At the moment, the queries are processed serially, by the statements:

GO_BLOCK('one');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
GO_BLOCK('two');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;

Is there a way to processes the queries parallel ?

View 1 Replies View Related

SQL & PL/SQL :: Join Two Queries Together To Get Result

Dec 12, 2011

How can i join two quires together to get result.

My requirement is:

First i want to select Table as we do

Select * from tab;

Then i want to describe the table as we do

Desc WO;

How can we join these two queries to have result.

View 21 Replies View Related

SQL & PL/SQL :: Running Queries Of 2 Different SID On 1 Pane

Jun 4, 2013

I have two queries that I need to run and compare the outputs against each other. Each query runs on a different host. I can run each query on a different pane (Window - I am using Toad for running query). What I am trying to do is:

- Run both queries on a single pane
- Compare the output where if a "study" matches on both query output, then display the result.

To being with, is it possible to run the queries on a single pane by defining SID string as a part of query syntax......?

SELECT study,
TO_CHAR (completed_date, 'mm/dd/yyyy') completed_date, status
FROM ...

SELECT study, name
FROM .....

View 15 Replies View Related

SQL & PL/SQL :: Record Of Last Executed Queries

Sep 15, 2010

I have some troubles when I try to retrieve last executed queries in a database.

For example;

I run the script below:

select distinct t.first_load_time, t.sql_text, t.last_load_time, s.username
from v_$sql t, v$session s
where s.username='SYS'

And as a result, I retrieve the queries executed by SYS user. But the problem is that, if SYS user executed the same query more than once,
only the very first record is shown.

It is like this,

SYS user executes "select * from table_abc" at 10:54:35, and after that SYS executes the same query at 13:45:55. and after running
the query above, I can only see the record which was executed at 10:54:35. I need to see the both results.

View 39 Replies View Related

SQL & PL/SQL :: Combine 2 Queries Into 1 Without Using UNION

Jun 5, 2012

SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........

View 5 Replies View Related

Reports & Discoverer :: How To Run Two Different Queries

Aug 27, 2010

how to run two different Queries In One Report.

View 7 Replies View Related

SQL & PL/SQL :: XML - Union Of 2 Select Queries

Jan 15, 2013

How I can get the union result of 2 queries and put them in xml result, but I want each query to be in seperate xml element. I don t want to put 1 single xmlelement and do a From then construct a virtual table uniting the 2 subqueries

I mean I don t want something like the following:

(Select
XMLAGG(
XMLELEMENT("credit",

[Code]...

Except the 2nd alternative is not working I get error message: "SQL command not properly ended"

View 4 Replies View Related

SQL & PL/SQL :: Multiple Queries Into Different Columns?

Jan 31, 2013

I am trying to validate a monthly report so was trying to write queries to get different criteria into one table. So my first query returns all the product,second query returns all the enrolled customers, 3rd query returns all the cancelled customers and 4th query returns all the newly enrolled for a month. Is there a way I can pass the first query results into 1st column, 2 query results into 2nd column, 3 query results into 3rd column and so on.

I tired writing the SQL several different ways and have spent a day on it and still cannot figure it out. I am using SQL Developer.

View 9 Replies View Related

SQL & PL/SQL :: How To Combine Queries When WITH Clause Is Used

Jun 21, 2010

I have 4 select queries.

Query1
Query2
Query3
Query4

First Step:Combine Query1 and Query2,but the requirement is Query2 should only use the Acct_ID and Bill_ID which are output from Query1.

Second Step:Combine Query3 and Query4,but the requirement is Query4 should only use the Acct_ID and Bill_ID which are output from Query3.

Third Step: Is to now combine both the data set from First Step and Second Step.

In order to achieve my First and Second Steps I used WITH clause.

With S1 as ( Query1 ),
S2 as ( Query1 Union Query2)
Select S2.* from S1,S2
where S1.ACCT_ID=S2.ACCT_ID
AND S1.BILL_ID=S2.BILL_ID

With S3 as ( Query3 ),
S4 as ( Query3 Union Query4)
Select S2.* from S3,S4
where S3.ACCT_ID=S4.ACCT_ID
AND S3.BILL_ID=S4.BILL_ID

1. Is that approach right for achieving my First and Second step requirements ?

2. How to achieve Third Step ?

View 3 Replies View Related

PL/SQL :: Queries Executed By A Session

Jul 11, 2012

I am trying to trace the SQL statements executed against a particular database user. I don't have AUDITING enabled and I am using Oracle 11g.I have the following query :

SELECT
S.MODULE,
SQL_TEXT ,
S.EXECUTIONS
FROM
[code].....

But if multiple users running the 'APP.EXE' are connected to the same db user, I am not able to understand which OS user executed which query. So I tried to join with V$SESSION view to get the user details.

SELECT
S.MODULE,SQL_TEXT ,SN.OSUSER, SN.MACHINE, S.EXECUTIONS
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U,
V$SESSION SN
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
AND S.SQL_ID=SN.SQL_ID
ORDER BY S.LAST_LOAD_TIME

But this doesn't seems to be working(In my case it didn't return any rows) I have also tried the following

select S.SQL_TEXT,S.MODULE,S.EXECUTIONS,SN.OSUSER,SN.MACHINE,SN.SID, U.username,U.user_id from
SYS.V_$SQL S, v$open_cursor oc,SYS.ALL_USERS U,V$SESSION SN
where S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
and oc.SQL_ID=s.SQL_ID AND SN.SID=OC.SID AND SN.SADDR=OC.SADDR;

but I am not sure whether this is giving the right results. So, I have the following questions

1) How do I get the queries executed by each session?

2) The EXECUTIONS column of V_$SQL seems to the executions from all the sessions. How do I know the number of times a particular query is executed by a session?

3) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?

View 5 Replies View Related

PL/SQL :: Merging Two Queries Into A Single

Mar 19, 2013

Below are two queries , in merging two queries into a single so that out put coveres out put from below two queries

create table t_look as
select 1 a, 2 b, 3 c from dual union all
select 1 a, 20 b, 3 c from dual union all
select 1 a, 21 b, 3 c from dual union all

[Code]....

View 12 Replies View Related

PL/SQL :: Sub-queries In Delete Statement?

Sep 6, 2012

I've small doubt regarding the subqueries in delete statements. observe the below statements with their results.

SQL> alter table emp3 rename column deptno to deptid;

Table altered.

SQL> select deptid from dept;
select deptid from dept
*
ERROR at line 1:
ORA-00904: "DEPTID": invalid identifier

[code]...

but when you use same subquery in update or select stmt it throws 'invalid identifier' or similar error.Why same does not happen with delete stmt ?

View 6 Replies View Related

Auditing For Queries With No Where Clause

Jul 31, 2012

I'd like to audit a table for any SELECT queries that are executed against it with no WHERE clause. I've read the documentation on DBMS_FGA carefully, and as close as I can tell, creating a policy with a NULL audit_condition causes all queries against the table to be audited, which isn't what I'm looking for.

What I'd like is something like this:

DBMS_FGA.ADD_POLICY (
object_schema      =>  'scott',
object_name        =>  'emp',
policy_name        =>  'mypolicy1',
audit_condition    =>  'WHERE CLAUSE IS ABSENT',
audit_column       =>  'comm,sal',
[code].......  

SELECT * FROM EMP;but queries with conditions ('WHERE sal > 400', for instance) are not trapped.

I'm using 11gR2 (11.2.0.2) on OEL.

View 2 Replies View Related

SQL & PL/SQL :: Error While Execution

Jul 23, 2010

When i am trying to execute the below in sql. i am getting the error.

create or replace type sum_n as object (
nodes node_d,
constructor function sum_n return self as result,
member procedure do_s (m date,exd varchar)
);
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/9 PLS-00201: identifier 'NODE_d' must be declared

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved