SQL & PL/SQL :: Extracting Values Acquired Via UTL_TCP?

Jul 26, 2010

Using UTL_TCP package (for the first time; maybe there's smarter way to do that), I captured contents of a certain web page. The page contains (along with some text, images, etc.), a list of values I'd like to extract. Something like this:

PRVI miravira pond 23.7.2010 102,2221
NEKI miravira pond 23.7.2010 105,0996
DRUGI miravira pond 22.7.2010 101,3789

The result (output) of the PL/SQL procedure contains several hundreds of lines. I narrowed the output to a part which I'm interested in. It looks like this (a screenshot, because [code] or [pre] tags won't allow me to paint text, while the others don't preserve formatting):

Red values are what I'm looking for. The first one represents a date (July 23rd 2010), and the second one is amount (105,0996).

Now, what's the problem: the above mess doesn't look the same every day (probably because the rest of data that appears on a web page changes too). Therefore, SUBSTR (with hard-coded positions) that seems to be working today is wrong tomorrow. Moreover, IF condition I used might not provide desired part of a web page every time.

Here's the code (modified a little bit, unimportant for what matters):

declare
c utl_tcp.connection;
n number;
buffer varchar2(255);
l_x_dat varchar2(10);
l_x_izn varchar2(10);

[code].....

L_X_DAT and L_X_IZN are values I need. SUBSTR parameters, as I said, are far from being good.Is there any smart way to extract those two values? What (Oracle) technology could be used here? Or should I just try to, somehow, set SUBSTR values correctly (dynamically)?

If there was a way to uniquely fetch those values (instead of getting the whole web page and digging for what I'm interested in), it would be extraordinary.

View 11 Replies


ADVERTISEMENT

JDeveloper / Java & XML :: Extracting Values From String

Aug 11, 2012

I would like to extract only the values from the XML string. The xml string looks like

<addressId>1</addressId><addressSource xsi:nil="true"/><addressInfoName xsi:nil="true"/><addressLine1>245 Murray Ln SW Bldg 14</addressLine1><addressLine2 xsi:nil="true"/><addressLine3 xsi:nil="true"/><addressLine4 xsi:nil="true"/><phoneNumber xsi:nil="true"/><phoneNumberExt xsi:nil="true"/><city>Washington</city><county xsi:nil="true"/><stateOrProvince>DC</stateOrProvince><ZIPCode>20528-1002</ZIPCode><country>USA</country><congressionalDistrict xsi:nil="true"/></address>

And the expected output is
245 Murray Ln SW Bldg 14
Washington
DC
20528-1002
USA

I need to extract only the values from the XMl string using sql.

View 4 Replies View Related

JDeveloper, Java & XML :: Extracting Values - Not Returning Message ID

Aug 27, 2013

I am having two XML's in my table.

XML1 =
<?xml version="1.0" encoding="UTF-8" ?>
<Twist xmlns="URL...." xmlns:ns2="dsig:URL....xmlns:dsig="URL....
xsi:schemaLocation=URL....
xsi:type="ElectronicBillingMsg">
<messageId>TWISTMSG</messageId>
</Twist>
[code]...

I am using the below query to extract value from XML.

SELECT extractvalue(xml_column, '/Twist/messageId') MsgId FROM mytab;

When i run this query on XML2, it gives the output.But when the same query is fired on XML1, its not returning the message id.

View 3 Replies View Related

SQL & PL/SQL :: How To Use UTL_TCP

May 14, 2010

code for using utl_tcp for communication between client and server.

my requirement is: i am using utl_tcp package for tcp/ip communication between client(my client application based on c#) and server(plsql code for server).

when i am sending data to client, then client receives that data successfully but when client sends back some data to server, my code does not receive client's requested data.

View 3 Replies View Related

UTL_TCP Connection Limit?

May 27, 2009

I am using UTL_TCP package of Oracle to connect to remote server. As per my architecture, I am opening multiple connections TCP in the same Oracle session.

I have come arround the limiation of 16 connections with this package.

View 8 Replies View Related

SQL & PL/SQL :: UTL_TCP Returning Blank Line?

Feb 8, 2010

I am trying to use utl_tcp to connect, through TCP/IP, to a 3rd party program PC Miler. When I use telnet, PC Miler works without any issue. However, when I use utl_tcp, beginning from the second PC Miler API call, when I do a utl_tcp.get_line to get the result, the first line that return is always blank (with 1 white space). Here is my program.

---------------------------------------------------
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);

[code]....

Note that the 1st and 3rd API call is the same and should give me the same results. The 2nd API is supposed to give me 1 line back. At the end of each output, the server will end will the word "Ready". I use that to indicate when to exit the loop.

If I disconnect and reconnect after each API call, each output result will be correct because each API call will become the "1st" call since connection - like this program:

DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);

[code]....

If there is something wrong with PC Miler, why would it work when I telnet through a Windows Command prompt? If it is not PC Miler and utl_tcp, what else can be wrong?

View 8 Replies View Related

SQL & PL/SQL :: Extracting Long Raw Columns

Jun 24, 2010

I'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.

View 2 Replies View Related

SQL & PL/SQL :: Extracting Part Of A String

Jan 9, 2013

To make SQL query to before and after specific character.

Create table test(flist not null VARCHAR2(200));

First field content with below record:

FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP

to filter the above record from FLIST column thorugh sql script as below:

FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP

means remove first 5 charator and after APXXXXXXXXX.

Output of SQL query should come like below:

CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION

View 24 Replies View Related

SQL & PL/SQL :: Extracting Date From Timestamp?

Jan 18, 2012

How to extract date from a timestamp data

for example

Input-15/06/2010 05:30:00.000000
output: 15/06/2010

View 3 Replies View Related

PL/SQL :: Extracting Data From XML Message

May 15, 2013

Currently, 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)

View 13 Replies View Related

PL/SQL :: Extracting Data From A String

Oct 4, 2013

This 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. 

View 4 Replies View Related

SQL & PL/SQL :: Extracting User Who Does Not Exist In Column

Oct 30, 2012

I have two tables.

First one is

STAFF
======
STAFFNUM NAME
======== ====
1 A
2 B
3 C

SUBJECT
=======
SUBCODE SUBNAME LECTURER
======= ======= ========
A1 ABC 1
A2 EFG 2
A3 HIJ 1

I did the following query

SELECT STAFF.STAFFNUM, STAFF.NAME, SUBJECT.LECTURER
FROM STAFF,SUBJECT
WHERE STAFF.STAFFNUM NOT IN SUBJECT.LECTURER

It will show me..

B and C is not teaching A1.
A and C is not Teaching A2.
B and C is not teaching A3.

This is not what I want.

What I want is to show who is not teaching any subjects.So the expected result is only C coming out.

View 10 Replies View Related

SQL & PL/SQL :: Extracting Data From Web Service Response

Mar 26, 2013

I 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';

View 2 Replies View Related

Client Tools :: Not Extracting All Of The Data

Jan 30, 2013

I 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 Related

PL/SQL :: Extracting Data From XML And Inserting Into Table

Aug 8, 2013

I 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 ?

View 8 Replies View Related

SQL & PL/SQL :: Extracting Data From XML File - Not Working?

Oct 4, 2012

I 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.

View 3 Replies View Related

Extracting Table Relationships In Oracle

Sep 30, 2008

I have requirement of extracting the relationships between all tables in the Database(Oracle 10.2.02) created by CA Unicenter Servicedesk.

Tell me about any tools/scripts which I can use to extract this information. I can do it manually, but it is time consuming.

View 4 Replies View Related

SQL & PL/SQL :: How To Retain Special Characters While Extracting From DB In UNIX

Jul 16, 2013

In my DB there are special characters are stored like "Świętochłowice". So in Unix script while extracting these characters, I have used
export LANG="universal.UTF-8" in order to make it English. which works fine.

But my question is how to retain this special character intact as, it is in DB?

So in UNIX script
export LANG="universal.UTF-8"
sqlplus -s uname/pwd@hostname/schema << ENDSQL | sed -e "s/Connected.//" -e "/^$/d"
set pagesize 0 feedback off verify off heading off echo off serveroutput on size 10000
spool /path/out.txt
Select name from tablename where is=12;
spool off;
exit
ENDSQL

Output is "Swietochlowice" (makes sense),but how to get the output as "Świętochłowice" which is in DB? I have tried different NLS_LANG option, but no success.

View 5 Replies View Related

SQL & PL/SQL :: Extracting Blob Data Using Oracle Function?

Jul 26, 2012

Below 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

View 1 Replies View Related

PL/SQL :: Extracting Data In A String Separated In Brackets

Jun 7, 2012

I 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.

View 3 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

Jul 13, 2011

I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :

TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209

My expected results should look like :

ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209

View 7 Replies View Related

JDeveloper, Java & XML :: Extracting Sub-nodes From XML With Multiple Nodes?

Oct 27, 2011

how to extract sub nodes from multi nodes in this file.

<?xml version="1.0" encoding="utf-8"?>
<Orders>
<LeasingCoNum>123456</LeasingCoNum>
<DateTime>10/05/2011 10:00:00 AM</DateTime>
<NewOrders>
<Order>

[code]....

View 9 Replies View Related

SQL & PL/SQL :: Extracting Data From LONG RAW Data Type Column

Oct 7, 2011

I have come one requirement where i need to extract data from a LONG RAW data type column.

View 7 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

Replacing Null Values Of Outer Join With Meaningful Values

Dec 3, 2010

I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.

Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......

The result of this is:

Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868

As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.

I need the output to be

Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

Oct 28, 2013

I am searching the simplest way for ad hoc MINUS.I do:

SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....

and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?

View 6 Replies View Related

PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column

Jul 25, 2013

From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?

View 2 Replies View Related

PL/SQL :: ORA-00947 - Not Enough Values But Enough Values Are Being Returned

Dec 18, 2012

I created a package with some types, and every was compiling fine. However, when I ran the new function, I got an error: ORA-21700: object does not exist or is marked for delete

After a little research, I realized that the types would have to be declared outside the package. As soon as I did that, I suddenly started getting the "not enough values" errors on all my types. I compared the number of columns being returned, and the number of columns in the type, and they match.Here is my type code:

CREATE OR REPLACE TYPE  TSA_CUSTOM.Lost_Plan as object (
   LP_Key number,  -- The member key of the plan that is going away
   LP_Type varchar2(20),
   LP_Dept varchar2(12),
   LP_SubDept varchar2(12),
   LP_Class varchar2(12),
   LP_VendorName varchar2(50)
[code]...

View 2 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

find the Test Case below.

--Creation of Table

create table tb1
(ID number(4),
event varchar2(20),
vdate date);

--Inserting Values into the Table.

INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')

[Code]...

--Selecting data from Table.

SELECT * FROM TB1;

ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10

10 rows selected.

how can i display the data as below format using Oracle 9i SQL.

IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10

View 4 Replies View Related







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