SQL & PL/SQL :: How To Update CLOB Column In A Particular Table
Feb 21, 2011How to update a CLOB column in a table, suppose the string is greater than 4000 size.
View 7 Replies
ADVERTISEMENT
SQL & PL/SQL :: Update Clob Column Using Execute Immediate?
Aug 30, 2010I am encountering an error message while updating a xmltype column using dynamic sql statement. I am using dynamic sql here as the table is not placed in the same schema from where the plsql procedure is invoked. The schema name is passed to the procedure as an argument. I am using below pseudo code for this purpose.
Create procedure myproc(p_schemaname varchar2, p_id number)
is
p_clob clob;
p_str varchar2(2000);
begin
[code]...
This is throwing an error 'missing expression' at the line of 'exeute immediate'.
But it works if I run a static sql update by hard coding the schema name in the statement.
Convert Existing Table With Varchar Column To A Clob?
May 8, 2013how to convert existing table with varchar column to a clob
View 1 Replies View RelatedServer Administration :: Allocating Extent To A Clob Column Of A Table
Dec 30, 2010I have a table with two clob columns and need to manually allocate space to the table and to its lob segment. Is the following command correct?
--to allocate extent to the table
alter table emp allocate extent;
--the table has columns named col1 and col2 which are clob
--to allocate extents to the columns
alter table emp modify lob (col1) (allocate extent (size 10m))
/
alter table emp modify lob (col2) (allocate extent (size 10m))
/
Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?
May 11, 2012I am trying to update currency column of one table using the currency column of other table using the following sql code.
update ODS.SO_ITEM OSI
set CURRENCY__CODE=(select currency__code from sa_sales.SO_ITEM SSI where SSI.ID=OSI.ID)
This update is taking taking a lot of time and is never ending.
should i create index on source table (SA_SALES.SO_ITEM) or on target table (ODS.SO_ITEM) ?
Update New Column Within Same Table
Oct 22, 2010I have a table ITEM as under;
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19I
4KABAN.NM.12.21564101SU23I
4KABAN.NM.24,5.22491015SR08I
4KABAN.NM.24,5.23181038SR22I
4KABAN.NM.24,5.23491015SR08I
4KABAN.NM.24,5.24121161SR26I
how to update the filed from NAME to NEW_NAME, I need following output.
ARTICLE VRTCODENAMESTATUSNEW_NAME
4KABAN.NM.12.21121143SU19ISU19
4KABAN.NM.12.21564101SU23ISU23
4KABAN.NM.24,5.22491015SR08ISR08
4KABAN.NM.24,5.23181038SR22ISR22
4KABAN.NM.24,5.23491015SR08ISR08
4KABAN.NM.24,5.24121161SR26ISR26
SQL & PL/SQL :: Update Test Table On ID Column
Feb 21, 2012CREATE TABLE TEST
(
NAME VARCHAR2(1000 BYTE),
ID NUMBER
)
[Code]...
I want the update the TEST table with TEST1 based on id column,i tried as like.it's not working..
UPDATE test t1
SET t1.ID = ( select ID from test1 t2
where t1.name = t2.email and rownum=1 )
SQL & PL/SQL :: How To Update Some (old) Records In One Column Of Table
May 26, 2011i have a table in my PD database which have more than 30,000 records .some records in a column say p_code is not tagged with code like '9876543'while other records are tagged in this column with code such as '19022345678'.
Now i want to update these records with tagging 1902 with each one .
SQL & PL/SQL :: Update Column - Add Two 00 After Alter Table
Apr 14, 2012I have a table PR in that some data is there for instance, My Mr_NO was Char(11) I modify MR_No to Char(13)My Table Structure now is:
MR_No Char(13),
Mr_Date Date
My Previous data is MR_NO=APN00209085
I want to add two 00 after alter the table I want my result data like APN0000209085.I am updating through this command
update PR set Substr(MR_No,4,2)='00'
ERROR at line 1: ORA-00927: missing equal sign
Result I want is APN0000209085
SQL & PL/SQL :: Update Single Column In X Table
Sep 2, 2011i need a query for update..the logic is
i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.
SQL & PL/SQL :: Error While Trying To Update A Column In Table
Jul 31, 2013I have a scenario where I need to update field in report_input table.It has bind variables in it which will be prompted while running the query.I see that QUERY_VALUE field that need to be updated has length of VARCHAR2(3000).
desc report_input
Name Null Type
----------------- -------- --------------
QUERY_NAME NOT NULL VARCHAR2(64)
QUERY_VALUE NOT NULL VARCHAR2(3000)
[code]....
Update A Column Based On Another In Same Table
Apr 1, 2012I am trying to update a column based on another column in the same table (student table) and a column from another table (school table)
Code is:
update student_table
set student_code =
(select l.student_code
from school_table l, student_table n
where l.school = n.schoolname)
I get the following error ORA - 01427 Single-row subquery returns more than one row.
SQL & PL/SQL :: How To Update Clob Value
Nov 13, 2012I the table VOYAGERS with the following data.
ID is of type number and DETAILS is of type CLOB.
ID DETAILS
--- --------
100 The ship has left san diego http:/localhost/icons/sandiego.png to okinawa on nov 10, 2011.
I need to update the record(id = 100) by replacing the url "http:/localhost/icons/sandiego.png" with "http:/localhost/icons/okinawa.png".
I need a procedure where I will pass the ID value, replace string(i.e http:/localhost/icons/sandiego.png) and replace with string (ie. http:/localhost/icons/okinawa.png).
SQL & PL/SQL :: Update Table Removing First 2 Digits From Column?
Apr 10, 2013I have a table like:
0035987850 P
0035987851 P
0035987852 P
I would like to update removing 00 in the first column. So after update to have:
35987850 P
35987851 P
35987852 P
SQL & PL/SQL :: Update Num_songs_in_album Column In Album Table
Nov 23, 2011 I want this trigger to automatically update the num_songs_in_album column in the album table whenever rows are inserted or deleted into the Track table.
SQL> CREATE OR REPLACE TRIGGER update_num_songs_in_album
2 BEFORE INSERT OR DELETE ON Track
3 FOR EACH ROW
4 BEGIN
5 IF INSERTING THEN
[code]....
SQL & PL/SQL :: Update Two Table Column In Single Query
May 25, 2012How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
PL/SQL :: Update Table Column Using Complex Sequence
Feb 6, 2013I use a sequence to generate an alphanumeric code and I need to update all rows of a table with that sequence. The sequence is created with this code:
SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;
I have tried many things - most recently
Update t3_temp_table
SET mergecode =
SELECT (CHR(TRUNC(s.nextval/1000) + ASCII('A')) || LTRIM(TO_CHAR(MOD(s.currval,1000),'099'))
FROM dual;
I get missing expression error.
It works fine if I use a straight s.nextval
PL/SQL :: Update Column Based On Sum Of Data From Another Table
Apr 17, 2013We had two tables.
Table 1: matusetrans
ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13
[Code]....
Table 2: Inventory
ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
Edit/Delete Message
SQL & PL/SQL :: Update Column In A Table Which Has 3 Columns Of 16 Million Rows?
Apr 25, 2012 trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.
Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.
Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.
Update Using CLOB Datatype In Oracle
Aug 16, 2010how can i update CLOB values in Oracle? I am passing string values to Clob datatype from .Net Eg '13223311','12122112','122125552'
View 6 Replies View RelatedPerformance Tuning :: Unable To Update Newly Added Column In Existing Table
May 21, 2013I am facing some challenge while running update query on newly added column in existing table.
Environment Details
Oracle 9i, version 9.2.0.6
Os Unix Aix 6.1
No of records in table : 12572770
Below are the step i followed.
1. In table testtablename, I have added new column COLUMNNAME29 with datatype VARCHAR2(8).
2. After adding the new column, i executed the update query to populate the data form COLUMNNAME1 to COLUMNNAME29.
3. The query is executed using COLUMNNAME24 in where clause, to drive query in index based.
SQL> desc testtablename
Name Null? Type
----------------------------------------- -------- ----------------------------
COLUMNNAME1 VARCHAR2(8)
COLUMNNAME2 CHAR(1)
COLUMNNAME3 CHAR(1)
COLUMNNAME4 VARCHAR2(8)
COLUMNNAME5 VARCHAR2(11)
[Code]...
Table altered.
SQL> select index_name, column_position, column_name from dba_ind_columns where table_name = 'TESTTABLENAME' order by index_name,column_position;
INDEX_NAME COLUMN_POSITION COLUMN_NAME
------------------------------ --------------- --------------------------------------------------
IDX_TESTTABLENAME 1 COLUMNNAME24
Problem faced & My analysis
1. The update query is hanging in database, it's not progressing (In single update, approximately 40000 records will get update)
2. No oracle error thrown in alert log or in session where the query being executed.
3. The event for the query is "db file sequential read".
4. When i update the newly added column COLUMNNAME29 with static value "1", the update completed successfully in few seconds.
5. Then i changed the static value to "1111" and executed the update statement, which result to query hanging in database.
6. I tried to update the existing column(COLUMNNAME1) in table with static value "1111", the update completed successfully.
Below are the queries completed successfully
Update Testtablename
Set Columnname29 = '1'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
[Code]...
Below are the queries hanging in database
Update Testtablename
Set Columnname29 = Columnname1
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Update Testtablename
Set Columnname29 = '1111'
Where Columnname24 >= To_Date('01-12-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )
And Columnname24 < To_Date('01-01-2003 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
Below is character set in database
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
[Code]....
XML DB :: Clob Column To XML
Apr 26, 2013I have table like-
CREATE TABLE C2X_TEST(NAME VARCHAR2(100),XML_CLOB CLOB,XML_XML XMLType);
here in XML_CLOB column xml files are stored.
Now i want to update XML_XML column with corresponding XML_CLOB value .
SQL & PL/SQL :: Insertion In Clob Column?
Jul 3, 2012i have another problem with clob column , when i try to insert data in it through the stored procedure then it shows an
error- 'ORA-01460: unimplemented or unreasonable conversion requested clob' and this error especially arise when data to e inserted in clob column , have more than 4000 characters.
sql>exec pi_test(1,'sysdate','text of clob column'); ORA-01460: unimplemented or unreasonable conversion requested clob
where is the error.
SQL & PL/SQL :: Read CLOB Column?
Dec 19, 2012I have two tables File_Master and File_Detail
File_Master Primary key FILE_ID
File_Detail Primary key FILE_ID,LINE_ID
I have a CLOB column FILE_CONTENT in table File_Master. For every FILE_ID record in File_Master, several hundreds of lines are stored in the CLOB column.
I want to read this CLOB column 'File_Content' and and break every line (1000 Characters)
piecewise to populate columns of File_Detail.
Since there will be thousands of lines to process, what would be the best approach in writing PL/SQL code for better performance?
SQL & PL/SQL :: CLOB Column Append
Feb 13, 2013I have a requirement when I need to append and show the contents of a CLOB (rich text) column into the Open office report.
The use case is as below.
User(s) can enter the details into a CLOB column.
This data entered by each user need to be concatenated onto another CLOB column which will hold all the history of changes. Mainly i wanted to perform a concatenation of these two columns.
|
| User Entered | Stored |
| Data Stored in Clob Column 1| Data Stored in Clob Column 2 |
| v_clob_1 | v_clob_target |
-------------------------------------------------------------------------------
|"Text 1" and a image |"Text 1" and a image |
-------------------------------------------------------------------------------
Any method to achieve this case. I had tried the following method to achieve this. But no success.
v_merge clob;
v_clob_target clob;
v_clob_1 clob;
1)
DBMS_LOB.APPEND(v_merge, v_clob_target);
DBMS_LOB.APPEND(v_merge, v_clob_1);
2)
DBMS_LOB.APPEND(v_clob_target, v_clob_1);
3)
v_merge:= v_clob_target || ''|| v_clob_1;
v_clob_target := v_merge;
SQL & PL/SQL :: Insert Data In Clob Column?
Jun 13, 2012i have a problem when i try to insert a large character string of nearly 1 lac characters (code of html) in a clob column of my test table, then i get an error "ORA-01704: string literal too long" , i didnot understand that why clob column is not storing this data.
View 1 Replies View RelatedPL/SQL :: Finding Length Of CLOB Column
Jul 11, 2012I had a varchar2 variable which was storing some data and I could use the LENGTH function to get the length of the data. However, If I change it to CLOB. What is the best way to get the length?
v_clob_size:= (DBMS_LOB.getlength(v_clob)) / 1024 / 1024;
DBMS_OUTPUT.put_line('CLOB Size ' || v_clob_size);
another one is
LENGTHB(TO_CHAR(SUBSTR(<clob-column>,1,4000))) But this seems restricted to first 4000 characters only.
SQL & PL/SQL :: How To Execute The Statement Exist In CLOB Column
Jun 30, 2010I have oracle table with a column of datatype CLOB. In the clob column having the manipulation statements (update, insert) as records.
How to execute the statements exists in a clob column as records?
my table structure and one record is
CREATE TABLE ACTN_CFG_T
(
OBJ_ID NUMBER(32) NOT NULL,
ACTN_ID NUMBER(38) NOT NULL,
ACTN_SEQ NUMBER(2) NOT NULL,
RU_DEF CLOB NOT NULL
[code]....
I want to execute the update statement when i call the obj_id. I used the follwing procedure but error.
1 DECLARE
2 V_ABC CLOB;
3 BEGIN
4 SELECT RU_DEF INTO V_ABC FROM ACTN_CFG_T WHERE OBJ_ID = 1625;
5 EXECUTE IMMEDIATE V_ABC;
6 END;
error is...
ORA-06550: line 5, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored
JDeveloper, Java & XML :: Querying XML In CLOB Column?
Mar 17, 2011I have a CLOB column called XML_DATA that has (not-surprisingly) xml data in it that's housed inside a table called HMS_XML_TRANSFER. It has been giving me a headache because I'm unable at this point to use the xml field as a condition to get its TRANS_SEQUENCE number. The where clause doesn't work.
SELECT TRANS_SEQUENCE, XML_DATA
FROM HMS_XML_TRANSFER
WHERE EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]');
The only test that I have been able to get working is the one below.
SELECT TRANS_SEQUENCE, EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057/ACTIVITY_CODE')
FROM HMS_XML_TRANSFER
WHERE TRANS_SEQUENCE = '8191602';
It will give me the ACTIVITY_CODE element so I know I can pull data from the XML but I can't do the reverse in the first example which is what I need because I don't know the TRANS_SEQUENCE number, I just know the ACTIVITY_CODE.
I have over 202 error messages logged in Teradata SQL from my many and varied attempts to get this to work using every example I could find online.
As an example this has not worked either...
WHERE EXISTNODE(XML_DATA, '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]') = 1;
So the question... How do I properly form my SQL statement so I can use the XML column's ACTIVITY_CODE element to get the TRANS_SEQUENCE column field? Oh and I'd like to see both columns in the result.
Below is the version of Oracle I'm using, the description of the Table HMS_XML_TRANSFER, and a sample of the XML that comes from XML_DATA. I can't seems to get tabs working.
===============
ORACLE VERSION
===============
SQL*Plus: Release 9.0.1.3.0 - Production on Thu Mar 17 08:18:15 2011
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
=========================
TABLE HMS_XML_TRANSFER
=========================
Name Null? Type
--------------- --------- --------------
TRANS_TYPE NOT NULL VARCHAR2(10)
DATE_IN NOT NULL DATE
DATE_PROCESSED DATE
STATUS VARCHAR2(8)
[code]....
SQL & PL/SQL :: Append More Rows In Single Clob Column
Apr 9, 2013I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.
CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
[code]....