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