Dynamically Build (recursive) Xml SQL Statement

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


ADVERTISEMENT

SQL & PL/SQL :: Dynamically Build SELECT Statement

May 8, 2013

We have an requirement to build (XML) Select statement dynamically based on the mapping table (which is in hierarchy format)

I have removed xml keyword, so query will not work. but need procedure which will recursively check mapping table (parent and child table) and build below given select statement.

Example 1:
SELECT dname,
(select ename,

[Code]....

View 8 Replies View Related

Execute Immediate To Dynamically Build A Trigger?

Apr 25, 2011

We have developed a code which builds a trigger on a atble dynamically at run time usinf Dynamic SQL command Execute immediate.

create or replace
PROCEDURE GENERATE_TRIGGER
(
P_TNAME IN VARCHAR2

[Code].....

I am not usre if the error is due to insufficient privileges as we are able to build tabl;e in the system dynamically using the above mentioned command.

View 1 Replies View Related

WITH Recursive Query Without Using WITH?

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

SQL & PL/SQL :: Recursive Table Query?

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

SQL & PL/SQL :: Update With Recursive Query

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

SQL & PL/SQL :: Overcome Recursive Triggers In Oracle?

Jul 14, 2012

How can we overcome recursive triggers in oracle SQL?

View 7 Replies View Related

PL/SQL :: Using Recursive Subquery Factoring In A Function?

Sep 24, 2012

I need to use Recursive Subquery Factoring for a project of mine, specifically I need to use some code I have found in a function.

[URL]

The code I have so far is this:

create or replace
function recursive
(
pattern in raw
, solution in raw
) return number as

[code]....

But when trying to compile it in Oracle SQL Developer, I get the following two errors:

Error(9,1): PL/SQL: SQL Statement ignored
Error(10,30): PL/SQL: ORA-00907: missing right parenthesis

The errors refer to these two lines:

with x( s, ind ) as
( select sud, instr( sud, '' '' )

I have looked over and over the code again, and I do not see any missing parenthesis. Furthermore I tried using with in a smaller test function:

create or replace function test
(
p in number
)
return number as
v number;
begin
with t(a) as

[code]....

Which compiles just fine, so I'm not sure why that line is being ignored in this function. The whole idea here is to replace the sudoku string in the function with a variable that I built from the two parameters.

View 5 Replies View Related

PL/SQL :: Sql Query To Build Average Value

Jul 18, 2013

I'm trying to build a query capture the below information. Table looks something like below . 

RoomSubjectStudentsA1Science10A1Maths20B1Science15B2English25C1Deutsch20A1French15C1English10 

How can i write a query so that i takes the average of students in each room and display the output? I need to write a query so that it also picks up any new rooms and students added.  Average is sum of total students in room by number of times each room is specified.  

eg: A1 is listed 3 times a the result would be 10+20+15 divided by 3.    

A1    B1    B2    C115    15    20    15 

View 7 Replies View Related

SQL & PL/SQL :: ORA-00604 - Error Occurred At Recursive Level

Mar 14, 2011

I am using Oracle 10g Database as back end and Developer 6i as front end.I have a procedure which is called upon SAVE. Means before COMMIT;This procedure holds few updates and delete statments.

This prcedure is throughing sql error as follows: ORA-00604: error occurred at recursive SQL level

The error occur when an delete statment is issued which as follows: DELETE FROM DUMMY_TAB001;

I issue the same statement from SQL it is running fine. But when my from runs on this delete statment encounter it through the above oracle error ORA-00604.

View 7 Replies View Related

JDeveloper, Java & XML :: Recursive Hierarchy Query To XML?

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

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 View Related

SQL & PL/SQL :: Copy Into Same Table / Recursive Rename Of Duplicates

May 11, 2011

In MS Windows, if I copy a file and paste it into the same folder, I get a copy with the text 'Copy of' in front of the file name. If I paste it again, I get another copy with a different version number.

E.g.
sqlnet.log
Copy of sqlnet.log
Copy (1) of sqlnet.log
Copy (2) of sqlnet.log

I was wondering if I could copy existing rows into a table and do the same thing?

So, for example if I had this table:

create table tst_srch (srch_is varchar(100), user_name varchar(100), srch_name varchar(100));

insert into tst_srch values (1,'USER1','SRCHA');
insert into tst_srch values (2,'USER1','SRCHB');
insert into tst_srch values (3,'USER1','SRCHC');
insert into tst_srch values (4,'USER1','SRCHD');
insert into tst_srch values (5,'USER2','SRCHC');
insert into tst_srch values (6,'USER2','SRCHD');
insert into tst_srch values (7,'USER2','SRCHD_1');

Could I write a procedure like copy_searches('USER1','USER2') that would copy all USER1's searches to USER2 - including renaming any duplicates.

So it would create these new rows:

8,USER2,SRCHA
9,USER2,SRCHB
10,USER2,SRCHC_1
11,USER2,SRCHD_2

I've looked at various insert statements, merge and match statements and exception handling in procedures .

View 6 Replies View Related

Installed Oracle11g - How To Build Demo

Jul 28, 2010

I installed oracle11g on my computer ,when im going to build demo its not working.its giving a error like this.

sys>conn u1/u1
error accesing PRODUCT_USER_PROFILE
warning product user profile information not loaded
you may need to run PUPBLD.SQL AS SYSTEM
connected
u1>@?/sqlplus/demo/demobld unable to open file "/oraeng/app/oracle/product/11.1.0/sqlplus/demo/demobld.sql"

View 1 Replies View Related

Build Database From Backup Files

Aug 3, 2011

I am an absolute neophyte at this. I'm fairly good at SQL, but have not administrated a database before.

I am trying to take a backup from our production database and restore it to a fresh install of Oracle 10.2 on a Windows 2003 server using RMAN - but I am not getting very far. Software versions are all the same. I have access to the manuals, but it seems like every link I follow adds more questions than it answers.

I have gone through the wizard and set up oracle on the new box - that all seemed to go smoothly. In order for this to work, do I need to set up the database with the same DBID as the one in the backup files? Or is there a way to import the data from the backup and have it build the database from those files?

I'm not sure I'm even asking the right questions. Do any of y'all know of a tutorial that would walk me through this process step by step?

View 4 Replies View Related

Build And Load Differences Between Dumps?

Jun 19, 2012

we get daily a full dump from our customer. We have to build the differences of tables (deltas) and load them in separate tables.

There some questions, requiremenst and restrictions:

1) Is it possible the merge the dumps ?

2) How to build the differences as the easiest ?

3) The whole process (import, merge, extract) should be run in batch.

4) We have only a solid knowledge and liitle time for doing.

View 1 Replies View Related

SQL & PL/SQL :: Oracle Select Build Table?

Jun 21, 2011

select a default table in oracle as we do in teradata, below is the syntax of teradata.

select 'XXX' as Curr,
'BUS OBJ ' as acq_nm
from (select 1 as one) Union
select 'ALL' as Curr, from (select 1 as one).

so I need the same select in oracle.

View 3 Replies View Related

SQL & PL/SQL :: To Build A Hierarchy And Output It Into A Sub-report

Feb 26, 2010

I want to build a hierarchy and output it into a sub-report. Since the data in few hierarchy fields exceeds 4000 characters and therefore m getting the ORA-01489 error and the entire report was failing.

So i have used user aggregate function to print the out put. I have also used row_number() to print sequence in query output.

the query that i am using is

SELECT pr_id,
REPLACE (REPLACE (twadmin.STRAGG2(distinct RPAD (to_number(curr), 2, ' ') || ')' || NVL (DECODE (medical, NULL, clinical, medical), 'UNKNOWN') || '###' ||
RPAD (' Causality as per rport ', 50, ' ') || ': ' || reporter || '###' ||
RPAD (' Causality as per report2 ', 50, ' ') || ': ' || hospital || '###' ||
RPAD (' Dechallenge', 50, ' ') || ': '|| dechallenge || '###' ||
RPAD (' Rechallenge', 50, ' ') || ': '|| rechallenge), ',', CHR(10)), '###', CHR (10)) causality
[code]......

the output of the query is

ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
2 )Mental status changes (Mental status changes)
[code].......

but the expected out put is

ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
[code]........

but it should be as

1)
2)
3)
..
..
..
10)
11)
12) for the values in the causality field

View 25 Replies View Related

SQL & PL/SQL :: Recursive Query - Each Field Starts Where Previous Ends

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

SQL & PL/SQL :: ORA-00036 Error (maximum Number Of Recursive Levels)

Aug 19, 2010

Ive just tried running a simple

update BASIC set 'column name' = NULL

Which works fine if i specify a where clause that returns a low amount of values, but im trying to run this update for the whole column (1000's or records).

Ive had no experience of this error before and am unsure of where to start, ive had a quick read around but see something of removing triggers?

The full error is :

ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "new.su_Table", line 61

View 22 Replies View Related

Security :: ORA-00604 / Error Occurred At Recursive SQL Level 1

Nov 3, 2010

I'm trying to drop a user but it gives me below error message-

SQL> drop user <username> cascade;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

However when i check from dba_queue_tables or user_queue_tables it doest show any queue tables.

View 6 Replies View Related

PL/SQL :: ORA-00604 - Error Occurred At Recursive SQL Level String

Jun 22, 2013

Oracle Version :- 11.2.0.2 I found a error in a trigger(Statement Level) ORA-00604:error occurred at recursive SQL level string. Before Finding this issue,Once the DB Response was slow . Will this be the issue Of DB Slow response. The Above trigger fires for each entry in an transaction table. The code is Patched and was executed . The above issue was found during another issue and not the DB Slow response.  My Doubt is Whether DB response slow issue would be because of this. Now after fixing this Slow response was not reported. 

View 0 Replies View Related

PL/SQL :: Trigger Error Maximum Number Of Recursive SQL Levels

Aug 29, 2012

I have getting error will insert into table i.e

i have a trigger as below

create or replace trigger INS_ERRORS
before insert on MIG_STG_ERRORS
for each ROW
declare
V VARCHAR2(22);
[code]........ 

when i insert into MIG_STG_ERRORS getting error message like 00036. 00000 - "maximum number of recursive SQL levels (%s) exceeded"

*Cause:    An attempt was made to go more than the specified number of recursive SQL levels.
*Action:   Remove the recursive SQL, possibly a recursive trigger.

View 4 Replies View Related

SQL & PL/SQL :: Function To Check Length And Build A String?

Dec 18, 2012

I have created a VIEW with a column containing user-defined aggregated values(stores the output in varchar2 data type). Some of the records exceed the maximum limit, for example varchar2(4000). I was suggested a PL/SQL function to check the length and build a string if necessary..

CREATE OR REPLACE
FUNCTION SUBURL(col1 IN VARCHAR2, col2 IN varchar2)
RETURN varchar2

[Code].....

Here, I got the following error,

Error(4,25): PLS-00103: Encountered the symbol "," when expecting one of the following:
:= . ( @ % ; not null range default character

View 14 Replies View Related

Application Express :: Build A Menu Framework

Jun 27, 2012

I have built a Menu Framework using the following instructions.

[URL]........

I am facing 2 problems.

1. If the user is in a child application and clicks logout. He is taken to the login screen of the master application (Correct), but once he enters login details, he is returned to the child application (False). I think he should be returned to the Home page of the Master application.

2. If I set Session Timeouts in the Edit Security Attributes of the applications. Then if the user is timed out, he is sent to the login screen of the Master application (Correct). If he then logs in, he is sent the the Home page of the Master application (Correct).

Now if he clicks a link to a child application he is then sent back to the Master application login screen. He then has to login again. He is sent to the Master Home page and everything is then ok.

View 1 Replies View Related

SQL & PL/SQL :: Query To Fetch Steps Of Test Having Recursive Calls To Other Tests

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

Server Administration :: ORA-00604 / Error Occurred At Recursive SQL Level 1

Oct 7, 2010

When trying deploy an mapping, the database link get this error:

ORA-06550: PACKAGE BODY, line 0, column 0:
ORA-04052: error occurred when looking up remote object db1.someC@db1@LOC_db
ORA-00604: error occurred at recursive SQL level 1
ORA-12170: TNS:Connect timeout occurred

I can use tnsping towards the db. I can use

elect * from v$instance@db1@loc_db

View 1 Replies View Related

PL/SQL :: Trigger - Error - ORA-00036 - Maximum Number Of Recursive SQL Levels?

Aug 10, 2013

From front end the user will enter VACANCY_ID in the label box and once he saves that transaction, in background VACANCY_CD filed(sequence) willautomatically generated

.below is my code:{code}create table tmp1(vac_id number,vac_cd number);create sequence tmp1_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;select tmp1_seq.nextval from dual; ---initiating seqselect tmp1_seq.currval from dual; --checking the current val--trigger creationCREATE OR REPLACE TRIGGER TRIGGER1 AFTER  INSERT ON TMP1 FOR EACH ROW   DECLARE seqval

[code]...

Error: ORA-00036: maximum number of recursive SQL levels (50) exceededORA-00036: maximum number of recursive SQL levels (50) exceeded 

View 6 Replies View Related

Performance Tuning :: Possible To Build Index Partition In Parallel

Jul 8, 2010

Is it possible to build index partition in parallel?I tried following command

alter index <index_name> rebuild partition <partition name> online parallel 5;

It executed without complaining, but want to know if index partitions can be build in parallel?

View 3 Replies View Related

Forms :: FRM-91500 - Unable To Start / Complete Build

Oct 9, 2009

I am trying to modify an existing form in oracle apps. I copied all the necessary objects and modified the form. I can compile the object in windows. When I am trying to compile the object in Linux server using SSH I am getting the following error.

FRM-91500: Unable to start/complete the build.I am using the following command for compile

f60genm userid=apps/apps batch=yes module= FORMNAME.fmb module_type=form.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved