SQL & PL/SQL :: Procedure To Get Path From Parent-child Relationship?
Apr 23, 2010
I have a table which uses parent-child relationship to store the options available. I need a procedure to give me the full path given the id of a particular option.
I have two different functions. One of them uses the Oracle built in function and the other uses simple queries with a loop.The code of the functions are given below.
Now, the problem is with their "performance". The difference in their performance is significant. The function using the Oracle function takes more than 2 hours to run a query whereas the other function takes less than 2 minutes.
I am having trouble trusting the other function. No matter how many tests I perform on the output of both the functions, it always comes out to be the same.
Function 1
=====================
FUNCTION Gettree (opt_id IN NUMBER,i_app_id IN NUMBER)
RETURN VARCHAR2
IS
path VARCHAR2(32767);
application_no NUMBER;
BEGIN
Just wanted to pose a question Can a (Unique and Not nullable)Alternate key of the parent table be part of child Table in an Identifying relationship i.e part of primary key of a child table????
Is there a rule that a primary keys of a parent table can be used to build a identifying relationship with its child?
I have a DB that has one parent table with many child tables. I would like to delete all records(child - parent) based upon a particular "net_id". I have three versions that "seem" to work BUT I'd like to use "version2" because I want to go to the parent one time(for the parent delete I just made it simple for me) only and be able to delete the child record(s) from the child table(s). I want to achieve this w/o using triggers...
My question: Is version2 an "ok" way to delete child records or should I try another method(version1 or 3)??
I am working on a project, the insert statement is not functioning properly and I cannot find the error The error is in the insert statement for CustOrder. The error states that NULL cannot be inserted into CustOrder.CustID. I have declared CustID and its reference. I have looked over the code several times and I cannot find the problem.
If it makes a difference I am using APEX. The company has not database or program to work with a database so I am building everything from scratch.
declare l_CustOrderID number; l_CustID number := :P11_CustID; begin
I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.
Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well
Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.
I've the following data and i need the parent and leaf most child,including their id's
Create table par_chld (id,p_id,c_id) as (select 1,900,501 from dual union all select 2,900,502 from dual union all select 3,900,503 from dual union all select 4,100,900 from dual union all select 5,200,900 from dual union all select 6,300,400 from dual union all select 7,101,500 from dual union all select 8,102,500 from dual union all select 9,103,500 from dual union all select 10,201,600 from dual union all select 11,201,601 from dual union all select 12,201,602 from dual )
In the above data p_id =100 has c_id 900 , and this 900 acts as a parent which has child 501 , 502 ,503
And the output should be in the following format , Where i don't need the middle level data. I need parent and its leaf child along with the id's of parent and child
I tried the following query where i got the p_id,id_c,c_id columns , but unable to get the column id_p
select connect_by_root p_id p_id ,id id_c,c_id from par_chld where connect_by_isleaf =1 start with p_id not in (select c_id from par_chld where c_id is not null) connect by nocycle p_id =prior c_id
Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.
If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888. The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.
i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.
I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.
SQL> create table t1(a number primary key, b number); SQL> create table t2(c number, d number references t1(a)); SQL> insert into t1 values(1,2); SQL> insert into t1 values(2,3); SQL> insert into t1 values(3,4); SQL> insert into t2 values(10,3); SQL> insert into t2 values(20,2); SQL> delete from t1 where a=2; delete from t1 where a=2 *
ERROR at line 1: ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child record found
I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.
One Text item is there in FormPARN, near to that Text item, Button is there.While clicking Button FormCHLD will be opened. Inside the FormCHLD, i will do some calculation and i need to store the result in FormPARN's Text Item.
I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.
for ex
E A-->B-->C-->D F-->G H
When i will pass d as node it will return c,b,a,e,f,g,h
I need to fech parent records only when no child record with status 'N' exists. There are only two possible values for status column of child table 'Y' / 'N'.
Below are table structures and insert statements for data.
CREATE TABLE MASTER ( COL1 NUMBER,
[Code]....
COMMIT;Query I framed is below
select * from master where exists (select null from child where child.col2 = master.col1 group by child.col2 having count(distinct col3) =1 )
Output in above case would be 3 as for 1 there's one record with status as 'N' and for 2 there's no child record. I am on 10g.
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.
Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.
how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.
My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE ============================= Smith NinthGrade PHYS ED 101 Smith TenthGrade CALCULUS 201 Smith TenthGrade ZOOLOGY 202
(The data has been simpliied and masked, but is true to the content and is queryable).
select * from V$VERSION
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production [code]....
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table Parent Child Report1Table A Table ATable M Table ATable N Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3 -Report1Table ATable M -Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20)); insert into hierarchy values ('Report1','Table A'); insert into hierarchy values ('Report1','Table B'); insert into hierarchy values ('Table A','Table M'); insert into hierarchy values ('Table B','Table N'); insert into hierarchy values ('Report2','Table P'); insert into hierarchy values ('Table M','Table X'); insert into hierarchy values ('Table N','Table Y'); insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function : select * from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path from hierarchy start with parent='Report1' connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
--Create a parent object CREATE OR REPLACE TYPE PARENTOBJ AS OBJECT ( FIRST_NAME VARCHAR2(50), static function GETNAME return varchar2, MEMBER PROCEDURE getoutput ) NOT FINAL; /
----Create the body for parent object type CREATE OR REPLACE TYPE BODY PARENTOBJ AS static function GETNAME return VARCHAR2 IS BEGIN RETURN 'PARENTOBJ'; END ; MEMBER PROCEDURE getoutput iS BEGIN DBMS_OUTPUT.PUT_LINE( 'Hello world from '||PARENTOBJ.getname ); END ; END; /
--Create a child object CREATE OR REPLACE TYPE CHILDOBJ UNDER PARENTOBJ ( static function GETNAME return VARCHAR2, --override the parent method OVERRIDING MEMBER PROCEDURE getoutput ) FINAL; /
----Create the body for child object type CREATE OR REPLACE TYPE BODY CHILDOBJ AS static function GETNAME return VARCHAR2 IS BEGIN RETURN 'childobj'; END ; --override the parent method OVERRIDING MEMBER PROCEDURE getoutput IS BEGIN DBMS_OUTPUT.PUT_LINE( 'Hello world from '||CHILDOBJ.getname );
END; END; /
The above types are created with no issues. However, when I am trying to inherit the child method and override its parent, I am getting the follwoing error:-
--NOW INHERIT THE PARENT INTO CHILD, AND OVERRIDE THE PARENT METHOD BY CALLING THE CHILD METHOD DECLARE OBJ PARENTOBJ; BEGIN OBJ := NEW CHILDOBJ(NULL); DBMS_OUTPUT.PUT_LINE('obj method getoutput() = ' || GETOUTPUT()); END; /
ORA-06550: line 6, column 56: PLS-00201: identifier 'GETOUTPUT' must be declared ORA-06550: line 6, column 3: PL/SQL: Statement ignored
I have countries, sites, states tables (total 3) in database (i have user id and password to connect to this database).
every week i need to extract data from these tables into excel files and i need to save those in shared drive for team use.
Currently i am connecting to database every time running sql query and manually exporting that latest data to excel and saving that as excel files in (G: eamcommon) folder with specific name.
output format should be :
excel (.xls) file names should - countries.xls,sites.xls,states.xls server name : ap21 output location : G: eamcommon ( G is shared drive).
i heard that we could create batch file to do this task and also we could use oracle procedure to do this task. but not sure which one is the best option.
I have one RDBMS concept question.if i have 2 tables with 0 to many realtionship. can i create referential integrity constraint between them?
In other words how can i tell whether the records in table A(child table) are not orphan records and have 0 to many relationship with table B(parent table) ? Right not there is no constraint defined on both tables.
I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.
I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b
where a.Col1 = 11060 and a.Col1=b.Col1 and a.Col2 <> b.Col2 order by Col1, Col2 [code]....
HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.
I have a table which has 2 one to many relationships like a tree structure in the same table, is there a way that I can show the same on the form and be able to do the insert and update on the same table?
