SQL & PL/SQL :: Oracle 10.2.0.4 On Solaris 10 - Recursive Query Using Hierarchy
Nov 9, 2011
We are on oracle 10.2.0.4 on solaris 10. My question is on a sql query. Is it possible to rewrite a query to avoid the connect by and prior constructs and use joins? For example i have the following query:
SELECT empno,
ename,
job,
mgr,
hiredate
FROM emp
START WITH empno in (7566,7698)
CONNECT BY PRIOR empno = mgr
How can it be rewritten using a two table join (self join)? I am not sure if it can be done and whether it is possible.
View 4 Replies
ADVERTISEMENT
Mar 5, 2013
To use dynatree (URL] I want the result to be in the xml form.but the result is not what I want.
SELECT
XMLELEMENT("div",xmlattributes('tree' AS "id"),
(SELECT DBMS_XMLGEN.getXMLType(
DBMS_XMLGEN.newContextFromHierarchy('
SELECT LEVEL,
case
when CONNECT_BY_ISLEAF = 0 then
[code]....
View 6 Replies
View Related
Sep 8, 2012
I need to do a PL/SQL program that prints the same that this WITH clause query, without using WITH or CONNECT BY, i was thinking about a solution with cursors?
WITH recursiveBOM
(assembly_id, assembly_name, parent_assembly) AS
(SELECT parent.assembly_id,
parent.assembly_name,
parent.parent_assembly
FROM bill_of_materials parent
[code].....
View 8 Replies
View Related
May 5, 2013
I have the following table structure...............
Main_Head table name
main_head_id ,pk
head_desc,
head_id ,
sub_head_id
keys
col table ref col
sub_head_id main_head head_id
the table is recursive table self join
-----------------------------------------
now i want to write the query which return all head_desc which have same head_id
View 12 Replies
View Related
Aug 5, 2010
I have this SQL select to give me all the nodes of a tree starting at a particular node:
SELECT tree.node_id, LEVEL depth_level
FROM tree_of_nodes nodes
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id
I need to update a column called dept_level, which is essentially the same as Oracle's LEVEL. Something like this statement is what I need, but obviously this statement doesn't work:
update tree_of_nodes
set depth_level = LEVEL
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id
I've tried inline views and other approaches and have not found a method that works.
View 3 Replies
View Related
Jun 10, 2013
Is it possible to sort records based on hierarchy in such a way that records sorts in sequence and records of same level comes in the last (in sorting order)
Eg: Default hierarchy level order: 0,1,2,2,3,3,3,4,5
Above given is the default sorting order of an query for an parent 'NODE_A1' having multiple same level i.e 2 and 3
Expected Hierarchy level order: 0,1,2,3,4,5,2,3,3
Please check attached file for an example.
View 8 Replies
View Related
Jun 4, 2013
How to get only the last level in Oracle SQL Hierarchy Query?
View 2 Replies
View Related
Dec 6, 2011
I have a table:
create table FIELDS
(
FIELD_NAME VARCHAR2(30) not null,
PRG_FIELD NUMBER not null,
LENGTH NUMBER
);
with
INSERT INTO FIELDS VALUES('FIELD1', 1, 3);
INSERT INTO FIELDS VALUES('FIELD2', 2, 3);
INSERT INTO FIELDS VALUES('FIELD3', 3, 4);
INSERT INTO FIELDS VALUES('FIELD4', 4, 2);
INSERT INTO FIELDS VALUES('FIELD5', 5, 1);
I need to insert in a table:
create table STUFF
(
FIELD_NAME VARCHAR2(30) not null,
FSTART NUMBER not null,
LENGTH NUMBER
);
And the output I want is:
INSERT INTO STUFF VALUES('FIELD1',0,3);
INSERT INTO STUFF VALUES('FIELD2',3,3);
INSERT INTO STUFF VALUES('FIELD3',6,4);
INSERT INTO STUFF VALUES('FIELD4',10,2);
INSERT INTO STUFF VALUES('FIELD5',12,1);
So each field starts where the previous (ordered by PRG_FIELD asc) ends.
I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.
PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out
View 1 Replies
View Related
Jun 26, 2012
How to create one complex query. here what I have in my table.
DELIVERABLESPRECURSORS
------------ -----------
4 15
15 332
15 26
26 332
29 4
29 15
224 26
224 274
224 259
259 29
274 15
288 259
288 26
288 274
916 27
917 27
918 27
I need a query which run against this table and bring me the following result.
4 > 15 > 332
15 > 332
15 > 26 > 332
26 > 332
29 > 4 > 15 > 332
..... and so on
In theory, I need query which will track DELIVERABLES'S PRECURSOR and PRECURSOR's of there PRECURSOR'S.
View 16 Replies
View Related
Jul 17, 2012
I am working on the quality center oracle database to write a query that fetches all steps of a test case including the ones having calls to tests. Structure of table is explained below. I've made up an example and attached it as an image to this post. This image also has the expected result of the query I want to write.
Table Name: STEPS (This table contains steps belonging to tests. Some steps are simply calls/links to other tests)
Columns:
STEP_ID (primary key - integer)
STEP_NAME (char)
STEP_DESC (char)
ORDER (integer)
TEST_ID (reference to table TEST.test_id)
[code].......
Referring to the example (see attached image), I'm looking to write a query that gives me all steps (including steps from called tests) of the test - "Empty trash from mailbox" in the correct order.
To start with I can write the following query to get steps of the test - "Empty trash from mailbox".
SELECT * FROM steps WHERE test_id = (SELECT test_id FROM test WHERE test_name = 'Empty trash from mailbox')
View 7 Replies
View Related
Apr 29, 2013
I am using Oracle database 10g R2.
suppose I have this structure
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
3 FRANK 2
What I want is when I query for any EMP_ID, I want to get all his manager first, and the last record is the employee in leaf of the hierarchy in the query.
For example, when I query for EMP_ID=2 ,I want to get this result
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
and when I query for EMP_ID=3 ,I want to get this result:
EMP_ID EMP_NAME MANAGER_ID
1 KING
2 STEVE 1
3 FRANK 2
How to write this query?
View 3 Replies
View Related
Jul 30, 2013
I've below tables.
create table tst_link (parent_pid number(5),
child_pid number(5)
);
insert into tst_link (1, 2);
[Code]....
For each parent pid i need to calculate total miles (including the child_pid also).
Required Output
PARENT_PID TOTAL_MILES
----------- ------------
1 48
4 100
8 125
View 4 Replies
View Related
Jul 14, 2012
How can we overcome recursive triggers in oracle SQL?
View 7 Replies
View Related
Apr 20, 2013
What is the hierarchy to stop and start oracle RAC instance ?
View 10 Replies
View Related
Jan 17, 2013
I want to teach myself Oracle DB 11g so I've created an Oracle Solaris 10 VM (under Oracle Virtual Box, see a theme here?) and when I went to look for the 11g installer I could only find the x86_64 packages but not the x86 packages.
View 1 Replies
View Related
Nov 27, 2011
What kind of storage technology is recommended in order to implement an Oracle 11gr2 database in a Solaris 11 OS?
-Oracle ASM or the Solaris 11 ZFS filesystem.
Its any difference between the performance of both storage technologies?
View 1 Replies
View Related
Nov 8, 2012
I'm trying to install the Oracle Database 11g release 11.2.0.3 on to Solaris 11.1. I've place the command below and the results of the command. See below:
./runInstaller -executePrereqs
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 180 MB. Actual 2971 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3554 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-11-08_04-50-43PM. Please wait ...louis@Solaris_T510:~/Downloads/Oracle_Database-11g_11.2.0.3/database$ Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct
Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-2" to type FontStruct
[code]........
It appears to me that I'm missing a Font
View 13 Replies
View Related
Aug 7, 2012
Share the document for 9i installation on solaris9. i got some document for 9i on solaris8 and 10 . if this is same for 9 ?
View 3 Replies
View Related
Sep 16, 2013
We've been battling with very slow performance for some time. Herewith a detail description of the problem:
Solaris-11/ZFS/Oracle problem
We purchased Oracle T4-2 servers, and are experiencing some weird performance problems.
Hardware:
T4-2
2 x 600GB HDD per server
128GB memory per server
2 x dual port QLE2562 FO HBAs
IBM V7000 StorWyse data array
2 x CISCO MDS 9148 fibre optic fabric switches
Software:
Solaris 11.1
MPXIO
Solaris 10 branded local zones
ORACLE 10g Enterprise edition
Project for the oracle user:
user.oracle:100::oracle::process.max-file-descriptor=(basic,8192,deny);process.max-stack-size=(priv,32768,deny);project.max-shm-memory=(priv,21474836480,deny)
We received the first server and wanted to migrate our APPB application system and Oracle 10g Standard Edition database from our SUN T5240 to the T4-2.
T4-2 setup � Disk 0:
Global zone: Solaris 11.1 ( ZFS - whole disk for the root pool)
Local zones: On the Solaris 11 environment we built two branded Solaris 10 zones using an Oracle template provided on the Oracle website - solaris-10u11-sparc.bin.
Our complete database resides on the IBM data array, in UFS LUNs.The UFS LUNs were mounted onto ZFS mount-points in the root partition (/), and then LOFS mounted into the zones.
We started with the Solaris 11.1 environment.
1)After a day or two the performance of the database starts deteriorating rapidly. We then stop the database and reboot the machine. After the reboot the performance level is restored.
2)Another huge deterioration in performance happens when we unmount the V7000 LUNs, and reboot to the alternate Solaris, and re-mount the LUNs.
3)What further compounds the issue is that when we start another the database in the second zone, we see another huge performance degradation.
4)We have logged a call with ORACLE. They requested us to gather information which was analysed by them. They did not find anything wrong with the way ORACLE was installed and the setup of the instances.
On Disk 1 we did a Solaris 10 8/11 (Update 10) installation, which we patched with the April 2013 CPU patchset. In this Solaris 10 global zone we built two native Solaris 10 local zones. The Oracle 10g databases were built in the zones (same configurations settings) not in the global zone, onto UFS LUNs. The database in its entirety lives on the IBM V7000 data array. This works fine.
We then received our next T4-2 server.Loaded it again with Solaris 11.1, and upgraded to ORACLE 12c SE Release 12.1.0.1.0 - 64bit12xxx seeing that Oracle 10 is not certified on Solaris 11. To keep things simple, we built two small databases in the ZFS root pool. The complete system now resides on one disk no UFS LUNs to consider, no Fibre Optic fabric, no CISCO switches, no IBM data array, BUT we get the same problem. The system will run for some time and then slow down drastically. Starting the second database slows the system down abnormally.
View 1 Replies
View Related
Aug 28, 2012
I am running an Oracle 10.2.0.3 on Solaris 5.9 OS. Front end appplication is PeopleSoft v8.8.From my AWR report I have found below SQL which needs to be tuned:
SELECT TO_CHAR (TO_DATE (TO_CHAR (B.ASOFDATE, 'YYYY-MM-DD'), 'yyyy-mm-dd'),
'dd/mm/yyyy'),
B.EMPLID,
B.PWCUK_LEGACY_ID,
B.NAME,
[code]...
View 6 Replies
View Related
Jan 4, 2013
We have to migrate our database from current OS (AIX 5.3) to Solaris 10. We'll do it through expdp / impdp. Is there any other way to do it as well?
Also, we have dataguard configured at current setup. So is there any way we can take backup of existing database at DR & restore it at new server (Solaris) or we'll have to take a fresh backup from DC (after migration) & ship it to DR & create a new standby?
View 8 Replies
View Related
Oct 25, 2013
What are reported issues when migratin Oracle 11g from Solairs to Linux?
View 1 Replies
View Related
Feb 18, 2013
While adding new oracle 11g R2 rac node on solaris 10, getting below error.
Checking user equivalence...
PRVF-4007 : User equivalence check failed for user "grid"
Check failed on nodes:
sr3
[code]...
View 4 Replies
View Related
Dec 2, 2010
From some day I have this error during export data pump:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31687: error creating worker process with worker id 1
ORA-31687: error creating worker process with worker id 1
ORA-31688: Worker process failed during startup.
This error is random, if I retry after few minutes the expdp work correctly.
View 8 Replies
View Related
Apr 20, 2013
Oracle Solaris 11
Oracle Database 11.2.0.3
Create a new database using dbca and at the end dbca reports it could not start database control.
The dbca warning window asks to set ORACLE_UNQNAME and to start database control again.
su - oracle
$ . oraenv
rcat
export ORACLE_UNQNAME=rcat
emctl stop dbconsole
emctl start dbconsole
failed...
I tried that and dbconsole still cannot start.
View 3 Replies
View Related
Sep 5, 2005
How to
1) to define a job in oracle9i for solaris
2) to schedule this job
Remember i have prior experience deploying jobs at windows platform but when i try it on (9i for ) solaris, the script does not work as it does not accept a submit job request.
View 4 Replies
View Related
Jul 25, 2011
I have oracle 10g up and running on Solaris 10, from windows I would like to connect to sql plus through windows authentication, for that I have already made sure that remote_auth = true and have created user in oracle with OPS$. But still I cannot connect.
I have the same setup but with oracle on windows server, the os authentication from windows clients works just fine.
does oracle 10g on solaris 10 supports windows os authentication?
View 4 Replies
View Related
May 11, 2013
which is really a HA zone clustered in VCS(not important for installation of the database).
What parameters do I need to set or change in the Solaris zone, there seems to be no /etc/system file.
View 3 Replies
View Related
May 11, 2013
I am trying to export selective data from one of my prod database tables. But not succeeding. I was keep on trying for the past 2 hours.
OS : SOLARIS SPARC
ORACLE - 10G
Query --> WHERE E3RECV_DT LIKE '201305%' (I need to export this query data)
Below Script i am using
===============
exp E3USER@SGEBAPU2 statistics=none consistent=n buffer=100000000 file=exp_pipe_file TABLES=IFDATA query="WHERE E3RECV_DT LIKE '201305\%'" log=PGTB_IFDATA_conditional.log
View 1 Replies
View Related
May 3, 2013
We have an requirement to create xml data for entire database (selected tables) which are in hierarchy.Procedure should read node_mapping table having parent and child tables relationship info and build XML Select statement.
Currently it is building SQL statement whenthere are one parent having multiple childrens i.e Dept having emp, emp_act, emp_rsch..but when child node are having childrens then it is not working - it has to repeatedly call this procedure (recursive) and build below given SQL statement.
1. To change procedure to build xml sql statement when there are multiple childrens to child nodes (hierarchy)
2. To format the output in xml data
We are using ORACLE 11G and WINDOWS 7
CREATE TABLE node_mapping
(
NODE_ID NUMBER(5) PRIMARY KEY,
PARENT_NODE VARCHAR2(100),
CHILD_NODE VARCHAR2(100),
PARENT_NODEID VARCHAR2(50),
CHILD_NODEID VARCHAR2(50)
[code]....
View 1 Replies
View Related
