SQL & PL/SQL :: Update String Anywhere In Text
Mar 3, 2011
We have table with a column REMARKS,where in text somewhere we are storing name also,Now we are planning to update only names with their service number
REMARKS Column Sample row text:
1.It has been created by John
2.John Created this
3.Assistance taken by John for this
Any query which can only replace john in all rows with 1234 .I can update the value if John comes at same position in all rows using SUBSTR() and concat rest of the text.But not getting how to update JOHN in rows at different positions.
View 7 Replies
ADVERTISEMENT
Oct 18, 2012
I have one table for eg. TB_Fruits.
In that i have one column FruitsName(Varchar)
In that column i am storing string in comma separated values.
Select FruitsName from tb_fruits;
Result: orange,banana,apple
Now the issue is suppose if i try to insert any of these fruits name again then it should not allow me to insert.
Suppose now if i try to insert ('grapes,banana')
or
('apple,grapes')
the orange,banana,apple can be in any position.
How to check if any of these names already exist or not in the column fruitsname?
I cannot use like or INstr function here. because the position is not fixed not even string.
View 1 Replies
View Related
Oct 28, 2012
when i insert string text in hebrew i see ????????? how to fix it ?
work on Oracle 11.2 on Oracle Developer using my C# program
View 3 Replies
View Related
Aug 4, 2010
I want to select the view names from text column in dba_views.
View 7 Replies
View Related
Oct 14, 2013
code to extract email id (full email id) from a string/text using Oracle Regular Expressions.
Example -
"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com
View 9 Replies
View Related
Jan 23, 2011
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 .
View 1 Replies
View Related
Jan 23, 2011
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 .
View 8 Replies
View Related
Oct 4, 2011
I need to concatenate string to the number field in an update statement like this:
update test1 set model_pin = seq_no || '_' || model
where eoc_code like 'AEW%'
When I run this command in sql , I get ERROR at line 1:
ORA-01722: invalid number
View 7 Replies
View Related
Nov 29, 2007
When I try to update a date column with this:
UPDATE
event_request
SET
REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi')
WHERE
eventID=123
Oracle returns this error:
ERROR at line 4:ORA-01861: literal does not match format string
where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:
$date = '2007-12-25 08:35'; INSERT INTO (REQ_EVENT_DATE) VALUES (to_date('$date', 'yyyy-mm-dd hh24:mi'));
So - what is wrong with the UPDATE query?
View 1 Replies
View Related
Jul 13, 2011
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
View 7 Replies
View Related
Sep 12, 2012
Have got basic form on a table and have a textarea which holds Notes added by user.
So Notes database field is updated on Save / Apply changes button being pressed.But would really like any text added / appended to the Notes field to be prefixed by userid and date / timestamp.
Is it possible via dynamic actions or Javascript to have any new text added / typed to be auto prefixed as per above.
Would only want the first key press in the filed to trigger the auto-prefix and if added text was deleted then the auto prefix to be deleted as well ?? If user doesn't press Save / Apply changes obviously want to leave existing Notes as is.
View 5 Replies
View Related
May 5, 2010
Is there text changed trigger with text item function like when_list_changed trigger of list item?
View 7 Replies
View Related
Apr 17, 2012
Can I use rich text item on oracle form10G with some simple features like BOLD, UNDERLINE, ITALIC and if possible one more feature like spell check.
I Google my requirement, but mostly I found win word attachment. Further more if I can save this type of data in field then how can I print in report.
View 3 Replies
View Related
Sep 17, 2013
I am on Oracle 11.2.0.3 on Linux and have implemented Oracle Text.I created Oracle Text indexes with default setting. However in an oracle white paper I read that the default setting may not be right. Here is the excerpt from the white paper by Roger Ford:URL....(Part of this white paper below....)Index Memory.
As mentioned above, cached $I entries are flushed to disk each time the indexing memory is exhausted. The default index memory at installation is a mere 12MB, which is very low. Users can specify up to 50MB at index creation time, but this is still pretty low. This would be done by a CREATE INDEX statement something like: CREATE INDEX myindex ON mytable(mycol) INDEXTYPE IS ctxsys.context PARAMETERS ('index memory 50M'); Allow index memory settings above 50MB, the CTXSYS user must first increase the value of the MAX_INDEX_MEMORY parameter, like this: begin ctx_ adm. set_ parameter('max_index_memory', '500M'); end; The setting for index memory should never be so high as to cause paging, as this will have a serious effect on indexing speed. On smaller dedicated systems, it is sometimes advantageous to temporarily decrease the amount of memory consumed by the Oracle SGA (for example by decreasing DB_CACHE_SIZE and/or SHARED_POOL_SIZE) during the index creation process.
Once the index has been created, the SGA size can be increased again to improve query performance." (End here from the white paper excerpt)My question is:
1) To apply this procedure (ctx_adm.set_parameter) required me to login as CTXSYS user. Is that right? or can it be avoided and be done from the application schema? This user CTXSYS is locked by default and I had to unlock it. Is that ok to do in production?
2) What is the value that I should use for the max_index_memory should it be 500 mb - my SGA is 2 GB in Dev/ QA and 3GB in production. Also in the index creation what is the value I should set for index memory parameter - I had left that at default but how should I change now? Should it be 50MB as shown in example above?
3) The white paper also refer to rebuilding an index at some interval like once in a month: ALTER INDEX DR$index_name$X REBUILD ONLINE; We are on Oracle 11g and the white paper was written in 2003.
View 5 Replies
View Related
Dec 30, 2010
I have a requirement in one of my forms screen.I have a text box(large text area) which should display a help text file when i move my cursor on the topics displayed on the screen.know the code and the properties to be changed in the text box to accommodate large external file text.
View 1 Replies
View Related
Dec 23, 2009
Is there any way to convert HTML format text to Plain Text ?
View 26 Replies
View Related
Jun 19, 2012
I'm new to Oracle Text. I want to implement search for the unique ids. Like google search when the user start typing 123 it need to brings anything starting with 123 and has show like entries how google will shows. When I add number 4 to like 1234 then it has bring numbers starting with 1234.
View 2 Replies
View Related
Jun 19, 2012
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
View 7 Replies
View Related
Mar 14, 2013
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
View 3 Replies
View Related
May 9, 2008
show an ex to use string buffer for select statemnt
View 1 Replies
View Related
May 14, 2010
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
View 4 Replies
View Related
Sep 7, 2010
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
View 1 Replies
View Related
Aug 17, 2010
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
View 9 Replies
View Related
May 29, 2012
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
View 3 Replies
View Related
Mar 6, 2013
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
View 10 Replies
View Related
May 30, 2013
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
View 5 Replies
View Related
Mar 21, 2011
I have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
View 11 Replies
View Related
May 30, 2011
Is it possible to get the same name of file name from imported text file?
What i've is, I import the text file then I exported it.
can I get the same name of the imported text file?
View 2 Replies
View Related
Jan 25, 2012
After many tests I can't make work and update of the same table inside the same table.
Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Sample Data :
create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);
insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......
When I execute :
update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
View 13 Replies
View Related
Jul 19, 2011
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies
View Related
