SQL & PL/SQL :: Extracting Data From LONG RAW Data Type Column
Oct 7, 2011I have come one requirement where i need to extract data from a LONG RAW data type column.
View 7 Replies
ADVERTISEMENT
Updating Long Data Type Column
Aug 31, 2010I 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.
Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type
Oct 17, 2011Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies View RelatedHow 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.
Server Administration :: Long Data Type Illegal?
Dec 26, 2012when I try comnd create table a as select * from b where 1=2; it says illegal datatype long..i m bemused what sin has the long datatype done?
View 4 Replies View RelatedForms :: Text Item With Long Data Type?
Sep 6, 2011I have a form with an item with "datatype=long". when I am typing in "Arabic" or "Persian" it break the text before line ends.but when I type in English "text" go to end line(in other words line fills to the end) and then breake to next line.It show that the problem is LANGUAGE.
nls_lang=AMERICAN_AMERICA.ar8mswin1256
Regional and language/regional option=Arabic(U.A.E)
in the block/item property : wrap_style= word ----multi_line=yes
Forms :: How To Show Long Type Data In Editor
Mar 18, 2011I have a long datatype field in which text of length more than 4000 is stored. I want to use editor for entering and displaying the data, but it shows the error:-
case 1: when editor is invoked directly- FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-06502
case 2: when I write something in the field- FRM-40209: Field must be of form.
SQL Net Round Trips On Long Data Type Table
Oct 12, 2012We are working on a performance tuning aspect, where in a table has a LONG datatype and number of round trips are increasing based on the number of rows fetched.i.e one round trip for every row.
Background.
1. created a table with LONG data type.
2. inserted bulk load of data.
3. set auto trace on and executed below tests..
SQL> select count(*) from test_long;
COUNT(*)
----------
6110
SQL> desc test_long
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NUMBER
TESTLONG LONG
[code].....
but on LONG column, irrespective of the array size the round trips does not reduce.
SQL & PL/SQL :: Concat Or Append In Long-raw Type Column?
Feb 4, 2012Im 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
View 15 Replies View RelatedSQL & PL/SQL :: What Should Be The Data Type For The Column
May 9, 2012I 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.
SQL & PL/SQL :: Extracting Long Raw Columns
Jun 24, 2010I'm creating a package function that would return the image from the table HR.PER_IMAGES.
Here's the table description of HR.PER_IMAGES
IMAGE_ID NUMBER(15)
IMAGE LONG RAW
PARENT_ID NUMBER(15)
TABLE_NAME VARCHAR2(30)
I have also created a simple PL/SQL code that would supposed to extract the value of the IMAGE column and store it in a variable then return it from a function.
DECLARE
x LONG RAW;
BEGIN
SELECT image
INTO x
FROM per_images
WHERE parent_id = :p_parent_id
;
END;
However, I'm getting the "ORA-06502: PL/SQL: numeric or value error" message. I have tried retrieving an specific image using Oracle Reports Developer and when I queried it was able to render the image on the report page.
XML Type Column Data XML Query?
Mar 16, 2013I 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]...
SQL & PL/SQL :: Table With VARCAHAR2 Data Type Column
Jul 28, 2010I 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"
Cannot Create BLOB Data Type For A Column In Tavle
Jul 6, 2010user@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.
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.
JDeveloper, Java & XML :: Select Data From XML Type Column
Jan 24, 2013I 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'
SQL & PL/SQL :: Table With Modifieddate Column With DATE Data Type
Jun 4, 2012I 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"
SQL & PL/SQL :: Column Of Table With Data Type Varchar2 - Corresponding Index
Jan 10, 2013I 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?
PL/SQL :: How Rownum Works For The Column Having Data Type As Varchar2
Jan 29, 2013bookshelf_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 ?
PL/SQL :: Check If Data Stored In Varchar2 Type Column Is Date Or Not
Jun 29, 2012Is 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;
Client Tools :: Number Data Type Column Does Not Display As Entered?
Jun 28, 2010Now 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]...
PL/SQL :: Extracting Data From XML Message
May 15, 2013Currently, I am running 4 separate queries in order to retrieve specific data from a XML file. Is there a way of extracting all 4 values via XML tags in a single query - e.g. :
Message Reference UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
456123 >(Cancelled)</UploadError>
456123 >4561</UserId>
456123 >1234</SecurityIdentifier
At the moment, I am extracting the required info as follows.
Example query 1: message_content, 2000, 2303 > retrieves starting point for an error header
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)
Once retrieved, I transfer to Excel and use a formula to extract the specific header (e.g. using =MID(B1,1,11))
Example query 2: message_content, 2000, 581 > retrieves the starting point for a user id.
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 581))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)
PL/SQL :: Extracting Data From A String
Oct 4, 2013This is my sample data:
WITH table_data
AS (SELECT 'ABC12345*Z23*1234*Cheese*24/02/2011' str FROM DUAL
UNION ALL
SELECT 'Aasda1ewr3345*A32345*1234*Bread*01/11/2012' str FROM DUAL
UNION ALL
SELECT 'dsf31212*TEST*124234*Blue*06/07/2007' str FROM DUAL
[code].........
I can extract the data from before the first *, but I need to also be able to extract the data from between the 1st and 2nd *s, e.g. Z23, A32345, TEST, THIS and THAT from the sample data above.
SQL & PL/SQL :: Extracting Data From Web Service Response
Mar 26, 2013I need to extract tag values from REST web service response.
Webservice response
Quote:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <LicenseDetailsRes xmlns="com:*******services:LicenseDetailsRes">
<ResponseCode>LD001 - SUCCESS</ResponseCode>
<OrderNumber>51663852</OrderNumber>
<LineNumber>8</LineNumber>
<quantity>0</quantity>
</LicenseDetailsRes>
I am storing above response in a table column 'output_xm' of xmltype datatype.
My requirement is to extract 'ResponseCode' & 'quantity' values from above response. For which I am using below query but it's not working.
SELECT x.order_number
,x.line_number
,x.tnc_pid
,EXTRACT (VALUE (d), 'LicenseDetailsRes/ResponseCode/text()').getstringval () status
,EXTRACT (VALUE (d), 'LicenseDetailsRes/quantity/text()').getstringval () pak_quantity
FROM xxcfi.xxcfi_vt_tnc_pak_details x
,TABLE (XMLSEQUENCE (EXTRACT (x.output_xml, '/LicenseDetailsRes'))) d
WHERE x.order_number = 50403901
AND x.line_number = 2
AND x.tnc_pid = 'R-LMS-4.0-100-K9';
Client Tools :: Not Extracting All Of The Data
Jan 30, 2013I exported and imported data from one oracle database to another, but not all the data got loaded in to the destination database. Basically it is filtering the data. Can it be because of the reason that the sql developer may be an express edition?
View 4 Replies View RelatedPL/SQL :: Extracting Data From XML And Inserting Into Table
Aug 8, 2013I have an XML of the following format:
<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students>
Now i have to insert this xml into DB , the table consist of following columns ( row number , property name , value ) Expected out put is (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is
1. how to get the tag names and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?
SQL & PL/SQL :: Extracting Data From XML File - Not Working?
Oct 4, 2012I need to extract the values from XML file.I tried with method described in below links -
[URLs]....
My XML file is -
<?xml version="1.0" ?>
<!DOCTYPE main [
<!ELEMENT main (DATA_RECORD*)>
<!ELEMENT DATA_RECORD (STATE_CODE?,JOB_NAME?,WC_CODE?,JOB_ID?,STATUS?,LOG_MESSAGE?)+>
<!ELEMENT STATE_CODE (#PCDATA)>
[code]...
The query is not returning any rows.
SQL & PL/SQL :: Extracting Blob Data Using Oracle Function?
Jul 26, 2012Below is the function code used to extract data from blob column. The function works fine when the blob data length < 2000 bytes. When it is more than, it is throwing an error as below.
Table name: mr_test
Columns: id number
seo blob
CREATE OR REPLACE FUNCTION fn_mr_blob(in_id IN number) return varchar2
IS
len NUMBER;
[Code]....
ORA-01489: result of string concatenation is too long
when I replcae the
"SELECT myvar||trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;"
with
SELECT trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;
myvar1 := myvar1||myvar;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
PL/SQL :: Extracting Data In A String Separated In Brackets
Jun 7, 2012I have a scenario where I have to extract data in a string which is enclosed in [].
Here is what I want to do..
input:
[name_first] [name_mi] [name_last]
required output: this should return all the data that is enclosed in the brackets.
name_first
name_mi
name_last
I have tried the "REGEXP_SUBSTR" but my database version is 9i, and it is available only from 10g.
Store Data In CLOD Data Type - How To Create A Unique Index
May 20, 2013We have been recommended to store data in CLOD data type.
Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.
We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?