i 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.
I have a table with VARCAHAR2 data type column, I am unable to update that column with the following data.
UPDATE we set sname='(([Emp].Job.Catageory
IN 'SALES,COMPUTER,SCIENCE,TELESERVICE')
AND ([Employee].IDtype IN 'ABC'))'
I am getting the error "ORA-00933: SQL command not properly ended"
I have a table with modifieddate column with 'DATE' data type.I am facing date format exception and tried with to_char, to_date but its throwing invalid number exception. how to format date accordingly.
SELECT * FROM EMP WHERE modifieddate > '31-Dec-2011 18:30';
ORA-01722: invalid number
01722. 00000 - "invalid number"
I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column
like '%abc%'
like 'abc%'
like '&abc'
Will be the corresponding index will be for those cases?
I´m newbie as Apex developer using Apex 4.0 with OracleXE 11.2, and now my problem is:
I have a tabular form where one of the columns, when calling the form, is a Display Item, so that you cannot change existing values.
I want, when adding a new row, that column become a Select List (named LOV) based on a query that exclude existing values in other rows already inserted.
Can I change that Display Item to Select List (named LOV) programmatically at runtime? If not, what other options do I have?
i 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.
I 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"
I 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"
below 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?
how to convert existing table with varchar column to a clob
View 1 Replies View RelatedI have come one requirement where i need to extract data from a LONG RAW data type column.
View 7 Replies View RelatedI have to create a table which should store data at Week level. The table have the following columns
Product id, Loc id, Business group id, FISCAL WEEK , Revenue,
Fiscal week column will have data as '2011-W01', '2011-W47' etc.
What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.
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]....
I have extracted in the following XML document some Mpeg7 visual descriptors from an image and I saved it in an XMLType column. I would like to use XMLQuery to extract the data from the Value node. how write such a query. I could not get the proper Xpath to any node.
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Mpeg7 xmlns="http://www.mpeg7.org/2001/MPEG-7_Schema" xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance">
<DescriptionUnit xsi:type="DescriptorCollectionType">
[code]...
i what to update a column data type am geting this error
ORA-02267: column type incompatible with referenced column type
my query is
when i run
SELECT b.table_name||
' joins to the '||
a.table_name||
' table on '||
c.column_name||
' = '||
d.column_name Table_relationships
[code]....
I have a task to update one of the rows in a table (having only 2 columns, number and long) which is long data type. We are on Oracle 10g. Not sure how to use update for a long data type column.
I have tried using dbms_metadata_util.long2varchar, but still not getting what I want.
I want to get the following format of data in row format using PLSQL. I want to do that in using a shell script also.
Suppose I have the data like this
123
45
2
789
how to write it in PLSQL as follows:
1427
25 8
3 9
user@tank> create table filetable(id number(6), file_content BLOB);
0 rows affected (0.02 seconds)
vlanke@tank_db> desc filetable;
ID NULL NUMBER(6,0)
FILE_CONTENT NULL
I am new to Database field.
- I created a table with 2 columns id (datatype as number) and file_content datatype as BLOB.
- My question is under desc filetable; query why does it not show me a data type as BLOB.
- create table query does not return any error to me. How do I verify that FILE_CONTENT column has datatype as blob.
I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows
"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......
I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns
SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'
bookshelf_test table structure
title varchar2(100) y
publisher varchar2(20) y
categoryname varchar2(20) y
rating varchar2(2) y
my query is,
select ROWNUM AS "Rank",title,publisher from (select rating,title,publisher from bookshelf_test order by rating desc ) where ROWNUM <=3
returns result ,
1 1 MY LEDGER KOCH PRESS
2 2 TO KILL A MOCKINGBIRD HARPERCOLLINS
3 3 THE MISMEASURE OF MAN W.W. NORTON
But inner query (select rating,title,publisher from bookshelf_test order by rating desc ) returns,
1 5 WONDERFUL LIFE W.W.NORTON
2 5 THE MISMEASURE OF MAN W.W. NORTON
3 5 TO KILL A MOCKINGBIRD HARPERCOLLINS
4 5 MY LEDGER KOCH PRESS
5 4 TRUMAN SIMON SCHUSTER
6 4 GOSPEL PICADOR
7 4 HARRY POTTER AND THE GOBLET OF FIRE SCHOLASTIC
8 4 INNUMERACY VINTAGE BOOKS
9 4 JOHN ADAMS SIMON SCHUSTER
10 4 JOURNALS OF LEWIS AND CLARK MARINER
11 4 LETTERS AND PAPERS FROM PRISON SCRIBNER
12 4 PREACHING TO HEAD AND HEART ABINGDON PRESS
13 4 THE SHIPPING NEWS SIMON SCHUSTER
14 4 THE GOOD BOOK BARD
15 4 THE DISCOVERERS RANDOM HOUSE
16 3 THE COST OF DISCIPLESHIP TOUCHSTONE
17 3 SHOELESS JOE MARINER
18 3 KIERKEGAARD ANTHOLOGY PRINCETON UNIV PR
19 3 EMMA WHO SAVED MY LIFE ST MARTIN'S PRESS
20 3 EITHER/OR PENGUIN
21 3 CHARLOTTE'S WEB HARPERTROPHY
22 3 BOX SOCIALS MARINER
23 3 ANNE OF GREEN GABLES GRAMMERCY
24 3 WEST WITH THE NIGHT NORTH POINT PRESS
25 3 UNDER THE EYE OF THE CLOCK ARCADE PUB
26 3 TRUMPET OF THE SWAN HARPERCOLLINS
27 2 COMPLETE POEMS OF JOHN KEATS VIKING
28 1 POLAR EXPRESS HOUGHTON MIFFLIN
29 1 GOOD DOG, CARL LITTLE SIMON
30 1 MIDNIGHT MAGIC SCHOLASTIC
31 1 RUNAWAY BUNNY HARPERFESTIVAL
why final queries top 3 rows r different than inner query ?
Is it possible to replace datatype of column of one table with another table's column datatype using %type like below
SQL>create table test1 (v1 varchar2(10));
SQL>create table test2 (v1 test1.v1%type);
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1);
INSERT INTO test('ABC');
INSERT INTO test(SYSDATE);
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY'));
INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
INSERT INTO test('15/01/2012');
...
commit;
code to append data of 32k to exisitng CLOB field in an oracle table..
View 1 Replies View RelatedNow i have a problem which i am facing for the first time. The problem is:
I have created a table test1 with two column of number data type and the column witdth is 25. Then enter the data but when i select the recrod it does not display the column data as it was entered.
SQL> create table test1
2 (
3 startno number(25),
4 endno number(25)
5 );
Table created.
SQL> insert into test1
[code]...
I have a table called cust_file, his table consists of a lot of columns (one of these columns called cus_tax) and have a lot of data,I use oracle 11g, I want to change the default value of the column cus_tax to be equal 1, I wrote
ALTER TABLE cust_file MODIFY(cus_tax DEFAULT 1); table alteredbut
after I inserted new data to test the operation, I found that the new record has a value
= null for the column cus_taxthen
I tested using the following query select
data_default from all_tab_columns where table_name='CUST_FILE' and column_name='CUS_TAX'; no rows selected...
Change the default value of the column cus_tax.
I have a table with structure as:
CREATE TABLE XML_TABLE_1
(
ID NUMBER NOT NULL,
SOURCE VARCHAR2(255 CHAR) NOT NULL,
XML_TEXT SYS.XMLTYPE,
CREATION_DATE TIMESTAMP(6) NOT NULL
[code].....
- So HOW do I find the total size occupied by this table. Does BINARY storage work as LOB storage. i.e. I need to consider USER_LOBS as well for this. OR foll. will work
select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024 )) as tablesize_in_GB
From dba_segments
where segment_name = 'XML_TABLE_1'
and OWNER = 'SCHEMANAME'
group by segment_name ;
- Also if I am copying it to another table of same structure as:
Insert /*+ append */ into XML_TABLE_2 Select * from XML_TABLE_1.
Then how much space in ROllbackSegment do I need. Is it equal to the size of the table XML_TABLE_1?
Is theree any way to generate the insert script (with the data) for an existing table.
In Toad, we can generate the DDL for a particular table. Just curious if it can be done using any Tool.
Is it possible to change the datatype of a column in a table online, using DBMS_REDEFINITION?
If Yes, then which of the options will be used with DBMS_REDEFINITION package?
table already exist & its little data too, may have to imp rest of lost data, is this the right command?
imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y CONSTRAINTS=n
scenerio2 (if have to drop & recreate the entire table) is this the right command?
imp SYSTEM/password FILE=file.dmp FROMUSER=black TOUSER=blake TABLES=(vcr_mappings, tablename2) ignore=Y
just for single table imp
I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object (
U_RANK number(2),
U_SCHOOL varchar2(150),
U_COUNTRY varchar2(150),
U_SCORE number(3)
)
[code].....
DBMS_OUTPUT:
------------
Warnings: --->
W (1): Warning: execution completed with warning
<---
0 record(s) affected
[Executed: 11/11/2012 9:12:19 PM] [Execution: 31ms]