SQL & PL/SQL :: What Should Be The Data Type For The Column
May 9, 2012
I 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 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.
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.
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 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'
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?
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 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;
Now 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 );
I am using the same query on a different table and there's no problem on it:
select to_char(ENTRYDATE,'MM-DD-YYYY HH24:MI:SS') from TBL_XX_PROGRAM where ENTRYDATE > sysdate -10 and rownum <10 03-03-2011 17:06:48 03-03-2011 17:06:48 03-03-2011 16:59:08 03-03-2011 16:59:08 03-03-2011 12:04:21
I checked the DDL of both tables and the only difference is that the TBL_XX_PROGRAM have the ENTRYDATE field defined as not null and the ENTRYDATE on the TBL_BANDWIDTH_READS does not have the same constraint, could it affect?
I am trying to run select query on xml using xmltable. One of the tag returns date and I want to take it in date format in xmltable column.
select t.* from XMLTYPETEST xt , XMLTable ('/TestData/trade' passing xt.data columns test varchar2(100) path 'GenTest/Id', testdate date path 'DateTest/Date')t;
But its throwing error as -
SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine ORA-01830: date format picture ends before converting entire input string 29958. 00000 - "fatal error occurred in the execution of ODCIINDEXCREATE routine" *Cause: Failed to successfully execute the ODCIIndexCreate routine.
I have oracle 10g database running on Sun Solaris. My database character set is AL32UTF8. Our user wants to store a double byte character and an image, I want to know what the column type should be using for these.
Im having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type
how I can avoid PLW-07204 "conversion away from column type may result in sub-optimal query plan" (or rather the problem notified here by Oracle) from my PL/SQL code.My code looks as follows:
create table T(D date); create or replace procedure P is cursor C(D_IN IN DATE) is select D from T where trunc(D)=SYSDATE; begin null; end P; [code]...
I don't want to disable the warning - it is there for a reason.
Additional description for the issue: It seems that D is of typ=12, while trunc(D) is of typ=13. You can use DUMP to read this info. This is described at URL....
I have a table and data as below.I need to select all the records if value have both 'M' and 'D'.If there is only 'M' or 'D' then select should not pull any records.
WITH data as ( Select '1' id, 'M' value from dual union all Select '1' id, 'M' value from dual union all Select '1' id, 'D' value from dual union all Select '1' id, 'D' value from dual ) select value from data group by value
I tried below query but it is not working.
WITH data as ( Select '1' id, 'M' value from dual union all Select '1' id, 'M' value from dual union all Select '1' id, 'D' value from dual union all Select '1' id, 'D' value from dual ) select * from data group by value having sum(count(distinct(value))) > 1
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?
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?
I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.
FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER, O_error_message IN OUT VARCHAR2, O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC", I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE) return BOOLEAN is
I am working on a one Stored Procedure which would display the output from the above function as,
Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?
I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.
Here is an example.
CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11)); SELECT object_name, object_type FROM user_objects WHERE object_type = 'TYPE'; CREATE TABLE Z (A CHAR(4), B SSN_T);