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
ADVERTISEMENT
SQL & PL/SQL :: Convert Clob To Varchar Data Type
Aug 16, 2012I want to convert a clob datatype to varchar data type.I have a clob data type in the below format.
-----------------------------------------------------------------------------------------
<HTML><HEAD><TITLE>Tata Communications Limited</TITLE><BODY leftMargin=20 topMargin=10 rightMargin=20 marginheight=10 marginwidth=10><Table border="0" cellpadding="0" cellspacing="0"
-----------------------------------------------------
I want to insert all the values into varchar datatype.provide the sample code how to convert.
SQL & PL/SQL :: How To Append Data To Existing CLOB Field In Oracle Table
Sep 13, 2010code to append data of 32k to exisitng CLOB field in an oracle table..
View 1 Replies View RelatedSQL & PL/SQL :: Converting Varchar To Clob Within SQL Union?
Feb 14, 2013Why does oracle not allow the following query
select to_clob(1) from dual
union
select wm_concat(sysdate) from dual;
wm_concat returns a clob. To make both queries in the union have the same type columns i convert the column in the first query to a clob but Oracle gives me an [1]: ORA-00932: inconsistent datatypes: expected - got CLOB error even though both are returning a clob value.
Each of the queries work individually and both return a clob value.
SQL & PL/SQL :: Convert Long To Varchar
Aug 30, 2012display or get values stored in a long column.i tried the below code, but myvar is shown as null. am i missing something here or is there a better apporoach than this ?. There are actually 16 rows returned for this query but with empty values for 'high_value' column which is critical for me. How can i do this long to varchar convertion ?
SET SERVEROUTPUT ON
DECLARE
my_var long;
BEGIN
for x in ( SELECT high_value
FROM all_tab_partitions
WHERE table_name = 'SALES_DISCOUNT'
AND table_owner = 'CED12'
ORDER BY partition_position DESC )
[code]....
Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention
May 15, 2011i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
How To Add Sequence Column To Existing Table With Records
Dec 8, 2012I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04. I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"
SQL & PL/SQL :: How To Add Sequence Column To Existing Table With Records
Dec 8, 2012I had created a new table named USERLOG with two fields from a previous VIEW. The table already consist of about 9000 records. The two fields taken from the VIEW, i.e. weblog_views consist of IP (consists of IP address), and WEB_LINK (consists of URL). This is the code I used,
CREATE TABLE USERLOG
AS
SELECT C_IP, WEB_LINK FROM weblog_views;
I want to add another column to this table called the USER_ID, which would consists of a sequence starting with 1 to 9000 records to create a unique id for each existing rows. I'm using Oracle SQL Developer: ODMiner version 3.0.04.
I tried using the AUTO-INCREMENT option,
ALTER TABLE USERLOG
ADD USER_ID INT UNSIGNED NOT NULL AUTO_INCREMENT;
But I get an error with this,
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option 01735. 00000 - "invalid ALTER TABLE option"
SQL & PL/SQL :: Adding Column In Middle Of Existing Table
Sep 6, 2010below query.
1) How to add a new column to the existing table's particular position, instead of atlast.
2) I created a table without mentioned the datatype size as below Create table dummy (name char, age number). Then what is the default size will be allocated for those column's?
SQL & PL/SQL :: Can Change Existing Table Column Data-type
May 18, 2011i have one table emp in this table i have the column eno,ename,hiredate and i have data also in this table.
my eno colum data type is number so now i have to change this colum data type from number to varchar2.
if yes how can when i am trying to change this colum data type i got this error
Failed to commit: ORA-01439: column to be modified must be empty to change datatype.
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 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]....
Server 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))
/
SQL & PL/SQL :: Getting Highest Value From Varchar Column
Feb 12, 2011I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score
John ==== 200*
Zohaib ==== 299
Ali ==== 0*
John ==== 200
Maria ==== 150*
Ali ==== 0
Maria ==== absent
John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score
Zohaib ==== 299
John ==== 200*
Maria ==== 150*
Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
SQL & PL/SQL :: How To Convert Varchar2 To CLOB
Mar 28, 2008How to convert a varchar2 column to CLOB when there is a thousands of records in it.
View 15 Replies View RelatedSQL & PL/SQL :: Convert LONG To CLOB
Apr 20, 2009I would like to know if there is possible to transform a CLOB type variable intro a long type variable. I know, that LONG is obsolete in Oracle, but I need it, because in a PL/SQL 'execute immediate' sentence a CLOB is not allowed.
View 5 Replies View RelatedSQL & PL/SQL :: Get Numeric Values From A Column Of Varchar Data?
Aug 16, 2013 I have a table and data like mentioned below.
create table emp( ename varchar2(20));
insert into emp values ('122');
insert into emp values ('abc');
insert into emp values ('0.2');
insert into emp values ('0-5');
insert into emp values ('25-30');
SQL> Select * from emp;
| ENAME |
-------------------
| 122 |
| abc |
| 0.2 |
| 0-5 |
| 25-30 |
I am running the code
SQL>select regexp_substr(ename , '^[[:digit:]]+.[[:digit:]]+$|^[[:digit:]]+$')
from emp;
AFTER RUNNING I AM GETTING THIS
| REGEXP_SUBSTR(ENAME,'^[[:DIGIT:]]+.[[:DIGIT:]]+$|^[[:DIGIT:]]+$') |
---------------------------------------------------------------------
| 122 |
| (null) |
| 0.2 |
| 0-5 |
| 25-30 |
| (null) |
Why it's not excluding '0-5' and '25-30', how I should write code to exclude this and Is there is any function in oracle to check for numeric in column and print.
SQL & PL/SQL :: Select Numeric Values From A Varchar Column
Feb 5, 2011select numeric values from a varchar column
For Example:
select * from t1 ;
ID
----------
00300
ABCXY
04230
xyzab
i need to fetch only numeric values from column id
My output should be
00300
04230
How To Convert Long Data Type To CLOB
Nov 15, 2007i am trying insert data from one DB to other DB table. one field data type is LONG in first DB table, Same field data type in other DB is CLOB.
i used TO_LOB function to convert from LONG to CLOB data type.
My problem is, i used this TO_LOB function, i got illegal operation of LONG Data type.
SQL & PL/SQL :: Using Varchar Parameter In Queries With Number Column Can Result In Performance Degrade
Jul 4, 2013I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.
Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;
Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.
Copy Data From One Field (varchar) To Another Within A Table
Oct 5, 2010i have a table COLOR having alphanumeric fields i.e Color Description & Color Ref. i want to copy the values from Color Description to Color Ref.
what will the select statement for this?
SQL & PL/SQL :: How To Convert Multiple Column Values Into Single Column
Jul 19, 2013CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
PL/SQL :: To Set A Bonus Value From The Existing Column
Jun 2, 2013we are providing a salary column. from that salary column we need to calculate bonus with the condition as bonus=(salary+10%). how to execute this.
View 5 Replies View RelatedSQL & PL/SQL :: Adding A Column Between Existing Columns
Jul 8, 2010I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies View RelatedXML 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;
Application Express :: 4.1 - How To Add A Report Column To Existing Page
Oct 12, 2012On a "Form on a table with report" page created with the wizard, I want to add a new report column from the underlying table (this is the multi-row page). If I try to add the column name to the query, Apex tells me:
"You have requested to change the Interactive Report query. If you added columns to the query, they will not be displayed when the report is run. You will need to use the actions menu and either select the columns or click Reset. If you removed any columns from the query, it will disable existing filters, highlight rules, and other report settings referencing those columns. Please confirm your request."
I sure don't want to start over and re-build the page, so where the heck is this "actions menu"?
I'm running Apex 4.1.
PL/SQL :: Convert CLOB With WE8ISO8859P15 Charset To UTF-8 Charset
Jun 28, 2013I have a CLOB containing a XML code and all XML tags where created using the DB charset: WE8ISO8859P15.
For example:
{code}
<?xml version="1.0" encoding="ISO-8859-1"?>
<certidao xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<funcionario>
[Code]...
I need to maintain this charset value.
But now I need to convert all my CLOB data into UTF-8 charset for external requirements and stored this new CLOB in a table column.
I can't change the charset of the session. I've tried to use a function
{code}
FUNCTION convert_xml (pcl_xml IN CLOB)
RETURN CLOB
[Code]....
This function apparently works, but when I try to validate the XML content (after I replace the encoding="ISO-8859-1" to encoding="UTF-8") the validation software tells me that I have some characters that should not be present in a file using UTF-8 encoding.