SQL & PL/SQL :: String Parsing

Aug 28, 2012

I have the following table A that contain one column "MYREC"

MYREC
1253 69889897 89884 891254
568989 89897891 321 698751232
1239892 123358798 7899 58123457

I need to parse the variable column, the issue is that the number of spaces.

The string might start with 2 or more white spaces, that I can get rid of with the LTRIM function..I'm having difficulties with the rest ):

View 5 Replies


ADVERTISEMENT

PL/SQL :: String Parsing Using Oracle

Sep 17, 2012

Oracle SQL.I need parsing Strings.

Example:

ID Name Address num
1 Peter 123 park st,223 park st 123,223

Answer
ID Name Address num
1 Peter 123 park st 123
1 Peter 223 Park St 223

View 4 Replies View Related

Parsing Comma-separated String?

Jan 22, 2009

I have a string value like -- a,,b,c,d,e,f

Using just sql, I want to put each value of the above string in a different row. So the output should be --

a
b
c
d
e
f

using procedures it would not be that great but I want to do it just using queries.

View 1 Replies View Related

Parsing Address And Name

Jan 25, 2012

I intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender.

E.g:
Nguyen Van A
Nguyen Thi B

After parsing, the result will be shown as:

TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale

 Parsed.gif ( 4.94K )
Number of downloads: 2

I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".

2/ Another procedure/function is [i]ParseAddress with the requirement as:[/i]Address field is divided into Street, Group, Area, Ward, County fields
E.g.:No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London

The result:

StreetGroupArea Ward County
No 6 Sum StreetGroup 8Area 2ABCD London

I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->it doesn't work.

View 1 Replies View Related

SQL & PL/SQL :: Parsing Name And Address?

Jan 25, 2012

I intend to write procedures/functions in PL/SQL to parse Name

field into various fields : Title, FirstName, MiddleName, LastName, Gender
E.g:
Nguyen Van A
Nguyen Thi B

After parsing, the result will be shown as:

TitleFirstNameMiddleNameLastNameGender
MrNguyenVanAMale
MsNguyenThiBFemale

I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender =

Female. Otherwise, Title = "Mr", and Gender = "Male".

2/ Another procedure/function is ParseAddress with the

requirement as:

Address field is divided into Street, Group, Area, Ward, County fields
E.g.:
No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London

The result:

StreetGroupAreaWardCounty No 6 Sum StreetGroup 8Area 2ABCDLondon

I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL ->

it doesn't work.

View 3 Replies View Related

JDeveloper, Java & XML :: Parsing Through PL/SQL

Nov 21, 2011

oracle PL/SQL. I have almost finished this xml parsing task but their is one problem. Actually in our table there are more than 70-80 columns & due to that only I don't want to put the hard coded column name in my procedure, because if I will do that, the unnecessary procedure size will be increase(means line of code).Here is our procedure

Create or replace procedure loadMyXML(dir_name IN varchar2, xmlfile IN varchar2) AS
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.Parser;
l_doc dbms_xmldom.DOMDocument;
l_nl1 dbms_xmldom.DOMNodeList;
l_nl2 dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
node1 dbms_xmldom.DOMNode;
l_colName VARCHAR2(100);
[code]...

View 3 Replies View Related

SQL & PL/SQL :: Parsing BLOB Contents

Mar 28, 2013

I have some BLOB contents in the format of...

DK99F17,AA,032820130840,Other
ABCD,AA,032820130840,OV
AAZ123,BC,032820130932,DWL
CBA12345,ZA,032820130939,Other
Each BLOB is associated to a file name in the format...
03282013100002_thisfile.txt

The blob for each file may be zero rows to n rows in size, but typically there are 2 to 5 rows (four rows were shown in the rows above).The following kind of gets me there, but not quite as it splits up the BLOB rows at the comma and not the line break (HEX=0D0A / CRLF).

with rec as
(select fs.file_name, utl_raw.cast_to_varchar2(fs.file_data) file_data
from tada.files_store fs
where fs.file_name like '%citations.txt'
and trunc(fs.date_created) = to_date('26-MAR-2013','DD-MON-YYYY'))
[code].....

View 4 Replies View Related

SQL & PL/SQL :: XML Parsing Into Base Table

May 20, 2010

I want to load XML into base table using PL/SQL procedure.For that I have wrote procedure but that does not work well .

View 4 Replies View Related

Bind Variable In Parsing?

Aug 13, 2013

Identical statements  from this link :  Parsing in Oracle — DatabaseJournal.com d. The bind variable types of the new statement should be of same type as the identified matching statement. i am getting confuse here .. when  parsing occurs some links saying about bind variable.but official document  never said about bind variables.

View 3 Replies View Related

PL/SQL :: Parsing Date With MM.YYYY And Between

Apr 17, 2013

I got a table with a Date in it. Lets called it Testdate and an item called start month and end month.

The Item (varchar2) looks like

MM.YYYY f.e. 03.2012 and is a

I want to see all data between start and endmonth.

First I tried to convert the date from my orignial table:

SELECT distinct TO_CHAR(TESTDATE, 'MM'||'.'||'YYYY')
FROM mv_auswertung;This works:

TO_CHAR(TESTDATE,'MM'||'.'||'YYYY')
02.2012
12.2012
01.2012

But now I am trying to get all data between start and enddate:

Select *
from mv_auswertung
where
TO_CHAR(TESTDATE, 'MM'||'.'||'YYYY') between
to_CHAR(:P7_startmonth) and to_char(:P7_endmonth)

View 5 Replies View Related

Query Parsing And Buffer Cache?

Apr 18, 2012

I want to know what exact process happens in oracle architecture when an update query is fired.

View 1 Replies View Related

JDeveloper, Java & XML :: Parsing In PL/SQL Without Any DBMS_XML Packages?

Jun 15, 2012

i dont have access to any Oracle XML related pacakges, however I have to pull the data from the following xml.Eg: The alternativeIdentifier.Name should be a column and the value should be the data for the column. Same applies to other tags under characteristics tag.

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:xsi="[url]http://www.w3.org/2001/XMLSchema-instance[/url]"
xmlns:SOAP-ENV="[url]http://schemas.xmlsoap.org/soap/envelope/[/url]"
xmlns:xsd="[url]http://www.w3.org/2001/XMLSchema[/url]"
xmlns:SOAP-ENC="[url]http://schemas.xmlsoap.org/soap/encoding/[/url]"

[code]....

View 29 Replies View Related

JDeveloper, Java & XML :: Parsing Containing Array Of Data?

Jan 12, 2011

I have to parse an XML and store the data in a table.

My XML :-
WITH data AS (
SELECT XMLTYPE(
'<?xml version=''1.0'' encoding=''UTF-8''?>

[Code].....

The output is :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

There will be cases when I will get multiple values for the columns conversationid, command, value.

<?xml version=''1.0'' encoding=''UTF-8''?>
<S:Envelope xmlns:S="http://schemas.xmlsoap.org/soap/envelope/">
<S:Body>

[Code].....

In that case the output should be as :-

CONVERSATIONID COMMAND VALUE
a949-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V1

iiii-9614-4e9c-9f49-831ab6cc6a41 OPEN_BROWSER V2

Not sure how to go about XML parsing when I have multiple values.

View 4 Replies View Related

Application Express :: Jasper Response Parsing?

Nov 7, 2012

I have a lot of problems in parsing SOAP web service response of Jasper The major problem is that the report document in the response appears at the end of the response. It appears even after the closing tag of the soap response. The images in the response are returned in binary form.

Because the response is partially binary and partially characters so I am not able to parse it correctly. If i make a web service call using APEX webservice references and then put the response in a collection then I get an error. I guess APEX collections are not able to handle this response. I say this because If I call the webservice using UTL_HTTP, then I get the response.

parsed a Jasper SOAP web service response which has a report in it.I have gone through h[URL]..but I am not using it.My question is focused on parsing the response and not on finding other ways of getting it

View 9 Replies View Related

SQL & PL/SQL :: Suspicious Parsing Of Remote Stored Proc?

May 12, 2011

Having:
3 databases: CYR1,CYR2,UTF
Database links was established CYR2 -> CYR1, UTF -> CYR1
Function GETALL is on the CYR1 site
SQL> desc GETALL
Parameter Type Mode Default?
--------- -------- ---- --------
(RESULT) VARCHAR2
FMT VARCHAR2 IN

[code]....

My questions:
1) What could be reason that oracle would look so deeply in source of REMOTE procedure, especially as because remote procedure is in VALID, compiled state?!!
2) Could we classify such restrictions as a bug?

This is how I call the remote function:
declare
v varchar(300);
begin
v:= getall @orcl8.oegc.tgk11.com@csk ('%slu%',129);
dbms_output.put_line(v);
end;

This is "offending" statement in getall (basic static SELECT), that I had to throw away for call to getall to succeed:
select .... from ПРАВА_ПРОГ_ДЕТАЛИ ...

ПРАВА_ПРОГ_ДЕТАЛИ would occupy exactly 32 byte in UTF8 (2*15_cyrillic_chars + 2 underscores), but in CYR1, this table name is absolutely valid, only 17 chars long, because Cyrillic characters are represented by one byte!

View 10 Replies View Related

JDeveloper, Java & XML :: Parsing And Load It Into Different Tables?

Jan 21, 2013

I'm new to XML and got a requirement to parse xml and load it into different tables.

Here is the XML file which i need to parse

<?xml version="1.0" encoding="ISO-8859-1"?>
<Interactions>
<Interaction ProductCode="ABCD" SourceCode="TEST_SRC" ExternalID="abcd1234">
<Consumer AddressLine1="9999 Test Ave" BirthDate="1939-03-19T00:00:00.000-05:00" CaptureDate="2013-01-

[code]...

XML has a parent element Interaction and each Interaction has different elements like Consumer, Campaign, Response, Survey, MultiSuppressions. Now i need to insert Consumer element data into table1, Response and Campaign elements data into table2, Survey data into Table3 and Multisuppression data into table4 with interaction number (this can be rownumber) so that i can link all the tables based on interaction number.

I googled on parsing xml and found xmltable can be used to parse xml. I wrote below procedure, but it will not work if i include MultSuppressions (will get cartesians).

create table table1 (interaction_id number,
sourcecode varchar2(20),
externalid varchar2(20),
productcode varchar2(20),
Addressline1 varchar2(40),

[code]...

View 15 Replies View Related

XML DB :: Error When Parsing XML Data That Is Loaded Using SQL Loader

Sep 7, 2012

I am loading content of an XML file into a table using SQL loader.Below is my Control file script -

LOAD DATA
INFILE *
INTO TABLE xx_cc_response_xml_stg TRUNCATE
xmltype(XML_DATA)
FIELDS
( COLUMN_ID constant 1,
file_name filler char(4000),
XML_DATA LOBFILE(file_name) TERMINATED BY EOF)
BEGINDATA
B2B_MasterDataUpdate_20120906152137.xml
------------------------------------------------------------------------------------

The file B2B_MasterDataUpdate_20120906152137.xml is correct and XML is well formed.When i try to query for XML_DATA (datatype XMLType) column in the table, i cannot see any content in the record, and it appears as (XMLTYPE)When I parse this XML using the below,

select value(d)
from xxnbn_cc_response_xml_stg a,
table(xmlsequence(extract(a.xml_data,'/InventorySearch'))) d;
------------------------------------------------------------------------------------
I get this error:
------------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [qmcxdsSelf4], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
exceptions.     This indicates that a process has encountered an
exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number
------------------------------------------------------------------------------------

View 1 Replies View Related

Forms :: FRM-15004 - Error While Parsing Join Condition?

Apr 16, 2008

I'm trying to create a relation from child block to the master block that I've created . Foreighn key is there from child to parent table.The error I get is below:

FRM-15004: Error while parsing join condition

The join condition is correct, but I'm still clueless as to what it could be.Is it a form bug?

View 7 Replies View Related

JDeveloper, Java & XML :: Improper Parsing Of Schema File?

Sep 17, 2012

I have used jaxb (used xjc.exe ) to parse a XML schema file.However after the class files were generated,one of the setter method is missing.Is it possible that this can happen or have i done something wrong.

the XSD file is

<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="shiporder">
<xs:complexType>

[code]....

The classes that got generated are (snippet of it)
public class Shiporder {
@XmlElement(required = true)
protected String orderperson;
@XmlElement(required = true)

[code]...

This does not contain setitem() method

View 1 Replies View Related

SQL & PL/SQL :: KUP-00554 / Error Encountered While Parsing Access Parameters

Jun 25, 2012

I am trying to create an EXT table but is constantly having the following problem, not sure why. I have done a few checks and used the scripts used as a standard but still is experiencing an error.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "minussign": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 9 column 1

The Table creation script is

CREATE TABLE TABLE1_EXT
(
BUSINESS_DATE VARCHAR2(50 BYTE),
CIN_CODE VARCHAR2(50 BYTE),
CIS_CODE VARCHAR2(50 BYTE)
)

[code]....

And the data used as a sample is

BusinessDate|CIN|CISCode
19062012|1000026697|CTTGLIE
19062012|1000079416|Z29MBGB

View 12 Replies View Related

Forms :: FRM-15004 Error While Parsing Join Condition

Aug 16, 2010

I receive a "FRM-15004 Error while parsing join condition" when attempting to create a relation between block1 (parent table) and block2 (child table). If I do a simple straight join the statement is accepted but if I use a Decode then it results in an error. How to successfully use a decode or a nvl in a join statement of a relation?

Results in Error:

block1.case_id = block2.case_id and
decode(block1.employee_id, null, block1.entity_id, block1.employee_id) =
decode(block2.employee_id, null, block2.entity_id, block2.employee_id)

This join is accepted but I would like to use a decode since a record can have either a employee_id or entity_id, not both

block1.case_id = block2.case_id and
block1.employee_id = block2.employee_id and
block1.entity_id = block2.entity_id

View 1 Replies View Related

PL/SQL :: How To Use Bind Variable In The Query To Avoid Hard Parsing

Jul 27, 2012

I have a query which is using literals

strquery:='SELECT SUMTOTAL FROM tab1 WHERE BATCHNO = '''
      || gBNo
      || ''' AND A_ID = '''
      || g_id
      || ''' AND L_ID = '''
      || g_LId
      || '''  AND S_Code = ''C_3'' ';

execute immediate strquery; I have been asked to use a bind variable to avoid hard parsing.How do i do it?

View 2 Replies View Related

JDeveloper, Java & XML :: Parsing With Special Character As Part Of Data

Sep 10, 2013

I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

Quote:ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken

I'm receiving a XML from some external source and can not change it.

View 8 Replies View Related

JDeveloper, Java & XML :: Migrating Database From Oracle 9i To 10g - Parsing Failed

May 2, 2011

We are in process of Migrating our database from Oracle 9i to Oracle 10g.

I am getting below error while parsing XML in 10g.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '//soap:Envelope/soap:Header/coHeader/company/text()'

Same code works fine in Oracle 9i database with same XML. Is there any difference in XML TYPE functionality in Oracle 9i and 10g?

View 4 Replies View Related

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I have this error (and solution):

ORA-02085: database link string connects to string

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?

View 7 Replies View Related

How To Find Time Taken For Parsing / Creating Plan And Actual Data Retrieval

Jul 13, 2010

Is there any way to find out the division between the time taken for query parsing, creating execution plan and actual data retrieval seperately? If I enable 'set timing on' I see the elapsed time which is the total time taken for all these 3. Some of my queries are taking long time when I run it first time and so want to know what is it taking long? is it the parsing or creating the execution plan, if so what can I optimize.

View 3 Replies View Related

Server Utilities :: SQL*Loader-350 Syntax Error When Parsing Data Type

Dec 7, 2010

I ran the following control file in sql loader:

LOAD DATA
INFILE "gateway.csv"
truncate
INTO TABLE GATEWAY
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols

[code]....

and I got the following error:

zcyds891:/opt/oracle> sqlldr gwcem/gwcem@pfs control=gateway.ctl log=/tmp/ldr.log bad=/tmp/bad.log
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Dec 7 05:07:59 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL*Loader-350: Syntax error at line 12.
Expecting "," or ")", found "INTERGER".
GATEWAYPROTOCOL INTERGER,
^

The current OS is Solaris, SunOS 5.10.

View 3 Replies View Related

Server Utilities :: KUP-00554 / Error Encountered While Parsing Access Parameters

Dec 2, 2010

I receive the following error message

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "binary_double,

[code].....

when I select count(*) on the external table created below.

SQL> CREATE TABLE cac_500_load
2 (
3 EMAILADDRESS VARCHAR2(80),
4 FIRSTNAME VARCHAR2(60),
5 LASTNAME VARCHAR2(60),
6 STREETADDRESS VARCHAR2(100),

[code].....

Here is the db version info:

SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

View 12 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

View 3 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 Replies View Related







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