SQL & PL/SQL :: How To Get Information From Another Identical Table

Oct 16, 2010

adjusting a query. I have a table which looks like this:

CREATE TABLE PF.PF_ANOMALIES
(
ID VARCHAR2(10 BYTE) NOT NULL,
PLAN_ID VARCHAR2(10 BYTE),
CLIENT_ID VARCHAR2(10 BYTE),
SEDOL_CODE VARCHAR2(7 BYTE),
LTY_ID VARCHAR2(10 BYTE),

[Code]....

I have the following query that will return me the anomalies of client transactions (based on joining other tables for extra info too)

SELECT pc.client_id,
pc.client_name,
NVL(pa.sedol_code, 'N/A'),
DECODE(ps.stock_name, NULL,
DECODE(plt.lty_name, NULL, 'N/A', UPPER(plt.lty_name) || ' LEDGER - ' || pa.orig_price_ccy),

[Code]....

-- Get cash anomalies on top as these will have 'N/A' sedols I have another temp table that also gets populated with anomalies:

CREATE GLOBAL TEMPORARY TABLE PF.PF_ANOMALIES_TMP
(
ID VARCHAR2(10 BYTE) NOT NULL,
FVD_TYPE VARCHAR2(10 BYTE) NOT NULL,
CLIENT_ID VARCHAR2(10 BYTE),
SEDOL_CODE VARCHAR2(7 BYTE),

[Code]..

My problem is that I also want my query to return rows from this tmp table too.

View 17 Replies


ADVERTISEMENT

Fetch All Information From Big Table For Those Key Present In Small Table

Jun 28, 2012

I am having two tables

Table 1 having 16 cror rows .
Table 2 having 1000 rows

I joined both the tables and fetch all inforamtion from big table for those key present in small table.Join query taking more time to fetch the rows .

View 2 Replies View Related

SQL & PL/SQL :: Partitioned Table Information

Jan 8, 2013

How to know weather table is partitioned or not ? Is there any data dictionary view to know the information about the Partitioned type,keys on table ?

View 4 Replies View Related

PL/SQL :: Pulling DDL Information For A Table

Nov 5, 2013

We are running Oracle 10g. I need to pull the DDL information from our Oracle tables. The following SQL statement returns the result as a "HUGECLOB". Is there a way to convert the result to text in varchar2 data type? Since tables being processed have numerous partitions, the DDL information for them is quite large, therefore, using substring would not be a viable alternative. 

[code]select DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE') from DUAL; [/code]

View 6 Replies View Related

Duplicate Sequence Number For Identical Rows

Jul 5, 2010

I have a table:

Name
_____
Smith Street
Smith Street
John Street
Ed Street
Ed Street
Ed Street

and need to assign sequence numbers only when the record (Name) changes, e.g. :

Name Seq
_____ ____
Smith Street 1
Smith Street 1
John Street 2
Ed Street 3
Ed Street 3
Ed Street 3

I have experimented with row_number partition but then i just get the sequence returning to 1 when the name value changes.

If I grouped the records by Name I would like to have unique, sequential numbers: 1, 2, 3 but where there is the same name I would like the sequence to stop and the number to replicate?

View 9 Replies View Related

SQL & PL/SQL :: Finding Identical Rows And Updating Records?

May 30, 2013

I need to find the identical rows in the below table based on ID column and update the previous identical record's end_date with latest record's start_date-1.

"ID" "NAME" "START_DATE" "END_DATE"
1 "a" 05-MAR-10 31-DEC-99
1 "B" 30-MAY-12 31-DEC-99
1 "C" 30-MAY-13 31-DEC-99
2 "A" 02-APR-10 31-DEC-99
2 "B" 02-APR-10 31-DEC-99
2 "C" 30-MAY-12 31-DEC-99
3 "C" 04-MAR-10 31-DEC-99

Result should be like below format..
"ID" "NAME" "START_DATE" "END_DATE"
1 "a" 05-MAR-10 29-MAY-12
1 "B" 30-MAY-12 29-MAY-13
1 "C" 30-MAY-13 31-DEC-99
2 "A" 02-APR-10 01-APR-10
2 "B" 02-APR-10 29-MAY-12
2 "C" 30-MAY-12 31-DEC-99
3 "C" 04-MAR-10 31-DEC-99

View 7 Replies View Related

2 Identical Database - Creating Script From DB1 And Run It To Restore DB2

Dec 27, 2010

We have a 2 database identical ( Say DB1 & DB2). In that, one of the database(DB2) base got corrupted. We cant recover back the database due to hard disk problem. So we did a new installation of database and patched to current level.

Now I want make DB2 up and running. So I though of generating a script from DB1 and run it in DB2 to restore back.

View 3 Replies View Related

SQL & PL/SQL :: Comparing Schemas - Content (data) Identical

Jan 20, 2011

I have two schemas with 149 tables in each schema, what I need to do is to prove that the content(data) between the two schemas is identical. I know that all the table names between the two schemas are the same, just need to prove that there is no difference in data.

So the query needs to prove that Schema A content = Schema B content

I know I cant do a simple select from Schema A.tab1 minus select Schema B.tab1 but since there are 159 tables, I am not sure if this is an efficient way of doing it.

View 14 Replies View Related

Server Administration :: How To View Table Constraints Information

Mar 13, 2010

I know that you can view all constraints in a table using $USER_CONSTRAINTS or $USER_INDEXES view. What i find difficult is that the information in those views is not really well presented.

I find it very hard to follow. For example, just to find out which columns are foreign keys and which tables they refer to is really tedious. Is there a simpler way to view all the constraints especially foreign keys of a table and including which table columns they refer to in the child table.

I like to see a diagramatic representaion of all the tables involved so i like to see both the parent and the child tables, which columns are related and using which FKs etc. is there an easier way to view this information?

View 4 Replies View Related

Networking And Gateways :: Connection Fail Two Database With Identical Sid Name On Different Hosts

Feb 18, 2013

My production database server is cloned for test environnement . I kept the same name of SID for both environnement but on different hosts.

I got error when try to connect to test database : TNS:protocol adapter error.

Below the the TNSNAMES.ORA files:

----- production host ---

HOFL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = asus-sol)(PORT = 1521))

[Code].....

View 10 Replies View Related

How To Delete Table Information In SQL Loader Log File Output In Oracle

Jan 19, 2011

I have a SQL Loader Log File Output. I would like to delete Table Information from the output file.Following is my code.

//PATH OPENED AS TEXT BY STREAM READER
System.IO.StreamReader strm = File.OpenText(batchFileName);
//OUTPUT

[Code]....

View 8 Replies View Related

SQL & PL/SQL :: Copy Supplemental Login Information From Renamed Table To New Tables?

Oct 19, 2010

I renamed 100 tables and recreated them, now I need to copy supplemental login information from renamed table to new tables. Environment is oracle 10G.

OS - Solaris

View 9 Replies View Related

Query MP3 ID3 Information?

Apr 25, 2007

Is it possible to query a mp3 file's ID3 information, or the properties where you can put in artist, album, etc. information?

View 1 Replies View Related

Changing A Lot Of Information In Database

Feb 18, 2011

I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).

what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.

View 1 Replies View Related

SQL & PL/SQL :: Fetch Information For A Foreign Key

Jul 13, 2010

the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.

View 3 Replies View Related

Security :: See Audit Log Information?

Dec 17, 2011

i enabled auditing by setting

audit_trail=db,extended in spfile,and restart the database and after that i issued command to audit as below :
audit select on emp by access;

but how to see audit log information(ie from which table),i checked dba_audit_trail but it doesnot have any record.

View 4 Replies View Related

SQL & PL/SQL :: Information On Package Contents

Oct 25, 2012

I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.

To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...

SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;

However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).

So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?

Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?

View 3 Replies View Related

Capture Information From AWR Snapshots

Oct 20, 2010

The sqlcache is getting over-written.I would like to capture information from AWR snapshots and feed those (as a workload) into the DBMS_ADVISOR.I can't see where it's possible (other than manually creating my workload from AWR information)

View 3 Replies View Related

PL/SQL :: Retrieve Primary Key Information

Dec 20, 2012

How can i retrieve which tables have composite primary keys, and only one of the primary key columns is a foreign key to another table?

Like:
CREATE TABLE club (
clubId NUMBER,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (ClubId)
);
  
CREATE TABLE  team (
teamid NUMBER(10),
clubid NUMBER(10),
teamname VARCHAR(10) NOT NULL,
PRIMARY KEY (teamid, clubid),
FOREIGN KEY (clubid) REFERENCES club (clubid));So in this case, the team table

View 4 Replies View Related

How To Get Information Of Hidden Parameters

Sep 16, 2012

)How do you view the value of the parameter that is being used by instance? show parameter..?
2)How do you get the information of hidden parameters
3)What is the database object that stores information related to various types of db connections over network
4)How do you verify since when the db session is running
5)How do you verify the Originating machine details of the database session
6)How do you verify the name of program that the db session is running
7)What is the naming convenion of Base tables. Where is the information of base tables stored?
8)How are dynamic views created. Whre is the information of dynamic views stored?

View 8 Replies View Related

Redo Log Information - Arch Vs LGWR?

Oct 25, 2012

Redo log information can be transmitted in one of two ways from the primary database to the standby database: either by ARCH or LGWR.

1. when ARCH involves
2. when LGWR involves
FAL_Client = (Should i enter net service name or Db name or service_name
FAL_Server = Should i enter net service name or Db name or service_name )
FAL_CLIENT='whichone'

[code]...

View 9 Replies View Related

Sequence Nextval Storage Information?

Dec 23, 2012

Sequence nextval is a pseodocolumn, but where the value of nextval is stored and why before we use CURRVAL for a sequence in our session, we must first initialize the sequence with NEXTVAL?

View 5 Replies View Related

SQL & PL/SQL :: Hierarchical Query To Get Metadata Information?

Mar 5, 2010

i'm trying to display the heirarichal relationship between the tables (parents-child-subchild).

[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID

[code]....

Expected output

table_name path
DEPT DEPT
EMP DEPT/EMP
EMP_AUTHORIZATION DEPT/EMP/EMP_AUTHORIZATION
EMP_AUTHRIZATION_CARD DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD
EMP_AUTHRIZATION_DUP DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD/EMP_AUTHRIZATION_DUP

but by using below query i am getting complete heirarichy.

SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Clause Retrieving Information From Varray

Apr 12, 2012

select *
from
table(it_count_tc('bbs_swe_tve_db'))

gives this output:

COLUMN_VALUE
------------
0
2
37
8
0
1
0
0
1
3
0
0

12 rows selected

BUT: what I what is like this

Jan Feb Mars April May ...
0 2 37 8 0 ...

it_count_tc is a function returning a varray of 12 integers input is a varchar2 string

View 2 Replies View Related

SQL & PL/SQL :: Version Wise Objects Information?

Dec 11, 2012

Is there any way to capture the version wise objects (Packages) information from the oracle data base.

Ex : I have package P1 which was created on 01-NOV-2012 with version 1 . After 10 days same package has been updated version 2 with some enhancements.Like that the package will be updated according to the latest requirement.

Now I need to capture the total audit trail history of the package with version wise specific changes and when changes has been occurred hoe can I achieve this?

View 2 Replies View Related

Security :: Audit Trail Information?

Jun 7, 2011

I am trying to setup logon/logoff auditing for our databases which reside in 9i and 10G on sun solaris servers. I am asked to turn on auditing sending the audit data to syslog! How exactly do you do that?

View 3 Replies View Related

SQL & PL/SQL :: Taking Selective Information From String?

Jan 31, 2011

I want to take selective output from String input like

Input:'Six Thousand * Four Hundred Seventy Two Rupees And Zero'

Output:'Six Thousand Four Hundred Seventy Two Rupees'

I am using substr and instr functions but not getting the desired output.

View 22 Replies View Related

Client Tools :: How To See Information Before Commit

Feb 24, 2013

I want to see the pending operations before commit. How can I view what operations are going to commit when I enter the commit.

View 7 Replies View Related

Forms :: Getting Hardware Information In Oracle 6i?

Feb 9, 2010

How Can We Get Hardware Information In Oracle forms 6i for example harddisk size,volume,processor etc.

View 1 Replies View Related

How To Collect Synonym Information And Compare

Oct 29, 2013

We are making Oracle Insight Application based on Oracle 11gR2 DB.We have two DEV environments. On Oct 17, we had trouble in one DEV environment.From dba_common_audit_trail , we found some user did drop public synonym statement and we doubt it as cause of the problem. I would like to collect synonym information of the two DEV environment.1How could I do that ?2And we are thinking of using Logminer to investigate the cause of problem.Logminer is usable to investigate the cause of problem if we have the REDO log of Oct 17 ?

View 5 Replies View Related







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