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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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