XML DB :: DeleteXml() Causing Data Format Error In Database
Jul 21, 2013
I'm using the oracle xml db 11g in my website application. Last week I found that using the deletexml() to delete a node in the xml may cause a terrible problem that the rest data in the database had wrong xml format. I wonder is it a bug when using binary xml storage. My xml data is describe below,
I using deletexml() to delete just a node once time, "update projects set object_value=deletexml(object_value, '/root/nodes/node[@id="1"]')" Usually it successes and the rest data is as expected, but sometime, I find that after deleting a node, any query in the database except "select * from projects" gets null. And the rest data has wrong format like below,
My layout is working well enough triggers though are causing me some problems. I am getting the following error message just about every time I write a trigger so I am getting the feeling I am doing something fundamentally wrong. Like there is something I am just not getting because I am getting this error on the simplest of actions.
The error: FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502. -------------------------------------------------------------- ORA-01403: no data found
The trigger is this: cs3_prog.set_date_mode;
this calls the following procedure in the package cs3_prog:
procedure set_date_mode IS BEGIN :cs3_data.street := 'something'; end;
this in the past did a lot more but I kept throwing stuff out to see what could have been causing the problem. This does actually work if I ok through the first error message and then try entering data again into the field the trigger is tied to. On this second firing of the trigger is does successfully alter the value.
Just the fact that this is happening every time I create a trigger and it seems to happen regardless of what I have the trigger doing leads me to believe I have something fundamentally wrong and I would love to have it set straight.
We are trying to import data into existing tables in a schema using data pump
However the foreign key tables are being imported first and then the master table data thus violating the constraints
Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)
Is it a normal behaviour during data pump import?
Is it possible that the keys being sequence generated are causing this?
As I understand import will commit after each table In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?
i have implemented dataguard successfully, but later on standby machine will change and i use another machine for standby and implement dataguard from beginning but it will generate the below mention error after executing duplicate target database for standby dorecover command.
contents of Memory Script: { switch clone datafile all; }
I am using, Release 11.2.0.3.0 of oracle. In our database we observe, high 'Application' wait followed by 'other' and 'User I/O'. After investigating through the wait class 'Other', i found that 90% of the wait is due to wait event 'Enq: WF Contention ' that to excatly 5 PM to 5.30 PM daily. Then i found from the dba_hist_active_sess_history, that the sessions experiencing this waits are oracle internal(M004),
Now i can see two of the oracle jobs has been scheduled at this particular time.
And these were having frequency daily.So, i am suspecting these were experiencing the waits. Now my question is, can i decrease the frequency(may be weekly) of these jobs and it will not put any negative impact on my DB? Or should change the schedule time to some less peak hour or whether these are 'not required' and can be disabled?
I'm getting the error below when downloading IR report in csv format. I'm getting that whenever the records is more than 108,000. Is there a limit in records to download? Is there a resolution.
I am using oracle 10.2.0.3 and i am receiving very slow response time for the below query and sometimes resulting in a deadlock throwing ora-60 error.
DELETE FROM GBC_CORE.SPI_ELEMENT_ID TRGT WHERE (TRGT.URI) NOT IN ( SELECT DISTINCT FROMTOURI.URI FROM ( SELECT SERVICEACCESSNAME AS URI, SUBSTR( SERVICEACCESSNAME,1,INSTR( SERVICEACCESSNAME ,'_')-1) AS FROM_URI, SUBSTR( SERVICEACCESSNAME,INSTR( SERVICEACCESSNAME ,'_')+1,LENGTH(SERVICEACCESSNAME)) AS TO_URI FROM TRPT.V_TRPT_SPI_VIEW@DBLNK_FCE_TRPT ) FROMTOURI, [code]...
i'm executing a load test using the below statement:
INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501; COMMIT; EXIT;
I've been using the same for a few days without any error while now wehn i try to use the same i get below error:
SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500; insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500 * ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected.
I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.
I'm working with sqlldr and i try to insert data from a csv file to a CTL file. One field of my table contains 5 characters but one row has 6 characters in this field, so it's rejected by oracle. (Logical, you can't insert 6 chars in a 5 chars field)
an error is visibly returned, so i wondered how you could catch the value of this error?is it a code? a message?
I'd like to add to my script a condition so that the end of the script would continue even if this error code is returned for that CTL execution.
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..
SELECT * FROM EVENT_SUB
OR INSERT INTO EVENT_SUB... OR ALTER TABLE EVENT_SUB WHERE....
So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.
Sometimes i use this: select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID' Then, I would simply recompile the invalid objects.
For indexes, i do...
alter index <name> rebuild;
BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.
Oracle 11.2; application express 4.1...I have a table with a unicode clob column into which I insert descriptive text. Within these text entries I have inserted CR/LF (00D00A). However when I display the text entries in a report, the text streams instead of appearing with separate lines.
My report statement: select text_entry_dt, entry_authy_nm, entry_text from obj_text where obj_id = :P41_FIND_ID and text_type_cd = 'DES'
hat to do to obtain line separation within the entry_text content?
I have a test server that keeps running into ORA-00020: maximum number of processes error each night. Our current setting is '600' processes to be allowed. There are no users to this database except myself. No front end app connects to it either.
I found that ORACLE.EXE (SHAD) process coming from SYSMAN user is the one eating up all the processes and eventually causing that error.
From v$session, the program is OMS.
From v$process, the hostname is the server itself.
I narrowed it down to restarting the ConsoleService, if i do that, then the number of processes drops down. So now im not sure why something within the ConsoleService is logging in, taking a process and not releasing it...What i can check?
We are facing problem as temporary tablespace getting full continuously. During below running query, temporary tablespace getting full continuously and now it is not managable so we had stop the processing but we need to resolve this issue as business impact is there.
MERGE INTO HDFCMPR.MPR_TB_MPRMASTER USING (SELECT /*+ USE_HASH(A,B) FULL(A) FULL(B) */ MER_TRACKID, TRANID, DECODE (UCAF, 'n', NULL, UCAF) UCAF, A.ID [code]....
I have a query on displaying data as per my requirement. I have created a table called sales it has four columns
create table sales(country,state,district,sales); and am inserting some same data
insert into sales('india','TN','Chennai',100); insert into sales('india','TN','KPURAM',120); insert into sales('india','TN','Bangalore',35); insert into sales('india','ANDR','Guinder',100); insert into sales('india','ANDR','Nellai',76); insert into sales('london','city-a','xstreet',89); insert into sales('london','city-a','binroad',100);
select * from sales;
country state district sales india TN chennai 100 india TN KPURAM 120 india TN Bangalore 35 india ANDR Guinder 100 india ANDR Nellai 76 london city-a xstreet 89 london city-a binroad 100
the data is displayed in this format. How i am trying to display data.
I have one table storing all the information about employye, i have to develop three different reports based on the same table with different groupings , is there a easy way to call each format differently using single interface ,something like ref cursor.I dont want to create two or 3 different reports , instead choose the format.
I want to print data from a clob in XML format and use the following
PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is xmlstr varchar2(32767); line varchar2(32767); cnt NUMBER;
[Code]...
However the length of the clob is 13832630 which is too large for a VARCHAR2. Thus my output of line is cut off at 4000 characters. How can i increase this... Do i overlook something here in my code?
I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.
CREATE TABLE DATA1 ( ID NUMBER(6), DAT_ID NUMBER(6), RNK NUMBER(2) ); Insert into DATA1 (ID, DAT_ID, RNK)
[code]....
now after having this data from "data1" table , we need to get the row from "data2" table.in "data2" table there are total 6 combination on basis of "POS,ORDER" [ there are only 2 "ORDER" i.e. 'F' and 'S' , where as POS value can be changed, BUT THE "POS,ORDER" COMBINATION WILL HAVE ONLY 6 UNIQUE COMBINATION. ] so, for "POS and DAT_ID" combination we need to get the lowest rank data first, if that is not present then get the other rank given in "DATA1" table and so on and if no rank is present then select the NULL row row data from "DATA2" table for ex: in DATA1 table for count(*)>1 and id=1, we have data as
-------------- IDDAT_IDRNK
11231 11242 11253 --------------
so, in "DATA2" table, first we will see for "POS and ORDR" combination which DAT_ID is present, i.e. in case od POS=11 and ordr=F, we will select
111231FD1
as it is having lowest rank in "DATA1" table and it is present in "DATA2" table,
for POS=12 and ordr=F, we will select
121242FD1
as we don't have "123 and 1" in "DATA2" table so we will select the next rank given in "DATA1" ( i.e. 124 and 2 ), similarly , for POs=31 and ordr=S, we will select, as this is the next available rank and DAT_ID present in "DATA2" table
1D131S1253
and if there is no rank present from "DATA1" table in "DATA2" table then we will select the NULL row, i.e. for POS=21 and ordr=F, we need to select :
1D121F
"there will be 6 row for each id"
the output we want is : ----------------------------------------- IDNAMEPOSORDER DAT_IDRNK 1D111F1231 1D112F1242 1D121F 1D12321S1231 1D2322S1242
generating an xml file using the below data.Table name is T_Data having 4 columns as given below with some data.
Region Divison District Store ---------- ----------------------- ----------- ----------- Northwest Northern California San Jose SJStore1 Northwest Northern California San Jose SJStore2 Northwest Northern California North LA LAStore1 Northwest Northern California North LA LAStore2 Northwest Northern California North LA LAStore3
I want to generate an XML File using SQL/XML Functions and the XML File should look like as given below.
