PL/SQL :: SQL To Find Out Changes?

May 17, 2013

I have a table with 50 columns, lets call it Table_A . There is a identical table called Table_B. The data in Table_B gets flushed every night and data from Table_A is moved to Table_B. A new set of data is inserted into Table_A. Now I need to find out which field value is changed and what is changed . A minus command can give the changed records, but how to get the what was prior value for each changed field . Here is what I am looking for

WITH TABLE_A AS  (  SELECT 1 ID, 'JOHN' NAME,  'SALES' DIV FROM DUAL
UNION
        SELECT 2 ID, 'MARRY' NAME,  'ACCOUNTS' DIV FROM DUAL
UNION
        SELECT 3 ID, 'KIM' NAME,  'SERVICE' DIV FROM DUAL),

[code]...

what i want is something that will spit out this (only one record, because there is only one diff record found in the above query.)

ID,     OLD_ID,     NAME,   OLD_NAME ,  DIV ,       OLD_DIV
--      ------      ----    --------    ---         -------
3           3       KIM       KIM       SERVICE     SALESThe above one is for sample purpose, The actual table has 50 fields, and except the ID field, all other field values might change.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Find Pk1_value

Mar 28, 2011

i need more important to know about this query,below query in where clause i have one condition like 'pk1_value =New_Deliveries.DELIVERY_ID" but in my New_Deliveries table i don't have pk1_value column,how to find that column.

select MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL))
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Find Inserted Row

May 30, 2012

i m create a table emp . all user of database have privileged to read and write the emp table. now How can identify that which user have insert row in emp file ?

View 4 Replies View Related

SQL & PL/SQL :: Find Last Day Of Given Month?

Sep 25, 2012

I am facing a problem to find the last day of the month which is stored in a variable. i tried the following query which gave me an appropriate answer

select to_char(last_day(sysdate),'DD-MON-YY')from dual

ans : 30-SEP-2012

But I am having only the month in my hand through which I want to find its last day. I cant use sysdate for my query.

View 2 Replies View Related

SQL & PL/SQL :: Best Way To Find Duplicates?

Feb 14, 2012

I have to load 50 Million records in a table. I also need to ensure that no duplicate records enter. Business demand is that It may send 2 Million records today, 1 Million records tomorrow, 3 million records on the next day and so on...

I have loaded 6 Million records into the table, but onwards speed of loading records (with duplicate check) is decreasing.

View 16 Replies View Related

SQL & PL/SQL :: Find Maximum Value In A Row?

Mar 28, 2012

I need to find the maxmimum value in a row, which consists 4 fields.

Columns: field_1, field_2, field_3, field_4

values : 2 , 4, 5, 1

the output should be field_3 =5

View 2 Replies View Related

SQL & PL/SQL :: Find Out Object Name

Feb 16, 2010

I want to find out the name of objects like procedures,functions which are using a specific column of table.

for example i have a table 'Orafaq' which has column 'Iss_Ide', i want to know which objects like procedures,functions,views are using column "Iss_Ide" of table orafaq

View 3 Replies View Related

PL/SQL :: Find No Of Records?

Apr 27, 2013

How to find out the no.of records in a cursor for loop without using counter variable. can i use %rowcount inside for loop?

declare
  cursor dept_cur
  is
    select deptno,dname,loc
    from dept;

[code]...

View 10 Replies View Related

Find Ddl For Any Indexes?

Oct 9, 2012

on 11g R2 on Win 2008

How to find ddl for any indexes built on MY_VW ?

View 6 Replies View Related

How To Find SessionID Of A Job

Jul 25, 2012

how can I find SessionID of Oracle Job? In Oracle 9i Jobs were running under Sessions with SID = 0. This seems to be not the case under Oracle 11g.

View 10 Replies View Related

How To Find Out The Value Of USER_DUMP_DEST

Apr 10, 2013

As titled.

View 15 Replies View Related

How To Find Table Fragmentation

Jul 22, 2011

A query to find the table fragmentation.

View 1 Replies View Related

How To Find Table For Script

Aug 19, 2010

i have table like TWRCHAIN_WS_SUM. it is regularly updated by another object like procedure, function. how can i find which object is updating the particular table across the database.

View 4 Replies View Related

Find Out What SQL Statements Currently Running

Feb 26, 2009

From database server, I need to monitor the details about the sql statements which are being currenlt running in client machines.

I tried with V_$SQLTEXT view where I can only see the SQL statements, hash value,address,SQL_id. but I'm not able to get the user name,name the client machine .

find out these details?.Which Data Dictionary i need to use ?.

View 2 Replies View Related

Unable To Find A Table?

Mar 22, 2011

why am i getting this?

SQL> SELECT TABLE_NAME,owner from dba_TABLES where TABLE_NAME in ('BSNL_ACCOUNT_DATA','BSNL_SERVICE_DATA','BSNL_PKG_DETAILS');

TABLE_NAME OWNER
------------------------------ ------------------------------
BSNL_ACCOUNT_DATA BPREAD
BSNL_SERVICE_DATA BPREAD

but the table exists

SQL> select count(*) from BPREAD.BSNL_PKG_DETAILS;

COUNT(*)
----------
3704

SQL> select TABLE_NAME,owner from dba_TABLES where TABLE_NAME='BSNL_PKG_DETAILS';

no rows selected

why is it showing no rows even if the table exists

View 3 Replies View Related

Find Out Row Count From Each Table?

May 6, 2009

I have written a script to get count of each tables of a schema.

DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;

[Code].....

View 1 Replies View Related

How To Find Summary Value By Range

Jul 22, 2011

this is my table

create table sequence ( id int not null primary key);

insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);

i need the answer to group the sequence like this

start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1

what should i do?

View 1 Replies View Related

How To Find 95th Percentile

Jul 14, 2012

I have a table as:

ID VALUE
---------- ----------
1 2
1 14
1 41
1 42
1 42
1 48
1 56
1 65
1 86
1 93
2 15
2 36
2 42
2 48
2 62
2 75
2 75
2 85
2 85
2 99

There are millions of distinct ID and more than 5000 VALUE's(in ascending order) for each ID.

I need to find the VALUE stored at [int((95 / 100) * COUNT(ID)) - 1] column for each ID, where COUNT(ID) is the no of rows for each ID.

The result will be as

ID VALUE
---------- ----------
1 86
2 85

View 1 Replies View Related

Find Nth Highest Salary?

Jul 18, 2011

How does the following underlined query works to find the nth highest salary

SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);

View 3 Replies View Related

How To Find SQL Text History

May 27, 2011

I have SQL ID , sql with bind variables ("SYS_B_005")from AWR report which is taken last week.I need find the full exact sql text with bind variable value (sql ran 1 week before).

View 4 Replies View Related

How To Find When ORACLE Started

Sep 5, 2010

I'm connecting from SQL Server to ORACLE (ORACLE is linkedserver) and I'm trying to find out when ORACLE started and how long it has been running. Is there query or command in ORACLE that I can use to check this? Is there more than one option to find this out and is it same for all ORACLE versions or diffferent from version to version?

View 1 Replies View Related

Find Out Row Count From Each Table

Mar 31, 2009

SQL> select count(*) from tab;
COUNT(*)
----------
63

I need to find out how many rows in each table.I've tried with cursor.

View 9 Replies View Related

Find Column Dependencies

Oct 22, 2010

I am having to pull out all the dependencies for a column as below.

When I select a column in a table, it should list

1. All Views that use this column and table
2. All Stored procs that use this column and table
3. All Functions that use this column and table
4. All Pl/SQL scripts that use this column and table.

Is there a easy way of getting this information from the Oracle dba tables?

View 3 Replies View Related

SQL & PL/SQL :: How To Find Summary Value By Range

Jul 22, 2011

this is my table

create table sequence (
id int not null primary key
);

insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);

i need the answer to group the sequence like this

start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1

what should i do?

View 7 Replies View Related

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related

SQL & PL/SQL :: Find Out Privileges On A Role

Oct 20, 2010

How can i find out or list all the privileges that were given to a role with sqlplus cmd?

View 2 Replies View Related

SQL & PL/SQL :: Find Dependent Objects?

Feb 9, 2011

Say i have a Procedure or package or any other stored subprogram, I need a script say to which i can pass this Procedure/Package/View etc name and then the code should give me the data results as which all other stored subprograms are used within the particular stored program name which we mentioned in our WHERE or Predicate clause

View 4 Replies View Related

SQL & PL/SQL :: Find Out Columns In Database Which Are Not Used

Jul 1, 2013

How to find in the oracle10g database if there is any column which is not being used at all.If the data is there in the column,how to check when was it last populated.

View 3 Replies View Related

SQL & PL/SQL :: How To Find Table That Contains Certain Column

Nov 9, 2011

To find out column in which table it is located.

View 1 Replies View Related

SQL & PL/SQL :: Find Difference In Two Schemas?

Feb 25, 2013

I want to find difference between the objects(tables(columns,constraints), views, sequences) in two schemas. One schema is staging and one is development . In development lot of changes are made , Is there a script or a opensource tool which will bring out the difference in columns, constraints in each table and sequences , views etc.

View 2 Replies View Related







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