SQL & PL/SQL :: %ROWCOUNT Returning Incorrect Value For Updates?

Apr 8, 2011

Consider the statement below:

Update employee e
set e.dept_id = (select d.dept_id
from dept d

[Code].....

The above is not the exact code which I am executing but an exact replica of the logic implied in my code.

Now, when i display the value of 'rows_updated' it returns a value greater than 0,i.e 3 but it should ideally return 0
since there are no records matching for the condition:
(select d.dept_id
from dept d
where e.dept_name = d.dept_name)

So, I executed the statement:
select count(*) from employee e
where emp_id = 1234
and exists
(select 1
from emp_his ee
where e.emp_id = ee.emp_id)
and the result was 3 which is the same value returned by %rowcount.

why this is happening as I am getting incorrect values in %rowcount for the number of rows updated.

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Fetch Data Based On Max Data And Rowcount

Mar 7, 2010

I have the following data with me

AddId Salaries
10600
10 50
10 400
10 300
10 200
10600
20 200
30200
30 600
30 300

Required output should be

The average of the three highest salaries exceeds 100, only those rows should be visible...wrt each addid.

View 1 Replies View Related

Concurrent Updates On A Value

Apr 18, 2013

You have a stock_amt value in one table and there is a procedure that updates and substracts from this stock_amt.

lets say in a store a have a stock amount of 50 items and this procedure, for each sale is subtracting from this value and it is not allowed to go below zero. The process, beside the update, on this column (set stock_amt = stock_amt - x) is doing a lot of other updates on other tables and it total it takes like 0.5 seconds. Everything is fine till I want to execute this procedure by 50 users in parallel.

The initial implementation, to avoid some dead locks and we put a lock on that column (stock_amt) but there where to much waits. we cannot hold that lock for 0.5 seconds.

What will be the best approach for this? For this stock amt problem, maybe the solution can be a trade like: do not update that column every time but once in a while, by another process or by a materialize view logic.

but what if my column is a critical value like a Prepay balance or bank balance and it needs to be updated in near real time. What will you do?

View 0 Replies View Related

Updates In Audit Table

May 22, 2011

I have to create a audit/history table on a master table so that I can store the old/current state of data in my audit table. I am planning to write following program.

1. Created the audit table with similar number of records.
2. Everyday at a particular time I will compare the audit/main table and push the records in audit table which are either updated or not present in the audit table so that the audit table = main table + old state of data.

I am unable to figure out the proper way to implement the point 2 above in oracle database.

View 3 Replies View Related

SQL & PL/SQL :: Count Of Updates By Week

Feb 19, 2013

I have a table of records with a date_modified column. I would like to simply report the count of updates made each week. For example, given:

Record Date_Modified
155291141/23/2013
157277201/24/2013
152619321/25/2013
142263211/28/2013
140043421/28/2013
150050431/28/2013
148315761/29/2013
154364281/31/2013
148066382/1/2013

[Code]...

I would like to return:

Week Updates
43
57
69
77
84

Although in place of the week number, providing either the value for the last day of the week or the first day of the week would be just as good.

how to go about this task.

View 7 Replies View Related

SQL & PL/SQL :: When Oracle Updates SessionID?

Apr 6, 2012

SESSIONID , returns the auditing session identifier am i right ?

my question is when oracle updating session id ?

SQL> show user;
USER is "SYS"
SQL> select sid,serial# from v$session where audsid=sys_context
2 ('userenv','sessionid');
[code]....

View 3 Replies View Related

When Recovery Catalog Updates Occur

Nov 7, 2012

The updates occur always that a significantly change was done? (Like create, rename or drop datafiles and tablespaces)

Or the RMAN catalog owner must issue "resync catalog" frequently?

View 2 Replies View Related

SQL & PL/SQL :: How Exactly Oracle Updates Rows Of A Table

May 17, 2011

Check the below simple sql stmts:

create table tab_tab(col1 number, col2 number);
insert into tab_tab values(1, 1);
insert into tab_tab values(2, 2);
insert into tab_tab values(3, null);
[code]......

My question is why the table content didn't look like:
col1 col2
----------
1 2
2 2
3 2
4 3
5 4

I thought that when oracle will update 1st, 2nd & 3rd rows then the sub query will get a count equal to 2, but the time when it goes to update the 4th & 5th row the sub query should get back a count equal to 3 & 4 resp. BUT that is not what exactly is happening!!

View 12 Replies View Related

SQL & PL/SQL :: Cursor For Loop Updates Only One Record?

Feb 17, 2012

I have a table of 3 columns:

SQL> show user
USER is "ANDREY"
SQL>
SQL>
SQL>
SQL> --create the table:

[code]...

I insert rows into it:

SQL> --fill it with data:
SQL>
SQL> insert into a(key1 , key2) values (1 , 1);
1 row created.
SQL> insert into a(key1 , key2) values (1 , 5);

[code]...

i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.

i've found out that i could do it by an SQL statement:

update a
set key3 = 'inactive'
where key2 = (
select max(key2)
from a a2 where a2.key1=a.key1
);

however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:

SQL> create or replace procedure proc1
2 IS
3
4
5 var1 a.key1%type;

[code]...

unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:

SQL> exec proc1;
PL/SQL procedure successfully completed.
SQL> select * from a;
KEY1 KEY2 KEY3
---------- ---------- ----------
1 1 active
1 5 incative
2 24 active
2 21 active

ORA-01034: ORACLE not available

View 10 Replies View Related

PL/SQL :: GL_Cancelled_Date Displaying Incorrect Year?

Sep 24, 2013

I currently have around 560 rows of bad data where

GL_Encumbered_Date and TO_CHAR(PRD.GL_ENCUMBERED_DATE,'YYYY'  display correctly.

The problem lies in

PRD.GL_CANCELLED_DATE which displays correctly (example: 30-APR-10) but TO_CHAR(PRD.GL_CANCELLED_DATE, 'DD-MON-YYYY') for the same row displays as 30-APR-0010. 

I have separated out the incorrect year with:

to_char(prd.gl_cancelled_date,'YYYY') where each of the 560 rows display 0010 instead of 2010. I am unsure if arithmetic with dates is allowed within SQL- but am curious if the operation date + number will result in a date.

If this is so, how could one go about taking 0010 as a date and add 2000 to create 2010 for: PRD.GL_CANCELLED_DATE.

View 2 Replies View Related

Incorrect Data Value When Insert Into Table

Sep 25, 2013

Would like to ask expert here, how could I insert data into oracle table where the value is 03 ? The case is like that,  the column was defined as varchar2(50) data type, and I have a csv file where the value is 03 but when load into oracle table the value become 3 instead of 03.

View 8 Replies View Related

Server Administration :: Incorrect Version Of Oracle

Apr 28, 2011

I installed 11.1.0.6. on Linux RedHat 5.5. Then upgraded to 11.1.0.7. However when I issue sqlplus /nolog and do select banner from v$version it shows 11.1.0.6. Although the install and upgrade were successful. I wonder why it does not show 11.1.0.7. I upgraded using the installer and not dbua could this be the reason why?

View 17 Replies View Related

Forms :: Incorrect Result On Assignment Statement?

Mar 3, 2011

When I run a menu (which was working fine yesterday and hasn't been changed in months) I click on an item and it runs the following

"
DECLARE
module_name VARCHAR2(125);
BEGIN
module_name := :GLOBAL.FORMS_PATH || 'covenants';
Open_Form(module_name);
END;
"

The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.

View 5 Replies View Related

SQL & PL/SQL :: Incorrect LEFT JOIN Results In 11gR2?

Jul 20, 2011

We just upgraded to 11g and have run into incorrect results for some of our LEFT JOINs. If the table, view, subquery, or WITH clause that is being LEFT JOINed to contains any constants, the results are not correct.

For example, a test (nonsensical) view such as the following is created:

create or replace view fyvtst1 as
select spriden_pidm as fyvtst1_pidm,
'Sch' as fyvtst1_test
from spriden
where spriden_last_name like 'Sch%' ;

When I run the following query, I get correct results; that is, only those with "Sch" starting their last name are listed.

select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
join fyvtst1 on fyvtst1_pidm = spriden_pidm ;

However, when I change the JOIN to a LEFT JOIN, the last column contains "Sch" for all rows, instead of NULL:

select spriden_pidm, spriden_last_name, fyvtst1_pidm, fyvtst1_test
from spriden
left join fyvtst1 on fyvtst1_pidm = spriden_pidm ;

We've discovered other quirky things related to this. A WITH clause with similar logic as the above view, when LEFT JOINed to a table will also cause the constant to appear in each row, instead of NULL (and only the value where there is a join). But when additional columns are added to the WITH, it behaves correctly.

This is easy enough to rewrite - but we have WITHs and views containing constants in numerous places, and cannot hope to track down every single one successfully before the incorrect results are used.

Finally, the NO_QUERY_TRANSFORMATION hint will force the query to work correctly. Unfortunately, it has a huge negative performance impact (one query ran for an hour, vs. 1 second in 10g).

View 10 Replies View Related

Security :: Oracle Critical Patch Updates?

Jun 7, 2013

Is this the correct forum to ask questions about Oracle CPU's?

I ran the CPU-JUL-2012 on a workstation for version 11.1.0.7, got Return Code 0 and "OPatch Succeeded".

Yet a Retina scan ran after shows no change....?

I've tried reading the availability document, but I have no experience at Oracle patching.

View 4 Replies View Related

Extended Statistics Causes Incorrect Query Results?

Nov 2, 2012

I have the following DDL:

drop table tmp_guid;

CREATE TABLE tmp_guid (
      c1   raw(16) not null
     ,c2   raw(16) not null
);    
begin

[code]...

It seems that a combination of a unique index and extended stats are to blame. Removing any one of them causes the query to also produce correct results.Extended stats basically captures the fact that despite being unique, c1 depends on c2.

View 0 Replies View Related

Application Express :: Tabular Form With Updates In 4.1

Dec 5, 2012

This problem went away for me in 4.2, but I am limited to 4.1 in production. The problem is this: I have three editable columns in my Tabular Form

1) I make a change to column 3 and press submit. The values in columns 1 & 2 (that were not changed) are overwritten with null during the update (submit). Column 3 is saved correctly.

I deleted my tabular form and re-created it thinking that I may have trashed something. But it appears that is the way it works. How do I prevent the unchanged editable columns from being overwritten with nulls?

View 2 Replies View Related

Perform Regular Updates On Several Oracle Tables

Sep 26, 2012

I am attempting to perform regular updates on several Oracle tables. The scripts performing the updates are scheduled to run every two minutes, get a value and update the table with that value.

The value doesn't always change but the scripts will still attempt to perform an update.

The same script is part of 7 objects, all of them are scheduled to run at the same time. They update the same table but never the same row.Even though the script is mostly the same on the 7 objects, they run completely independently of each other. The first object will usually perform the update without any problems but when it comes to the second object the script will time out.

View 9 Replies View Related

PL/SQL :: Incorrect Query Result Over Database Link

Jul 2, 2012

My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)

SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;

I am on 11R1 (11.1.0.7)

FOR EXAMPLE:

Local database:
CREATE TABLE TEST_TBL1
(
KEY1 NUMBER(5) NOT NULL,

[Code].....

View 8 Replies View Related

Conversion Hours Incorrect When Revert To Actual Date

May 18, 2011

I can't seem to understand why the hour is incorrect. Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour
is incorrect.

CODE
SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION
to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' ||
to_char(to_date(mod  (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test,
systimestamp,tb1.dte_computation_on_data
from
( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW
    floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data
  FROM dual)tb1;

results
---------------------------------------------------------------------------------------
REVERT_TEST             SYSTIMESTAMP                            DTE_COMPUTATION_ON_DATA
05/19/2011 03:46:18     5/19/2011 11:46:18.005171 AM +08:00     1305776778

View 1 Replies View Related

Reports & Discoverer :: Incorrect Page Number At Top Right Corner?

Jan 17, 2012

I am working on the Reports Builder 10.1.2 at the Database Oracle 11.2.0. I have developed a report , which shows the page number at top right cornet. So I have selected a field and set the Source as "page Number". While I run the report through Reports Builder,it is properly displayed the page number. But while I copied the report to unix and run through "$ORACLE_INSTANCE/config/reports/bin/rwrun.sh", the page number is showing as 65536 . By changing the Source of the field to "Total Page" or "Physical page Number" make no difference.Every times It is showing the page number 65536 in the pdf output of the report.

View 6 Replies View Related

Server Administration :: TNS-01150 / Address Of Specified Listener Name Incorrect

Sep 16, 2013

I am building a new Oracle Server. I have the following in the listener.ora file.

LISTENERProd =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLEPRODDB)(PORT=1555))
)
SID_LIST_LISTENERProd=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=c:Oracleproduct11.2.0dbhome_1)

[code]....

View 6 Replies View Related

Server Administration :: Dbms_metadata Giving Incorrect Results?

Jun 20, 2011

nlsb> select file_name,bytes from dba_data_files where tablespace_name='ARIAN_DATA';

FILE_NAME BYTES
------------------------------------------------------------ ----------
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf27lcn_.dbf 5368709120
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1txnm_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tr6v_.dbf 8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tonc_.dbf 8589934592

[code].....

dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Or is dbms_metadata unreliable?

View 1 Replies View Related

SQL & PL/SQL :: ORA-22370 / Incorrect Usage Of Method AnyData Insert

Mar 26, 2010

Any success using the sys.anydata.ConvertClob member function when trying to insert data into a Anydata column? This function has been listed in Oracle documentation since 9i, but even in 11G I get the error :

ORA-22370: incorrect usage of method AnyData Insert.

Code is pretty straight forward using a CLOB column in a after insert trigger :

Insert into TRACE_DETAIL(OBJ_ID, COLUMN_ID, OLD_VALUE, NEW_VALUE) values (logId, 73, null, sys.anydata.convertClob(:NEW.Req_Data));

View 6 Replies View Related

Server Administration :: Error In Log File - Address Of Specified Listener Name Incorrect

Feb 4, 2011

I am having issue with configuring listner name on Oracle Server.My default listener is working,But I have stopped the default listener and tried to create another listener is differnt port,but no success,It always says The address of the specifed listener name is incorrect.Below is the listener.ora file

My db name and sid name are etl

LIST1=(DESCRIPTION=(ADDRESS=
(HOST=127.0.0.1)
(PROTOCOL=TCP)
(PORT=1530)
)

and the list1.log file says The address of the specified listener is incorrect.

View 12 Replies View Related

Server Utilities :: Pre Requisite For Oracle Critical Patch Updates

Jan 20, 2011

I need some detail information regarding the CPM Patches released by oracle quarterly.

Is it compulsory to set the CPM patch when it is being realized. How to set or apply this patch. (either by runInstaller or OPatch utility i am not sure about this)

What is the difference between the interim & CPM patches.

View 3 Replies View Related

Application Express :: Cross Form Updates For Searched Record?

Aug 30, 2012

How to update a form fields which taking a searched value from another table .

I have a form that records student data and which has a drop down to select the 'student registration No' that comes from different table.

When insert or update a student record, first we select the student registration No from drop down to see if he is an existing student.

If he is already registered student then relevant student name and other details should be shown in the form and should be able to enter few other details as well.

View 3 Replies View Related

Application Express :: Flash Chart Show Incorrect Data

Jun 3, 2013

The following query is used to generate a flash chart:

select null link
'Available' as "Available",
to_number(max_licenses - consumed_licenses)
from software_detail
where capture_date = trim(GET_CAPTURE_DATE)
and software_product = :p1
and software version = :p2
[code].....

But the 2d doughnut flash chart shows:

Available = 39
Consumed = 1

How is this possible?

Oracle 11.2.0.3 RAC on Windows 2008 R2
APEX 4.2.1.00.0 with Apex Listener 2.0 on Apache Tomcat 6.

View 4 Replies View Related

PL/SQL :: Stored Procedure Taking Long Time When Updates In Loop

Feb 5, 2013

This is my stored procedure

I have below store procedure:

create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,

[Code]...

This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.

View 12 Replies View Related

PL/SQL :: Track Table Data Changes (inserts / Updates / Deletes) Including Os_user

Mar 12, 2013

I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.

If I CREATE MATERIALIZED VIEW LOG on that table.

View 4 Replies View Related







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