SQL & PL/SQL :: How To Update CLOB Column In A Particular Table

Feb 21, 2011

How 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, 2010

I 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.

View 3 Replies View Related

Convert Existing Table With Varchar Column To A Clob?

May 8, 2013

how to convert existing table with varchar column to a clob

View 1 Replies View Related

Server Administration :: Allocating Extent To A Clob Column Of A Table

Dec 30, 2010

I 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))
/

View 3 Replies View Related

Performance Tuning :: Update Currency Column Of One Table Using Other Table Currency Column?

May 11, 2012

I 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) ?

View 7 Replies View Related

Update New Column Within Same Table

Oct 22, 2010

I 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

View 2 Replies View Related

SQL & PL/SQL :: Update Test Table On ID Column

Feb 21, 2012

CREATE 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 )

View 13 Replies View Related

SQL & PL/SQL :: How To Update Some (old) Records In One Column Of Table

May 26, 2011

i 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 .

View 26 Replies View Related

SQL & PL/SQL :: Update Column - Add Two 00 After Alter Table

Apr 14, 2012

I 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

View 13 Replies View Related

SQL & PL/SQL :: Update Single Column In X Table

Sep 2, 2011

i 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.

View 2 Replies View Related

SQL & PL/SQL :: Error While Trying To Update A Column In Table

Jul 31, 2013

I 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]....

View 10 Replies View Related

Update A Column Based On Another In Same Table

Apr 1, 2012

I 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.

View 1 Replies View Related

SQL & PL/SQL :: How To Update Clob Value

Nov 13, 2012

I 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).

View 2 Replies View Related

SQL & PL/SQL :: Update Table Removing First 2 Digits From Column?

Apr 10, 2013

I 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

View 5 Replies View Related

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]....

View 8 Replies View Related

SQL & PL/SQL :: Update Two Table Column In Single Query

May 25, 2012

How 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 ;

View 1 Replies View Related

PL/SQL :: Update Table Column Using Complex Sequence

Feb 6, 2013

I 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

View 5 Replies View Related

PL/SQL :: Update Column Based On Sum Of Data From Another Table

Apr 17, 2013

We 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

View 6 Replies View Related

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.

View 13 Replies View Related

Update Using CLOB Datatype In Oracle

Aug 16, 2010

how 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 Related

Performance Tuning :: Unable To Update Newly Added Column In Existing Table

May 21, 2013

I 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]....

View 15 Replies View Related

XML DB :: Clob Column To XML

Apr 26, 2013

I 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 .

View 8 Replies View Related

SQL & PL/SQL :: Insertion In Clob Column?

Jul 3, 2012

i 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.

View 8 Replies View Related

SQL & PL/SQL :: Read CLOB Column?

Dec 19, 2012

I 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?

View 1 Replies View Related

SQL & PL/SQL :: CLOB Column Append

Feb 13, 2013

I 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;

View 6 Replies View Related

SQL & PL/SQL :: Insert Data In Clob Column?

Jun 13, 2012

i 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 Related

PL/SQL :: Finding Length Of CLOB Column

Jul 11, 2012

I 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.

View 3 Replies View Related

SQL & PL/SQL :: How To Execute The Statement Exist In CLOB Column

Jun 30, 2010

I 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

View 10 Replies View Related

JDeveloper, Java & XML :: Querying XML In CLOB Column?

Mar 17, 2011

I 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]....

View 8 Replies View Related

SQL & PL/SQL :: Append More Rows In Single Clob Column

Apr 9, 2013

I 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]....

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved