PL/SQL :: Remove Duplicate Values From Concatenated Long String Of State Codes
Dec 4, 2012
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
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]....
I have following query: type EMP_REC is record ( id number, name varchar2(20), dept number );
type EMP_TBL IS TABLE OF EMP_REC INDEX BY BINARY_INTIGER; EMP_TABL1 EMP_TBL;
select * BULK COLLECT into EMP_TBL1 FROM emp;
How to remove duplicate records from EMP_TBL1 collection if exists.I don't want to remove duplicate records from main table. But actually want to remove duplicate records from EMP_TBL1 collection if exists.
I have a view and in that view i need to remove duplicate rows from output. For that i need to run select query in where clause of view if select query return true then we need to execute second condition.
my requirement in view like
And.......... And ((select count(*) from table A where conditions)=1 )then name is null AND
in that code first we need to check first select query condition then we need to apply name is null condition. but i tried to run it but select query not run properly. because tables is used in View.
select am_obj_emp_obj(empno,ename,sal,deptno,service) from (select empno,ename,sal,deptno ,service from emp_vw where 1=1 and rownum < 2000 and service in ('MAN','SACH','SACL','KACL')) WHERE SAL = (SELECT MAX(SAL) FROM EMPS_VW WHERE DEPTNO = deptno or sal is null) union all
[code]...
This query is pulling 12 records of which 6 records are coming from the first query and the same 6 records is coming from the second query after union all Here am_obj_emp_obj is the object type and emp_vw is the view
Now I wanted to remove the duplicate records.When I implement the union operater it is giving me error.
ORA-22950: cannot ORDER objects without MAP or ORDER method.
How can we tune our RMAN Duplicate as it was taking 10+ hours to finish. Current production database size is 800GB. Approx duration of rman online backup (FULL, including archived logs) is 4 to 5 hours.
Here's the rman backup script we used: sql 'alter system archive log current'; list archivelog all; run { show all; report schema; backup database plus archivelog delete all input; } exit
Here's the rman duplicate command we used: run { set until time "to_date(to_char(sysdate,'Mon DD YYYY') || ' 02:30:00', 'Mon DD YYYY HH24:MI:SS')"; allocate auxiliary channel ch1 type disk; duplicate target database to testdb; } exit
I want to remove matching dbid's from the rman catalog without compromising te integrity of the catalog. I do however know a single way of removing them and its using the dbid and db_key which in my case are not not unique. The funny thing is they've running normally for a year now.
I am receiving a ORA-01489: result of string concatenation is too long error on the following code. The size of the MNO_NOTE fields is: MNO_NOTES_1 X(2000). I'd rather not modify the DB table column size, but rather that capacity of the "notes", or whatever structure the concatenated string is stored in. Could I use the substring method?
SELECT TO_DATE(TO_CHAR(mno_date_recorded,'yyyymmdd')|| TO_CHAR(mno_time_recorded,'0009'),'yyyymmddhh24mi') AS create_date, stf_id AS create_user, RTRIM(MNO_NOTES_1)|| RTRIM(MNO_NOTES_2)|| RTRIM(MNO_NOTES_3)|| RTRIM(MNO_NOTES_4)|| RTRIM(MNO_NOTES_5)|| [code]...
SELECT country_name, substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME, substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE, substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as [code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
i want to ,remove consecutive occurance from string
Example I/P: 'POWELL POWELL BRIAN K AND BONNIE POWELL JARRELL JARRELL' to O/P : 'POWELL BRIAN K AND BONNIE POWELL JARRELL'I tried the below code is Working fine , But i wanted to do this using Regexp or Some other Better Method WITH T
I have a sql query which has around 115 columns and out of which 25 columns are of varchar2(2000) and when I run the query I get the ORA-01489: result of string concatenation is too long error.
I tried to use to_clob function for the columns having varchar2(2000) and if I run the sql from toad , it works fine, but when I tried to run the same query from sqlplus and spool to a file, the result doesn't come in a single line. I have tried to import the spool file to my local and open it , but still it doesn't come in a single line, the data is trucated This is how my data looks in the spool file.
1-L31OGM|Red|1|Due|Qualified|02/08/2012||02/08/2012| you are missing a message.
These are the below set options used in the query . I even tried set long 100000000 and also set longchucksize option also, I have tried with WRAP OFF and WRAP ON also ,but still it doesn't work.
SET HEADING OFF SET WRAP OFF SET LINESIZE 32000 SET FEEDBACK OFF SET PAGESIZE 0 SET LONG 32000 SET TRIMSPOOL ON SET ECHO OFF SET TERMOUT OFF
get the data in a single line and using utl_file package is not an option in our project due to security reason.
I use sqlplus in oracle (linux). I have a table and the string cell have long string . Like below :
CODEcolumn A Column B
A BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB .....................................BBBBBBBBBBB
So, I need to edit/update the row A and the value in Column B. But the string in Column B is so long and I only need to edit one character. IF I use update command , I need to type very long string and it is easy to wrong edit .
I use sqlplus in oracle (linux).I have a table and the string cell have long string .
Like below :
column A Column B
A BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB .....................................BBBBBBBBBBB
So, I need to edit/update the row A and the value in Column B.But the string in Column B is so long and I only need to edit one character.IF I use update command , I need to type very long string and it is easy to wrong edit .
SYS@prod> select PLATFORM_ID, PLATFORM_NAME from v$database; PLATFORM_ID ----------- PLATFORM_NAME ------------------------------------------------------------------------- 12 Microsoft Windows x86 64-bit [code]...
as I googled the solution does not seems to apply to my case.it very puzzling that such a short query can produce
ORA-01489: result of string concatenation is too long.
If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.
select length('string with more than 4000 char) from dual;
SELECT 'Existing Tables: ' || LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY table_name) tablenames FROM user_tables;
i receive the error :
ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long" *Cause: String concatenation result is more than the maximum size. *Action: Make sure that the result is less than the maximum size.
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
