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.
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
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
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
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.
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)
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
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')
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
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.
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:
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]........
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.
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:
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?
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.
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.
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?
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
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
