I am using an Oracle view and package to extract data from my DB to build an XML file on the fly.
My problem is that on very rare occasions, an invalid XML character will be in the database. This, of course, causes my XML file to error.
My question is: What are the possible ways to remove invalid XML characters when selecting out of a DB field? Any function that has been written for this type of thing, or is a VERY long 'translate' more of what I should be doing?
I need to removed special characters (!, ", #, $, %, &, /, () from a string, i have a table with sll this special characters and words that i have to remove from the string.
How can i do that ?
i have a string with |R!$#&2-_D%2 and i want to get R2-D2
SELECT '|R!$#&2-_D%2' as Original, 'R2-D2' as Correct FROM DUAL
I believe we need to use Translate function to get rid of special characters, But I would not be knowing what sort of special charecters which appear in the string, In that case how do I use Translate?
I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:
SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL; A -
I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter
I am using Oracle Version 11.2. Here's an example of what I am trying to do..
-- create table with a clob column create table sr_test (c1 CLOB)
-- load data that is more than 4000 characters into clob declare var1 varchar2(32000); begin var1:= '';
for i in 1..5000 loop var1:= var1||i||',';
end loop; dbms_output.put_line(var1); insert into sr_test(c1) values (var1); end;
-- select table to make sure clob is loaded
select c1, dbms_lob.getlength(c1) from sr_test
-- create procedure to return data from table
create procedure sr_p1(result out sys_refcursor) is begin open result for select c1 from sr_test; end;
-- run the procedure to get data
DECLARE RESULT sys_refcursor;
BEGIN RESULT := NULL;
ACCOUNTING.SR_P1 ( RESULT );
:rc0_RESULT := RESULT;
END;
Everything works as intended. However, this procedure is being called from Webservices. According to what I have been told, webservices adds 18ms for each clob that needs to be converted into char so it can be displayed on the screen. So, I need something like this
create procedure sr_p1(result out sys_refcursor) is begin open result for select dbms_lob.substr(c1,32000,1) from sr_test; end;
Is there an alternate method to send more than 4000 characters in the refcursor?
After exporting some data to excel, I noticed that on one row all the columns shifted over some. So I queried this record in the database and noticed that the ADDRESS field has some unknown characters at the end of it. They are little squares. I think they are TABS.
2630 LINDEN BLVD, APT. #8G(2 squares are in here)
ADDRESS_1 "TRIM(:ADDRESS_1)",
Besides trimming the data, is there some other function I can use to clean up the address further?
I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
create table test ( name varchar2(50), descd varchar2(50) ) insert into test values ('kethlin','da,dad!tyerx'); insert into test values ('tauwatson','#$dfegr'); insert into test values ('jennybrown','fsa!!trtw$ fda'); insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST ( C VARCHAR2(2000 BYTE), B NUMBER, A NUMBER );
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP :M_END_YN := :M_END_ALL; [code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN :M_END_ALL := 'N'; END IF;
--code written on button to update those values which are checked.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP IF :M_END_YN = 'Y' THEN [code]......
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
I receive source data with leading zeros. The Rules says: "If there is a leading 0 it has to be removed and data has to be shown starting with character 2".Here is my code I started with:
I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.
I install oracle 10 g but I don't remember password . so I then remove oracle 10 g by select Universal Installer and click Deinstall and then reinstall but I can't reinstall because I don't remove/delete oracle_home I then delete folder in path C:Oracle and reinstall Question
1.in this remove oracle 10 g method true or wrong if wrong , How effect on my computer 2.why after reinstall my computer is very slow.
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10)) insert into testingtype_table values (1124,2424,'cbaaa'); insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10)); create or replace type testingtype_tab as table of testingtype_obj;
Code Block declare l_testingtype_tab testingtype_tab := testingtype_tab(); begin select distinct testingtype_obj(ordernumber ,org_id ,company_name) bulk collect into l_testingtype_tab from testingtype_table; end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
how can i get distinct records through this coding, when i add select distinct col1, col2, col3 from tablename where RECD_ON between :control. REC_ FROM and :control.REC_TO; in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.
declare qry varchar2(5000); n number; alert number; Begin [code]....
As you can see, I removed the first four columns because the eventkey is the same. In this case, there is only the applicant which is different.So the rest should be blank.
How to write a pl/sql query to trim/remove more than one character from string.
Like the itemfield is 'Profit CY' I want it to show as 'Profit' but only for itemfields that say 'Profit CY' in the column for remaining items in column such as 'Loss CY' should stay as it is.
My query is suppose to capture the student's number, date of birth, and number of students which that students mentors. I am suppose to show only the 5 oldest students
SELECT s.std_num, s.birth_date, COUNT(s2.mentor_num) From Student s left outer join Student s2 on s2.mentor_num=s.std_num GROUP BY s.std_num, s.birth_date ORDER BY s.birth_date ASC;
This function works but the problem is in displaying the five records because whenever I apply the rownum<=5 function the counter returns incorrect results. I tried writing another query to fix it but I could not dispay the counter anymore
SELECT ROWNUM as SENIOR, s.std_num, s.birth_date, COUNT(s2.mentor_num) FROM (SELECT s1.std_num, s1.birth_date, COUNT (e2.super_id) From Student s left outer join Student s2 on s2.mentor_num=s1.std_num GROUP BY s1.std_num, s1.birth_date ORDER BY s1.birth_date ASC) S WHERE row_num<=5;
This code only works if I remove the count in the first line but I need to display the number in my result.
