SQL & PL/SQL :: Dependencies Of Tables On Package At Column Level
Mar 29, 2010
I need to identify the dependencies of all the Tables on Packages at column level.
E.g. : XYZ is a package that uses ABC Table having E,F,G has a column, PQR - Table and its columns - R,S,T
The resultant query / code should return like this
PackageName TableName ColumnName
XYZ ABC E
XYZ ABC F
XYZ ABC G
XYZ PQR R
XYZ PQR S
XYZ PQR T
Identify the dependencies at column level.
View 5 Replies
ADVERTISEMENT
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
May 14, 2010
I have a data like,
1) manual_temp_master
auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010
2)manual_temp_detl
auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010
[code]....
I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:
sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010
View 8 Replies
View Related
Feb 24, 2010
I am unable to understand why row level triggers cant be used in mutating tables.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
View 1 Replies
View Related
Feb 9, 2013
The below query returns level and other selected columns. I need to get the max(level) 2nd column value in the below example.How to modify the query?
Ex
Level max(level) id
1 5 101
1 5 102
1 5 103
2 5 104
2 5 105
3 5 107
4 5 120
5 5 134
5 5 280
SELECT DISTINCT level lvl
,form_frms.emp_id
,form_frms.ing_emp_id
,form_frms.prve_id
,CASE
WHEN (select div_dn
[code].......
View 5 Replies
View Related
Mar 12, 2010
A Package cannot query a table that the package owner has permission to query via a role specifically:
There are schema_1 and schema_2
schema_1 owns package_1
schema_1 has select, update, insert on schema_2 via role_1
schema_1 CAN query schema_2
package_1 cannot access schema_2
(288/22 PL/SQL: ORA-00942: table or view does not exist)
How can this be resolved?
View 6 Replies
View Related
Jan 20, 2011
Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?
View 5 Replies
View Related
Jul 4, 2012
i am using 10.2.0.1.0 version of oracle.
I have gathered frequency histogram manually on one of my column of a table to provide more information to optimizer for better calculation of cardinality.
Now i have my weekend job runs for gathering stats on schema level with method_opt as 'For all column size repeat'. But i don't want the stats of above column to be overridden by the stats job. I don't want to lock the statistics of whole table, but i just want to lock the column level stats for this table.
View 7 Replies
View Related
Dec 24, 2010
through Difference between table Level, column Level ,row level constraints.
View 8 Replies
View Related
May 4, 2011
--here's my set up
CREATE USER schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;
CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;
[Code] .....
my desired outcome for user 1
user1> select * from schemaowner.user_data;
user_id column1
1 USER1
my desired outcome for user2 are as follow:
user1> select * from schemaowner.user_data;
user_id column2
2 TESTER 2
the nearest solution is from with reference to [URL]
Quote:
Adding Policies for Column-Level VPD
....
SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
WHERE d.deptno = e.deptno;
the database returns a subset of rows as follows:
ENAME DNAME JOB SAL COMM
-------------- -------------- ------------ ------------ -------------
ALLEN SALES SALESMAN 1600 300
WARD SALES SALESMAN 1250 500
MARTIN SALES SALESMAN 1250 1400
BLAKE SALES MANAGER 2850
TURNER SALES SALESMAN 1500 0
JAMES SALES CLERK 950
so how do I implement roll level and column level simultaneously?
View 3 Replies
View Related
Feb 8, 2013
I'm trying to un-escape the data of a table column using the package function - UTL_URL.Unescape . But the problem is my column is a CLOB and length of my data exceeds that acceptable by the function.
So, i'm just trying to simulate that function behavior normally. My logic is to scan all characters with pattern '%yy' (HEX) and then convert it into DECIMAL using TO_CHAR(,'XX') . But got stuck in this regexp-replace.
with xx as(
select '<p>%3Cp%3Ewefwef%3C/p%3E</p>' col from dual
)
select
UTL_URL.unescape(col) x1,
regexp_replace(col,'%([a-zA-Z0-9]){2}','2|.') x2
from
[code].......
View 17 Replies
View Related
Jul 20, 2012
How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..
View 2 Replies
View Related
Apr 22, 2010
I have a hierarchical data structure where a child can have many parents, and a parent can have many childs.See the attached file hierarchy_iliustration.jpg. This example has 4 hierarchy levels, in real problem there can be unlimited number of levels.I want to write a SQL query that lists all indirectly dependent child nodes for a given parent node.
Test structure for example attached:
CREATE TABLE TEST.NODE_T
(
ID NUMBER PRIMARY KEY,
TEXTAS VARCHAR2(254)
);
[code]....
View 5 Replies
View Related
Dec 3, 2012
Dependencies between objects in the database? I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does a table use in any PL/SQL code.
Often do you have a need to get answers to these questions? I'm developing such tool now and I want to know, if everybody interested to one.
View 9 Replies
View Related
Jun 11, 2013
find out the list of referenced objects which are dependent on INVALID objects.
I wrote the below query to get the referenced objects which depends on invalid objects.
select owner,name,type, referenced_owner,referenced_name, referenced_type, referenced_link_name , dependency_type from dba_dependencies
where type not in ('JAVA CLASS')
AND referenced_type NOT IN('JAVA CLASS')
AND (NAME, TYPE) IN (
select object_name, OBJECT_TYPE from dba_objects
WHERE STATUS='INVALID')
And the output is something like below -
Sr. No OWNERNAMETYPEREFERENCED_OWNERREFERENCED_NAMEREFERENCED_TYPEREFERENCED_LINK_NAMEDEPENDENCY_TYPE
1PUBLICVEHICLE_INSPECTION_REQUESTSYNONYMINSBIN$Uu99fysmRj6Ppn2QppCTWg==$0TABLEHARD
2PUBLICPRODUCT_HIERARCHY_MAP_TEMPSYNONYMCONFSYSBIN$ndGddLcKSDWRwsn5g91Rcg==$0TABLEHARD
3PUBLICACPKG_SUB_RECEIPTINGSYNONYMINSACPKG_SUB_RECEIPTINGPACKAGEHARD
[code].....
Our requirement is to drop all the invalid objects. But we need to know whether it effecting to any other valid objects?Do any valid objects gets INVALID after dropping of the INVALID objects. We need to know the hierarchy of it.
View 5 Replies
View Related
Aug 10, 2011
In a document from the oracle website. "If a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency".
View 4 Replies
View Related
Nov 17, 2010
what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.
View 1 Replies
View Related
Mar 4, 2011
I have been asked to take the export of a schema and then asked to remove all dependencies on tablespace. Provide me the syntax.
View 1 Replies
View Related
Feb 16, 2010
As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
View 3 Replies
View Related
May 28, 2013
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
View 8 Replies
View Related
Oct 22, 2012
Can you take an incremental backup level 1 or level 0 without archivelogs?
syntax would bebackup as compressed backupset cummulative level 1 database.
The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.
View 20 Replies
View Related
Aug 4, 2011
How do i list all tables from dba_tab_columns which contains both column name='id' and 'date'. I don't want to list the tables contains either 'id' or 'date'
View 8 Replies
View Related
Feb 13, 2012
I had face the problem regarding to database structure changes..
I had a old database which contain multiple column for 1 empno. which contain multiple subjects and its total marks.
Now the new structure is like all subjects and total marks as a row.
Eg. OLD data Structure
EMPNO SUNBJECT_CODE TOTAL_MARKS
1111 S1 45
1111 S2 21
1111 S3 55
1111 S4 41
NEW DATA STRUCTURE
EMPNO SUB1 MARK1 SUB2 MARK2 SUB3 MARK3 SUB4 MARK4
1111 S1 45 S2 21 S3 55 S4 41
I have more than 1.5 lakhs records in my old data structure..
Is there any Possibilities direct from Sql or i have to do it manually in excel sheet?
View 11 Replies
View Related
Jul 24, 2012
i want to add a column data of datatype number of all the tables at a time in a database..
i am trying with the all_tab_columns but i can get only the column info whether it is number or varchar2 or any other data type but i am unable to retrieve the column name and the sum of length of the data present in all the rows in that column...
View 39 Replies
View Related
Oct 15, 2012
is it possible to update a same column name in two tables.
I have two tables in same schema
(1)table name
pem.igp_parent
column name
igp_no.
igp_type
(2)table name
pem.igp_child
column name
igp_no.
igp_type
i want to update igp_no column in one query, how it would be possible.
View 2 Replies
View Related
Apr 11, 2013
between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.
which will execute first on update DML event ?
View 2 Replies
View Related
Jan 15, 2012
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
View 4 Replies
View Related
May 29, 2011
I need to implement the foreign key on a column of a table from 2 tables. My requirement is in bellow.
drop table t1;
create table t1 (slno number, acc_no number);
drop table t2;
create table t2 (acc_no number primary key, acc_name varchar2(100));
drop table t3;
create table t3 (acc_no1 number primary key, acc_name1 varchar2(100));
[code]...
It is provided that the values of acc_no in t2 and acc_no1 in t3 are unique.Now it required that while inserting into t1 , the system will check either t2 or t3 tables.
View 7 Replies
View Related
Oct 2, 2012
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
View 7 Replies
View Related
Aug 1, 2012
CREATE TABLE ACXIOM_RD.SOLN_STAR_MATRIX_123456_TMP1
(
ATTRIBUTE_ID NUMBER(10),
FIELD VARCHAR2(30 BYTE),
ATTRIBUTE_NAME VARCHAR2(64 BYTE),
TABLE_NAME VARCHAR2(32 BYTE)
)
[Code]....
Just now framed the query as like...
select framedquery from ( SELECT CASE WHEN table_name='MT_MEMBERS_SCOPED'
THEN ' case when 1>0 then nvl (( select value from MT_MEMBERS_SCOPED where source_code=123499 and member_id= soln_test.member_id and attribute_id= '||attribute_id ||''||'),0) else ''00'' end ' || ATTRIBUTE_NAME ||', '
WHEN table_name='MT_MEMBERS'
[Code]....
My requirement is soln_test table contains 3 records ,for each record need to match member_id in the MT_MEMBERS_SCOPED for the four records in the table.. output should be 3 records in soln_test table + 4 records in SOLN_STAR_MATRIX_123456_TMP1 totally 7 records it means column to row...
if the member_id not exists in both tables it should return '0000' value.
View 9 Replies
View Related
