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
ADVERTISEMENT
Jan 26, 2012
I want to make an Information System of Subjects and its corresponding sections.Below is my desired output:
+ Sections
- Section 1
- Advanced Algebra
- Physics
- Section 2
[code]...
I wanna do it in a hierarchical tree, since I don't know how to do it,
View 28 Replies
View Related
Feb 22, 2012
I am using the dbms_xmlgen.getXMLfunction to get the result of any query in xml format.
With this XML I also want the metadata information about all the fields used in the query (passed to getXML function). Is it possible and how can I achieve this.
I tried to Google it but couldn't find any solution , it's easy to do it in java where I can get the resultset meta data from the resultset but I have to do it in Oracle function since I want the result in xml format and want to use the oracle XML API.
You may think why I need metadata , the reason is the application will later use this information to sort the data contained in these fields according to their data type provided to.
View 7 Replies
View Related
Apr 23, 2010
i got problem that i want to know Metadata in my Oracle DB 2 version there're OracleDB 9i and OracleDB 10g ,So i want to know about sql command to show all metadata of these Database, because i want to list My metadata and improve to Metadata's Standard(ISO/IEC 11179). How can i show all metadata of these database. is it the same sql command line with this 2 version Database to show all metadata?
View 1 Replies
View Related
Jan 27, 2009
I'm trying to get different level using hierarchical query in sql.my table is
item_id child_item_id
------------------------------
p21 p25
p21 p22
p22 p23
p22 p24
p25 p27
p25 p26
p27 p28
p27 p29
p30 p31
p30 p32
I want to display result with respective levels.
for example p21 ,p30 are coming under first level .
p22,p25 ,P31,P32are 2nd level.
p23,p24,p26,p27 are 3rd level
p28,p29 are FOURTH level item_id's.
Already I 'VE tried using CONNECT BY PRIOR clause.BUT STILL I COULDN'T GET THE RESULT.
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
Sep 24, 2012
How to count siblings in a hierarchical query? I'm trying to get a listing of employees
SELECT LEVEL, last_name||', '||first_name AS Manager, count(employee_id)
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY level
This returns 4 levels. I'm wanting to add up the number of siblings under the level 2 instead of listing them all.
View 1 Replies
View Related
Aug 19, 2013
I want Hierarchical Query..I have Table of chart of account
CREATE TABLE COA
(
ACCOUNT_CODE CHAR(19),
ACCOUNT_TITLE VARCHAR2(70),
ACC_TYPE Char(1),
PARENT_CODE CHAR(19)
)
[code]....
View 2 Replies
View Related
Apr 24, 2010
Below query. Below is the DDL , DML and expected output.
create table tab_1 (col1 varchar2(10), col2 varchar2(10));
select * from tab_1
Output of query
col1 col2
12
23
34
[code]...
The above query is not giving below expected output.
Output:
4 4/3/2/1
3 3/2/1
7 7/6/5
8 8/7/6/5
11 11/10/9
View 6 Replies
View Related
Apr 19, 2013
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
View 1 Replies
View Related
Apr 25, 2013
I want Hierarical query to display my Chart_Of_Account. I want to make a tree Form in 6i error i am getting is connect by nocycle prior account_code=parent_code
*
ERROR at line 4:
ORA-00920: invalid relational operator
Table
Create Table Chart_Of_Account (Account_Code Char(19), Account_Title varchar2(70), Parent_Code Char(19))
insert into Chart_Of_Account ('DGHOA01010101000001','TEST','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000002','TEST1','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000003','TEST2','DGHOA01010100000000')
select -1,level,account_code||' - '||ACCOUNT_Title,'NULL',to_char(ACCOUNT_CODE)
connect_by_iscycle from chart_of_account
start with Substr(account_code,13,7)='0000000'
connect by nocycle prior account_code=parent_code
View 3 Replies
View Related
Sep 7, 2012
Here is my case,
create table t (id number,row_id number primary key ,value number);ID row_id value
1 1 10
1 2 11
1 3 1
2 4 11
3 5 11
3 6 12
4 7 12
4 8 12
4 9 13
4 10 11
4 11 10Requirement is
1) To get all the ID that have the value 10
2) The row_ids for the ID I got in I
Can I do this with Hierarchical query. If not which one of below will be fast?
select * from t where id in (select id from t where value=10);
or
select T2.* from t T1, t T2 where T1.ID=t2.id and T1.VALUE=10;
I have billion of rows so I am looking for either Hierarchical query to solve it or some other way . All the three column the index.
View 5 Replies
View Related
Jul 23, 2013
The full statement is:
SELECT v.key$ FROM VERSION_TABLE v, DOCUMENT_TABLE d, CLASS_TABLE z WHERE
v.documentKey = d.key$ AND
d.classKey = z.key$ AND
z.key$ IN (SELECT zz.key$ FROM CLASS_TABLE zz
START WITH zz.name = 'esDTTemplate'
CONNECT BY PRIOR zz.key$ = zz.parentKey) AND
v.ESGROUP = 'SearchOperatorsMapping' ORDER BY d.name
Now I noticed that the subquery is never used to seed the join: indexes - if any - are used. Otherwise a full table scan is performed.In the example - if ESGROUP is indexed, then it's chosen to start the join evaluation. If not, a full table scan is performed.Is there any way to suggest to the optimizer to use the subquery in case there are no indexes - as a fallback ?
In the above example where VERSION_TABLE contains nearly two million records, the no index solution takes 60 secs. vs. less than 1 sec. in the index case.Wrapping the hierarchical query in a inline view leads to same result.
PS: the execution plan (without index) is:
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT ORDER BY | | 1 | 171 | 9 (23)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 171 | 8 (13)| 00:00:01 |
[code]...
View 3 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 27, 2013
creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone
(
emplid VARCHAR2(10),
seqno NUMBER,
action VARCHAR2(3),
office NUMBER,
mobile NUMBER
);
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999)
/
INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888)
/
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE
---------- ---------- --- ---------- ----------
100 1 OLD 1111 9999
100 1 NEW 2222 9999
100 2 OLD 2222 9999
100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
EMPLIDFIELDOLDNEW
----- ------ ---- -----
100OFFICE11112222
100MOBILE99998888
Challenges :
1) Make pivoting with old and new values
2) For each field we have to show old and new values
3)if old and new values are same, dont show in report.
View 8 Replies
View Related
Aug 29, 2012
I have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
View 15 Replies
View Related
Nov 10, 2010
i have many partitions in one table. i need metadata for some of the partitions. We have any option to get the metadata for partitions only.because while selecthing the table meatdata i am getting long script.
View 2 Replies
View Related
Jun 6, 2012
I'm trying to get the Create-Statements of all tables of several schemas:
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'<schmema>')
FROM (SELECT TABLE_NAME, OWNER FROM SYS.ALL_TABLES WHERE owner='<schema>') u
Is there a way to create a loop to handle several schemas listed in a array, for example?
View 10 Replies
View Related
Jul 11, 2011
I have to build something like an export-interface-mappingtool. So user defines more or less a query and I have to display the attributes of this query to enable a mapping.
If I build up a ref cursor fom this query, I did not find the possibility to read its metadata (mostly Type/length is enough).
What I do not want to do is creating a temp. table or parse the query string to derive it from the dictionary.
Is it possible at all to get the metadata of a dynamic SQL ?
View 6 Replies
View Related
May 18, 2010
I'm currently busy with database consolidation, so I'm searching for a solution to generate some useful DDL to prepare the new target database before importing the application's data. This should include TABLESPACE DDL and all additional users with their grants.
So first I thought of developing a simple script, which will create the CREATE TABLESPACE DDL but with transformed datafile paths.But my throws some errors and I don't understand why:
ORA-31604: invalid NAME parameter "STORAGE" for object type TABLESPACE in function SET_FILTER
declare
l_hObject NUMBER;
l_Objddl CLOB;
[code]...
View 7 Replies
View Related
Nov 20, 2012
We are migrating one of our databases from linux to AIX (testing phase). We're using transportable tablespace for the same. All steps are done and we're stuck during importing the metadata. While importing metadata we require to mention fully qualified datafile name which has to be plugged. The number of datafiles is 309 and the name of the all the datafiles has to be in one line. Now if we try to write it in one line using vi editor, the editor gives error 'ex: 0602-140'.
Is there a way by which we can overcome this situation?
View 5 Replies
View Related
Jul 19, 2011
i am using a schema which i need to take a backup of meta data only i am using exp utility
exp shan/shan@shan file=/backup_dump/shan.dmp log=/backup_dump/shan.log owner=shan rows=n
but it will return me below error, i have only access to user shan our client cant allow me to use system or sysdba schema or any other required grants or privileges so is there any way to take metadata backup of user shan from user shan.
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
View 3 Replies
View Related
Mar 2, 2010
A function returns the comments of all objects of the schema using metadata api. I used DATABASE_EXPORT as object_type for open function. which name (eg: base_object_name, base_object_schema...) should i use to get comments of single object. My code is..
1 CREATE OR REPLACE function f_depen_obj
2 return clob
3 as
4 a number;
5 b number;
[code].....
I got comments of all objects when i commented 11th,12th lines. Now i want the comments of EMP table of SCOTT schema. I got the following error ..
SQL> select f_depen_obj from dual;
ERROR:
ORA-31603: object "EMP" of type TABLE not found in schema "EMP"
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 3806
ORA-06512: at "SYS.DBMS_METADATA", line 3784
ORA-06512: at "SCOTT.F_DEPEN_OBJ", line 17
no rows selected...i got output (comments of single object) when i use COMMENT instead of DATABASE_EXPORT in open function(line no: 9). But i need to use DATABASE_EXPORT as a object_type. Which name is suitable for set_filter procedure instead of 'NAME'?
View 3 Replies
View Related
Aug 17, 2012
I'm using Oracle 11g enhanced ADD COLUMN Functionality. Adding new columns with DEFAULT values and NOT NULL constraint no longer requires the default value to be stored in all existing records.
Sometimes we change DB columns from NOT NULL with DEFAULT to NULL with DEFAULT. This operation "materialize" column default. Is there an easy way (Dictionary view) how to find, that COLUMN default value is stored as metadata or is "materialized" ?
Oracle RDBMS version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
View 4 Replies
View Related
Oct 19, 2012
how is to edit user_sdo_geom_metadata view?
View 6 Replies
View Related
Jun 9, 2010
I have a problem with DBMS_DATAPUMP.metadata_filter.Let's suppose that I need to export a huge list of tables (a,b,c,d,e,f,g,h,i...). Let's suppose that the list is dynamic do NOT want to use
DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (''a'', ''b'', ...)',
object_type => NULL);
In my_export_table there is the list:
CREATE TABLE my_export_table
(
EXPORT_OBJECT_NAME VARCHAR2(50 BYTE)
)
Now I'm trying to use this form:
DBMS_DATAPUMP.metadata_filter (handle => h1,
NAME => 'NAME_EXPR',
VALUE => 'IN (SELECT a.export_object_name FROM my_export_table a, user_objects b WHERE a.export_object_name = b.object_name AND b.object_type = ''TABLE'')',
object_type => NULL
);
but it results in error.
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB []
ORA-00942: table or view does not exist
[code]...
View 1 Replies
View Related
Jul 31, 2013
>select level ,empno,ename,mgr
from emp
connect by prior empno=mgr
start with mgr is null;
Output:-
>
LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
4 7876 ADAMS 7788
3 7902 FORD 7566
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
2 7782 CLARK 7839
3 7934 MILLER 7782
Note:- I got only this that this query is alternative of self join and it is giving manager name along with mgr id for each employee but when it gives output i couldn't able to understand how to identify who is manager of whom. Tell and explain with clause in oracle , i only know it is alternative of inline view but i want to know how does it work and how to use 'WITH' in oracle query if possible.
View 4 Replies
View Related
Sep 29, 2011
first of all sample data;
create table test_circular_data(c1 varchar2(10),c2 varchar2(10));
insert into test_circular_data values ('c1','l2');
insert into test_circular_data values ('c1','l3');
insert into test_circular_data values ('c3','l3');
insert into test_circular_data values ('c4','l3');
commit;
There is a circular relation between columns c1 and c2, so what I'm trying to retrieve is something like that :
c1--> l2 --> l3 --> c3 --> c4
The steps to get that result is :
1.- c1 related to l2 : c1-->l2
2.- c1 related to l3 : c1-->l2-->l3
3.- l3 in the list and related to c3 : c1-->l2-->l3-->c3
4.- l3 in the list and related to c4 : c1-->l2-->l3-->c3-->c4
View -1 Replies
View Related
Dec 24, 2012
Hierarchical Tree in oracle form 6i .i am trying to build tree in form 6i but it give error
SELECT 1,
level,
job
,
'',
ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
order by level
i want to show job wise employees ...
View 4 Replies
View Related
Nov 13, 2011
I have a table which contains the connectivity details. It is telecom data. The table contains the relation between the point features(Ex: Terminal) and linear features(Ex: Cable)
Below is some sample data
drop table connectivity ;
create table connectivity
(
ftr_no number(5),
ftr_id number(5),
[Code]...
-- 2nd linear ftr
insert into connectivity values (30,10359,33, 9655, 2,NULL,NULL);
insert into connectivity values (30,10359,33, 9656, 1,NULL,NULL);
insert into connectivity values (30,10359,30, 10359,3,NULL,NULL);
[Code]...
In the connectivity table PARENT_FTR_NO and PARENT_FTR_ID needs to be updated based on these conditions.
1. Correspponding to each feature there are 3 entries in this table.
2. Each feature(Point orLinear) has 2 sides INcoming and OUTgoing
3. The possible values for the RELATION column are 1, 2 and 3 where
-> 1 indicates feature conncted at the OUTgoing end
->2 indicates features connected at the INcoming end
->3 indicates self connected
4. Always a linear feature is the parent of a point feature which are connected to the OUTgoing end of the linear feature (i.e where RELATION = 1)
There is another table which contains FEATURE_NO and FEATURE_TYPE.
create table ftr_catgry
(
ftr_no number(5),
ftr_type char(1)
);
[Code]...
FTR_NO F
---------- -
33 P
31 L
30 L
Where
'L' represents linear feature
'P' represents point feature
The relation should be updated as
Step 1. Start with a linear feature, for example FTR_NO = 31
SQL> select * from connectivity where ftr_no = 31 and ftr_id = 10354 and relation = 1;
FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9651 1
The feature connected to the OUTend of the 10354 i 9651 with FTR_NO = 33 and it is a point feature so the parent for 9651 is 10354
Step 2.
SQL> select * from connectivity where ftr_no = 33 and ftr_id = 9651 and relation = 1;
FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
33 9651 33 9652 1
9652 is connected to 9651 at the OUTend, since 9651 is point feature so the parent of 9651 which is a linear feature is also the parent for 9652
Likewise the all relations have to be updated
Expected result after update
SQL> select * from connectivity;
FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9650 2
31 10354 33 9651 13110354
31 10354 33 10354 3
33 9651 31 10354 2
33 9651 33 9652 13110354
33 9651 33 9651 3
33 9652 33 9651 2
[Code]...
24 rows selected.
View 2 Replies
View Related
