3 Tips for more readable Web!

2010/09/09Tips and TricsFirefox Addons, ReadabilityMartin Mareš

On the Internet you can find really a lot of articles, blogs, and various texts. If you’re an avid reader like me :), then you will just throw a couple of my tricks, how to read a little to diversify and improve.

[Česká verze článku – 3 Tipy pro čitelnější web!]

Tip 1 – Gutenberg would be happy

Imagine that you read the article in your browser, you will see only the headlines read, balanced text into paragraphs, pretty good in black and white as they browse the book… It’s a story? If you now go up on a page (or portal), jumping on my ads, the text on the page is used in several typefaces, different colors and sizes (I know a bit exaggerating :), does seem like the site, or not? You’re right, it looks like today’s Web. Yet very simple solution is bookmarklet Readability. “Install” to the bookmarklet in a similar manner as outlined in the following figure:

Now we will show how to use this bookmarklet. For example, the website server ihned.cz. Choose any article and see how the site looks like a standard browser and to retrieve after click to Redability bookmarklet. I think the results are evident at first glance. Thus you know how to use this bookmarklet. Is not it great to have again after a long time, an article in such form as he read directly from the book?

Tip 2 – Synchronize and read your way to work

As an avid reader, I studied how to fill the empty space of time when I was on the way to work. In my case it is two hours. I prefer this place is filled by reading (and it also makes). But you do not want to read only books, but I want to consume information from the Internet, but a nicer form than in the browser on my mobile phone. Today’s mobile browsers can handle it as their “fathers” at the desktop, so there again we have a problem with the confused heap of ballast, which the Articles do not want. The only salvation for me was the installation of add-on Read It Later (if you want to use the full power of Read It Later and synchronize your reading the article with more facilities such as iPad, iPhone, HTC, BlackBerry, on Windows Mobile, Andriod … and others, it is necessary to register on the site Read It Later List, registration is free. What benefits you will describe further in the article).

Main functions for Read It Later

Store links to articles (pages) in the queue. By reading the delay later. Links in the queue function like bookmarks, but here it’s really the importance of the queue! Just an article you like, but you do not have time for it now, you would read it on the way home from work, such as a tram or bus. Putting him in a queue and to read it, so he removes from the queue. Simple and practical. Queue should not only accumulate, should be also empty. Evacuated to the fact that you read the buffered articles.
Allows for offline reading – if you set the addon (choice Settings -> Offline -> mark “Automatically make all saved pages avilable offline” and “Download web view“), Then, after adding a reference to the queue will download the page to the browser cache. In the event that you are connected to the Internet, so you can rest in the offline view articles you have queued for reading.
References stored in the queue to a server and allows you to synchronize to other devices (and so I’m doing exactly the way to work – currently on the HTC Touch Pro 2 – screenshots are below)

How it “runs” in the browser

After installation, add to that in the browser, in the place where you enter the address (URL) icon appears, you can use to add just open page in a queue for later reading. How does my queue:

Reading articles from the Read It Later QUEUE in the browser

Next set of pictures shows how to use add-on Read It Later in practice. I think this will please every reader, you can see just the right format and also nicely readable text on the entire site. Well is not this better than using “normal” AdBlock Filter? In addition, if you look closely at the upper right corner (yes this cogwheel in the third picture), click on it appears to choose the font size for example, or choice black-on-the-white or white-on-the-black (day/night). Really very handy.

After click on "T" letter from the queue

Reading articles on the HTC Touch Pro2 device

On the pages Read It Later you will find links to applications for other devices (or other browsers) that can log in to your account Read It Later and download the offline version of the pages. Then you can easily read these pages on a train, metro or bus journey to work. (reading while driving in a car I do not recommend :) And here are some pictures of the HTC Touch Pro2 device, such as may appear offline reading articles:

Instapaper it’s a little different

On a similar, maybe better for someone, working Instapaper. In principle, then you just have any browser toolbar at the top two icons bookmarks – one bookmarklet to ensure the imposition of a queue link and a second link to the site Instapaper. For some it may be the key that is on these pages do not need to register if you want to use the option “Keep everything in cookies“. If you want to synchronize with other devices, you need to register for free. Instapaper I stopped, at least show you how to look my queue references to “read later“:

Tip 3 – Ad removed

The last and least practical, as it is totally the simplest way to eliminate the possibility of annoying ads articles. This was certainly one of those advertisements does not want to pay, but the reader now so many rolls of pages of advertising ballast, it’s really to bad. The choice of how to deal with the ad seems to me to remain on the reader.

Options to remove the ads are few and are dependent on the choice of browsers. Firefox is doing for removing ads probably best in this case only briefly refer to the well-known add-ons AdBlock Plus and FlashBlock. Both are in the popularity ranking glutton ads in the first place. Similar additions can be found today and certainly for your browser Google Chrome. But what if you use multiple browsers or want ad filter for virtually all HTTP connections (in Microsoft Windows OS). In this case, it is much better to use the program Ad Muncher from the Australian programmers. The program acts as an HTTP proxy and filters all HTTP traffic to the network. The program is not free, but the $ 29.95 Australian dollars isn’t much, license is tied to one computer, but I got it almost 5 years and at that price I have updated all the time for free. The program works by filtering the total content delivered to site before it gets to draw in the browser. Then tighten the page directly scans and modifies the resulting HTML code (including JavaScript). This could well devour all ads, or even all sorts of other bad behavior of some pages (pop-ups, etc.).

Examples of removing ads from inhed.cz portal:

Home - portal ihned.cz Adblock turned on

Home - portal ihned.cz - enabled Ad Muncher — Home - portal ihned.cz - enabled, Ad Muncher

The results show that the use AdBlock Plus in Firefox is just as good as using the program Ad Muncher, however, lose the advantage of filtering all HTTP traffic (advertising can be hidden really anywhere). I hope you will enjoy these tips.

Sources:

Scott Hanselman – Two Must-Have Tools for a More Readable Web.
Ad Muncher
Read It Later

How to select multiple rows from single line using a query over DUAL table

2010/08/30Oracle (SQL, PL/SQL)CONNECT BY, DUAL, Oracle, PL/SQL, SQLMartin Mareš

Simple solution to split a string into multiple rows in Oracle is a combination of a query over the “table” DUAL and using clause CONNECT BY. All have demonstrated a simple example.

[Česká verze článku – Rozdělení řetězce do více řádků pomocí dotazu nad tabulkou DUAL]

Idea that there is the following string:

Anna,Martin,Sebastian,Gabriela,Pavel,

We need to split this string so that the result was a list of names, each name on one line, like this:

Anna
Martin
Sebastian
Gabriela
Pavel

How to achieve this (in simple terms) shows the following SQL query:

SELECT substr( 'Anna,Martin,Sebastian,Gabriela,Pavel,',
         ( case when rownum = 1
           then 1
           else instr( 'Anna,Martin,Sebastian,Gabriela,Pavel,', ',', 1, rownum - 1 ) + 1 end ),

         instr( substr( 'Anna,Martin,Sebastian,Gabriela,Pavel,',
         		( case when rownum = 1
                  then 1
                  else instr( 'Anna,Martin,Sebastian,Gabriela,Pavel,', ',', 1, rownum - 1 ) + 1 end )
       		), ',' ) - 1

       ) as products
FROM dual
CONNECT BY LEVEL <= length( 'Anna,Martin,Sebastian,Gabriela,Pavel,' )
                  - length ( replace('Anna,Martin,Sebastian,Gabriela,Pavel,', ',') ) ;

How to processing XML with multiple nodes (using XMLSequence in Oracle)

2010/08/26Oracle (SQL, PL/SQL)BULK COLLECT, EXTRACT, Oracle, PL/SQL, XML, XMLSEQUENCEMartin Mareš

XML, which contains multiple nodes is necessary in the Oracle database parse with function XMLSequence. I will show you the application in three versions. First, with no XML namespace, then with the defined XML namespace and eventually use directly in the SQL expression. What is XML, or XML namespace, see W3Schools.

[Česká verze článku – Jak na zpracování XML s vícenásobnými uzly (XMLSequence v Oracle)]

Example without XML namespace

The assumption is the following document, where “multiple-node” is a tag ipAddress – occurs with the other in a level several times. Therefore, it is an array of IP addresses with items value and mask

<?xml version="1.0" encoding="UTF-8"?>
<ipAddressList>
	<ipAddress>
		<value>10.10.0.1</value>
		<mask>255.0.0.0</mask>
	</ipAddress>
	<ipAddress>
		<value>192.168.1.1</value>
		<mask>255.255.255.0</mask>
	</ipAddress>
</ipAddressList>

The next demo is a simple data type RECORD, in which we store values value and mask with the entry in the field using the BULK COLLECT clause.

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

And there is mentioned a script that demonstrates the use XMLSequence.

DECLARE
    l_xml_source      XMLType;

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

    TYPE type_ip_address IS TABLE OF rec_ip_address INDEX BY BINARY_INTEGER;
    l_ip_address_list type_ip_address;

BEGIN
    l_xml_source := XMLType('<ipAddressList>
<ipAddress>
  <value>10.10.0.1</value>
  <mask>255.0.0.0</mask>
</ipAddress>
<ipAddress>
  <value>192.168.1.1</value>
  <mask>255.255.255.0</mask>
</ipAddress>
</ipAddressList>');

    SELECT EXTRACTVALUE(VALUE(xml_list), '//value') AS ip_address_from
          ,EXTRACTVALUE(VALUE(xml_list), '//mask') AS ip_address_to
      BULK COLLECT
      INTO l_ip_address_list
      FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml_source, 'ipAddressList/ipAddress'))) xml_list;

    IF (l_ip_address_list.COUNT > 0) THEN

        FOR i IN l_ip_address_list.FIRST..l_ip_address_list.LAST LOOP
            DBMS_OUTPUT.put_line('[' || i || '] = ' || l_ip_address_list(i).ip_address_value || '/' || l_ip_address_list(i).ip_address_mask);
        END LOOP;

    END IF;

END;
/

The output of the script is the following list of IP addresses:

SQL> DBMS Output (Session: [1] ADMIN@TEST_DB.WORLD at: 25.08.2010 23:30:33):
SQL> ----------------------------------------------------------------------
SQL> [1] = 10.10.0.1/255.0.0.0
SQL> [2] = 192.168.1.1/255.255.255.0

Trick in this case is the use of the FROM TABLE (XMLSEQUENCE (…)). XMLSEQUENCE function returns the VARRAY, and above it is already possible to use command TABLE(…).

Example of use with defined XML namespace

In case define namespace such example:

xmlns:ip="http:/martin-mares.com/sampleIpAddressList.xsd"

Modify the test XML like this:

<?xml version="1.0" encoding="UTF-8"?>
<ip:ipAddressList xmlns:ip="http:/martin-mares.com/sampleIpAddressList.xsd">
	<ip:ipAddress>
		<ip:value>10.10.0.1</ip:value>
		<ip:mask>255.0.0.0</ip:mask>
	</ip:ipAddress>
	<ip:ipAddress>
		<ip:value>192.168.1.1</ip:value>
		<ip:mask>255.255.255.0</ip:mask>
	</ip:ipAddress>
</ip:ipAddressList>

And we have A final revised script that can work with the namespace:

DECLARE
    l_xml_source      XMLType;
    l_namespace       VARCHAR2(2000) := 'xmlns:ip="http:/martin-mares.cz/sampleIpAddressList.xsd"';

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

    TYPE type_ip_address IS TABLE OF rec_ip_address INDEX BY BINARY_INTEGER;
    l_ip_address_list type_ip_address;

BEGIN
    l_xml_source := XMLType('<ip:ipAddressList ' || l_namespace || '>
<ip:ipAddress>
  <ip:value>10.10.0.1</ip:value>
  <ip:mask>255.0.0.0</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.1</ip:value>
  <ip:mask>255.255.255.0</ip:mask>
</ip:ipAddress>
</ip:ipAddressList>');

    SELECT EXTRACTVALUE(VALUE(xml_list), '//ip:value', l_namespace) AS ip_address_from
          ,EXTRACTVALUE(VALUE(xml_list), '//ip:mask', l_namespace) AS ip_address_to
      BULK COLLECT
      INTO l_ip_address_list
      FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml_source, 'ip:ipAddressList/ip:ipAddress', l_namespace))) xml_list;

    IF (l_ip_address_list.COUNT > 0) THEN

        FOR i IN l_ip_address_list.FIRST..l_ip_address_list.LAST LOOP
            DBMS_OUTPUT.put_line('[' || i || '] = ' || l_ip_address_list(i).ip_address_value || '/' || l_ip_address_list(i).ip_address_mask);
        END LOOP;

    END IF;

END;
/

The output is again a list of IP addresses:

SQL> DBMS Output (Session: [1] ADMIN@ZIS_VT.WORLD at: 25.08.2010 23:48:34):
SQL> ----------------------------------------------------------------------
SQL> [1] = 10.10.0.1/255.0.0.0
SQL> [2] = 192.168.1.1/255.255.255.0

In the above script, please pay attention to EXTRACTVALUE. It’s very important to use ip:value instead of value. Otherwise Oracle can return exception:

RA-31013: Invalid XPATH expression

Example with clean SQL expression

Now only you combine what we’ve used in previous examples:

    SELECT EXTRACTVALUE(VALUE(xml_list), '//value') AS ip_address
          ,EXTRACTVALUE(VALUE(xml_list), '//mask') AS mask
      FROM TABLE(XMLSEQUENCE(EXTRACT(XMLType('<ipAddressList>
                                                <ipAddress>
                                                  <value>10.10.0.1</value>
                                                  <mask>255.0.0.0</mask>
                                                </ipAddress>
                                                <ipAddress>
                                                  <value>192.168.1.1</value>
                                                  <mask>255.255.255.0</mask>
                                                </ipAddress>
                                                </ipAddressList>'), 'ipAddressList/ipAddress'))) xml_list;

The result is a list of IP addresses:

SQL> IP_ADDRESS      MASK
SQL> --------------- ---------------
SQL> 10.10.0.1       255.0.0.0
SQL> 192.168.1.1     255.255.255.0

UPDATE 10/19/2010: Add next chapter demostrate how to Update XML nodes.

How to XML node update

This example shows how to change the data in XMLType before the process XMLSequence function. For nodes that contain a mask value of 255.255.255.0 changes to 255.255.255.128 using SQL function UpdateXML and the correct XPath expression:

DECLARE
    l_xml_source      XMLType;
    l_namespace       VARCHAR2(2000) := 'xmlns:ip="http:/martin-mares.cz/sampleIpAddressList.xsd"';
    l_default_mask    VARCHAR2(2000) := '255.255.255.0';
    l_new_mask        VARCHAR2(2000) := '255.255.255.128';

    TYPE rec_ip_address IS RECORD (
        ip_address_value VARCHAR2(15),
        ip_address_mask  VARCHAR2(15)
    );

    TYPE type_ip_address IS TABLE OF rec_ip_address INDEX BY BINARY_INTEGER;
    l_ip_address_list type_ip_address;

BEGIN
    l_xml_source := XMLType('<ip:ipAddressList ' || l_namespace || '>
<ip:ipAddress>
  <ip:value>10.10.0.1</ip:value>
  <ip:mask>255.0.0.0</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.1</ip:value>
  <ip:mask>' || l_default_mask || '</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.2</ip:value>
  <ip:mask>' || l_default_mask || '</ip:mask>
</ip:ipAddress>
<ip:ipAddress>
  <ip:value>192.168.1.3</ip:value>
  <ip:mask>' || l_default_mask || '</ip:mask>
</ip:ipAddress>
</ip:ipAddressList>');

    SELECT UPDATEXML(l_xml_source, '/ip:ipAddressList/ip:ipAddress[ip:mask = ''' || l_default_mask || ''']/ip:mask/text()', l_new_mask, l_namespace)
    INTO l_xml_source
    FROM dual;

    SELECT EXTRACTVALUE(VALUE(xml_list), '//ip:value', l_namespace) AS ip_address_from
          ,EXTRACTVALUE(VALUE(xml_list), '//ip:mask', l_namespace) AS ip_address_to
      BULK COLLECT
      INTO l_ip_address_list
      FROM TABLE(XMLSEQUENCE(EXTRACT(l_xml_source, 'ip:ipAddressList/ip:ipAddress', l_namespace))) xml_list;

    IF (l_ip_address_list.COUNT > 0) THEN

        FOR i IN l_ip_address_list.FIRST..l_ip_address_list.LAST LOOP
            DBMS_OUTPUT.put_line('[' || i || '] = ' || l_ip_address_list(i).ip_address_value || '/' || l_ip_address_list(i).ip_address_mask);
        END LOOP;

    END IF;

END;
/

The modified result is:

[1] = 10.10.0.1/255.0.0.0
[2] = 192.168.1.1/255.255.255.128
[3] = 192.168.1.2/255.255.255.128
[4] = 192.168.1.3/255.255.255.128

Little Big Twitter success :)

2010/08/25Flash newsASP.NET, ASP.NET MVC, Microsoft (C#, MVCMartin Mareš

Scott Hanselman yesterday responded to my Tweet, wants little to celebrate :) The best way that I mention who is Scott Hanselman and refer you to other sources of interesting. Who is Scott Hanselman? He now works in Microsoft’s position “Principal Program Manager Lead in Server and Tools Online”. It is known Blogger and Speaker. It is a great communicator and promoter of the ASP.NET, now mainly ASP.NET MVC.

[Česká verze článku – Malý Velký Twitter úspěch :)]

On his blog you will find articles related to Visual Studio, C#, MVC and programming in general. Scott is also very interested in social networks, for example, see his videos on the popular Channel9. In Czech, it can find such books as author ASP.NET 3.5 v jazycích C# a Visual Basic of Computer Press publishing.

More information about Scott, click here:

… and said tweet :)

“Indians” learn to install Oracle on Linux

2010/08/24Flash newsEcuador, Linux, Oracle, RedHatMartin Mareš

In Ecuador, site (EOUG) decided to do something for beginners who want to learn to work with Oracle databases. After thirty days, will publish articles on installation and commissioning of the Oracle server on Redhat Linux 5.1 32-bit. Articles are in English with many screenshots. I think that for someone it can be very beneficial. Would something like this was even here. On the czech site Oracle User Group (COUG) you can find the latest news from 1.1.2007 – a great pity.

[Česká verze článku – “Indiáni” učí instalovat Oracle na Linuxu]

The first two articles, “a series of Ecuador” can be found at the following addresses:

Sources:

Twitter – @paolapullas
Czech Oracle User Group –http://www.coug.cz

In the battle Oracle vs. OpenSolaris will probably be the winner Linux

2010/08/24Flash newsLinux, OpenSolaris, OracleMartin Mareš

During the day watching the debate on Twitter (channel #oracle) for end of support for the development of the OpenSolaris project. It seems to always be something bad into something good. In this case, it plays into the hands of Linux, which would probably acquire more clients. Begin to explore the links on howto “how to migrate from Linux to OpenSolaris”, one such is Migration Kit for Solaris OS to Linux (via @infolinux). I’m incline in this case Linux! :)

[Česká verze článku – V bitvě Oracle vs. OpenSolaris bude pravděpodobně vítězem Linux]

Calling Web Services in PL/SQL using UTL_HTTP package

2010/08/22Oracle (SQL, PL/SQL)Mock service, Oracle, PL/SQL, SOAP, SoapUI, UTL_HTTP, UTL_RAW, WSDLMartin Mareš

Today we will show how to access data from WebServices directly in PL/SQL, as I promised in the previous article.

[Česká verze článku – Volání Webové služby (Webservice) v PL/SQL pomocí UTL_HTTP]

For this purpose you need:

Tool for development/testing web services. Applying package SoapUI
SQL*Plus environment. For our tests will fully comply.

Creating a WebService (for testing)

Create a test web service described by the following WSDL document.

<?xml version="1.0" encoding="UTF-8"?>
<definitions name="HelloService"
   targetNamespace="http://www.ecerami.com/wsdl/HelloService.wsdl"
   xmlns="http://schemas.xmlsoap.org/wsdl/"
   xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
   xmlns:tns="http://www.ecerami.com/wsdl/HelloService.wsdl"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema">

   <message name="SayHelloRequest">
      <part name="firstName" type="xsd:string"/>
   </message>
   <message name="SayHelloResponse">
      <part name="greeting" type="xsd:string"/>
   </message>

   <portType name="Hello_PortType">
      <operation name="sayHello">
         <input message="tns:SayHelloRequest"/>
         <output message="tns:SayHelloResponse"/>
      </operation>
   </portType>

   <binding name="Hello_Binding" type="tns:Hello_PortType">
      <soap:binding style="rpc"
         transport="http://schemas.xmlsoap.org/soap/http"/>
      <operation name="sayHello">
         <soap:operation soapAction="sayHello"/>
         <input>
            <soap:body
               encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
               namespace="urn:examples:helloservice"
               use="encoded"/>
         </input>
         <output>
            <soap:body
               encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
               namespace="urn:examples:helloservice"
               use="encoded"/>
         </output>
      </operation>
   </binding>

   <service name="Hello_Service">
      <documentation>WSDL File for HelloService</documentation>
      <port binding="tns:Hello_Binding" name="Hello_Port">
         <soap:address
            location="http://localhost:8088/mockHello_Binding"/>
      </port>
   </service>
</definitions>

Install and run the program SoapUI. The new project will create over SoapUI menu item File -> New Project Soap UI.

Generate Mock Service – Figure shows that the WSDL document describes a service with one method sayHello.

Mock enter a name for service – I leave the default, which offers SoapUI.

Start testing the service (if you leave the default settings, the service is mapped to port 8088).

Open and display the WSDL document in a browser window.

Here is a browser window with the resulting WSDL document test that returns Web Service “HelloService”.

Run the query method sayHello. You should see the following.

Edit (27.9.2012): Tip for Mockup Response.
This simple modification can facilitate Response Mockup testing:

Content query to the server (SOAP Request):

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:examples:helloservice">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:sayHello soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <firstName xsi:type="xsd:string">Martin Mareš</firstName>
      </urn:sayHello>
   </soapenv:Body>
</soapenv:Envelope>

Content server response (SOAP Response):

<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:examples:helloservice">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:sayHelloResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <greeting xsi:type="xsd:string">gero et</greeting>
      </urn:sayHelloResponse>
   </soapenv:Body>
</soapenv:Envelope>

Consuming WebService using the PL/SQL package UTL_HTTP

We let our service Mock test run in a window SoapUI and we get into the PL/SQL code, SQL*Plus. A prerequisite for the proper functioning of the following PL/SQL code is that you get from your development/testing of the Oracle database on a computer where you are running a test web services on port 8088.

When you run the code consuming a Web service may experience the following error:

ORA-29273: HTTP request failed
ORA-06512: on "SYS.UTL_HTTP", line 1130
ORA-24247: access control list (ACL) did not allow access to the network
ORA-06512: on line 22

If that happens, you need to set permissions on the connect call and resolve (name resolve over DNS) the Oracle database. We provide the following script (in my case, triggered by the user SCOTT). In the script, please note principal line (which should include the name of the user for which permissions you assign), the item privilege (privilege name “connect” and “resolve“) and a guest entry (you can fill in either the Hostname or IP address of the computer is running Web service)

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'Test ACL',
                                    principal   => 'SCOTT',
                                    is_grant    => true,
                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'SCOTT',
                                       is_grant  => true,
                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => 'MY-NOTEBOOK-HOSTNAME');
END;
/
COMMIT
/

If you set permissions correctly above, it should become operational next script demonstrating the Request/Response test our WebService. (The script, pay attention to variables l_host_name and l_port, values should match those you set)

DECLARE
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_buffer_size    NUMBER(10) := 512;
    l_line_size      NUMBER(10) := 50;
    l_lines_count    NUMBER(10) := 20;
    l_string_request VARCHAR2(512);
    l_line           VARCHAR2(128);
    l_substring_msg  VARCHAR2(512);
    l_raw_data       RAW(512);
    l_clob_response  CLOB;
    l_host_name      VARCHAR2(128) := 'MY-NOTEBOOK-HOSTNAME';
    l_port           VARCHAR2(128) := '8088';

BEGIN
    l_string_request := '<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:examples:helloservice">
   <soapenv:Header/>
   <soapenv:Body>
      <urn:sayHello soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
         <firstName xsi:type="xsd:string">Martin</firstName>
      </urn:sayHello>
   </soapenv:Body>
</soapenv:Envelope>';
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url => 'http://' || l_host_name || ':' || l_port || '/mockHello_Binding', method => 'POST', http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
    UTL_HTTP.set_header(l_http_request, 'Host', l_host_name || ':' || l_port);
    UTL_HTTP.set_header(l_http_request, 'Connection', 'close');
    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset=UTF-8');
    UTL_HTTP.set_header(l_http_request, 'SOAPAction', '"sayHello"');
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_string_request));

    <<request_loop>>
    FOR i IN 0..CEIL(LENGTH(l_string_request) / l_buffer_size) - 1 LOOP
        l_substring_msg := SUBSTR(l_string_request, i * l_buffer_size + 1, l_buffer_size);

        BEGIN
            l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
            UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT request_loop;
        END;
    END LOOP request_loop;

    l_http_response := UTL_HTTP.get_response(l_http_request);
    DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
    DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
    DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');

    BEGIN

        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
            l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
        END LOOP response_loop;

        EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
    DBMS_OUTPUT.put_line(CHR(10) || '=== Print first ' || l_lines_count || ' lines of HTTP response... ===' || CHR(10) || CHR(10));

    <<print_response>>
    FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) - 1 LOOP
        l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
        DBMS_OUTPUT.put_line('[' || LPAD(i, 2, '0') || ']: ' || l_line);
        EXIT WHEN i > l_lines_count - 1;
    END LOOP print_response;

    IF l_http_request.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_request);
    END IF;

    IF l_http_response.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_response);
    END IF;

END;
/

The output of the script is as follows:

Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "483"

=== Print first 20 lines of HTTP response... ===

[00]: <soapenv:Envelope xmlns:xsi="http://www.w3.org/200
[01]: 1/XMLSchema-instance" xmlns:xsd="http://www.w3.org
[02]: /2001/XMLSchema" xmlns:soapenv="http://schemas.xml
[03]: soap.org/soap/envelope/" xmlns:urn="urn:examples:h
[04]: elloservice">
   <soapenv:Header/>
   <soapenv:B
[05]: ody>
      <urn:sayHelloResponse soapenv:encoding
[06]: Style="http://schemas.xmlsoap.org/soap/encoding/">
[07]:
         <greeting xsi:type="xsd:string">gero et
[08]: </greeting>
      </urn:sayHelloResponse>
   </s
[09]: oapenv:Body>
</soapenv:Envelope>

Consuming a real WebService CDYNE Weather

In conclusion, we show the real consumption of WebService CDYNE Weather (from WSDL description), specifically a method call GetCityForecastByZIP. Calling the following script assumes settings (ACL) in an Oracle database (just as I mentioned above in the article):

BEGIN
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => 'ws.cdyne.com');
END;
/
COMMIT
/

And now the promise of a script:

DECLARE
    l_http_request    UTL_HTTP.req;
    l_http_response   UTL_HTTP.resp;
    l_buffer_size     NUMBER(10) := 512;
    l_line_size       NUMBER(10) := 50;
    l_lines_count     NUMBER(10) := 20;
    l_string_request  VARCHAR2(512);
    l_line            VARCHAR2(128);
    l_substring_msg   VARCHAR2(512);
    l_raw_data        RAW(512);
    l_clob_response   CLOB;
    l_host_name       VARCHAR2(128) := 'ws.cdyne.com';
    l_port            VARCHAR2(128) := '80';
    l_zip             VARCHAR2(128) := '94065'; -- ZIP for Oracle corporation (Redwood City)
    l_resp_xml        XMLType;
    l_result_XML_node VARCHAR2(128);
    l_NAMESPACE_SOAP  VARCHAR2(128) := 'xmlns="http://www.w3.org/2003/05/soap-envelope"';
    l_response_city   VARCHAR2(128);
    l_response_date   VARCHAR2(128);
    l_response_temp   VARCHAR2(128);

BEGIN
    l_string_request := '<?xml version="1.0" encoding="utf-8"?>
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <GetCityForecastByZIP xmlns="http://ws.cdyne.com/WeatherWS/">
      <ZIP>' || l_zip || '</ZIP>
    </GetCityForecastByZIP>
  </soap12:Body>
</soap12:Envelope>';
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url => 'http://' || l_host_name || ':' || l_port || '/WeatherWS/Weather.asmx', method => 'POST', http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
    UTL_HTTP.set_header(l_http_request, 'Connection', 'close');
    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/soap+xml; charset=utf-8');
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_string_request));

    <<request_loop>>
    FOR i IN 0..CEIL(LENGTH(l_string_request) / l_buffer_size) - 1 LOOP
        l_substring_msg := SUBSTR(l_string_request, i * l_buffer_size + 1, l_buffer_size);

        BEGIN
            l_raw_data := utl_raw.cast_to_raw(l_substring_msg);
            UTL_HTTP.write_raw(r => l_http_request, data => l_raw_data);
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT request_loop;
        END;
    END LOOP request_loop;

    l_http_response := UTL_HTTP.get_response(l_http_request);
    DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
    DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
    DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');

    BEGIN

        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
            l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
        END LOOP response_loop;

        EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
    DBMS_OUTPUT.put_line(CHR(10) || '=== Print result ===' || CHR(10) || CHR(10));

    IF(l_http_response.status_code = 200) THEN
        -- Create XML type from response text
        l_resp_xml := XMLType.createXML(l_clob_response);
        -- Clean SOAP header
        SELECT EXTRACT(l_resp_xml, 'Envelope/Body/node()', l_NAMESPACE_SOAP) INTO l_resp_xml FROM dual;
        -- Extract City
        l_result_XML_node := 'GetCityForecastByZIPResponse/GetCityForecastByZIPResult/';
        SELECT EXTRACTVALUE(l_resp_xml, l_result_XML_node || 'City[1]', 'xmlns="http://ws.cdyne.com/WeatherWS/"') INTO l_response_city FROM dual;
        SELECT EXTRACTVALUE(l_resp_xml, l_result_XML_node || 'ForecastResult[1]/Forecast[1]/Date[1]', 'xmlns="http://ws.cdyne.com/WeatherWS/"') INTO l_response_date FROM dual;
        SELECT EXTRACTVALUE(l_resp_xml, l_result_XML_node || 'ForecastResult[1]/Forecast[1]/Temperatures[1]/DaytimeHigh[1]', 'xmlns="http://ws.cdyne.com/WeatherWS/"') INTO l_response_temp FROM dual;
    END IF;

    DBMS_OUTPUT.put_line ( 'Result> l_response_city=' || l_response_city);
    DBMS_OUTPUT.put_line ( 'Result> l_response_date=' || l_response_date);
    DBMS_OUTPUT.put_line ( 'Result> l_response_temp=' || l_response_temp);

    IF l_http_request.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_request);
    END IF;

    IF l_http_response.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_response);
    END IF;

END;
/

The output of the script is as follows:

Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "2673"

=== Result... ===

Result> l_response_city=Redwood City
Result> l_response_date=2010-08-17T00:00:00
Result> l_response_temp=73

Packages UTL_HTTP, UTL_RAW and their use in HTTP GET method

2010/08/20Oracle (SQL, PL/SQL)Oracle, PL/SQL, UTL_HTTP, UTL_RAWMartin Mareš

The Oracle database is possible with the package UTL_HTTP UTL_RAW and send/receive queries from a Web server. On a small example demonstrates how to use these packages. (The examples use the package UTL_RAW on the ground that some Web sites, perhaps in combination with the version of Oracle database, Oracle generates EXCEPTION if you use a function READ_TEXT, sometimes generate a coredump).

[Česká verze článku – Balíky UTL_HTTP, UTL_RAW a jejich využití pro metodu HTTP GET]

Easy to use data tightening (demonstrated by the GET query to the server martin-mares.com)

DECLARE
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_buffer_size    NUMBER(10) := 512;
    l_line_size      NUMBER(10) := 70;
    l_lines_count    NUMBER(10) := 10;
    l_string_request VARCHAR2(512);
    l_line           VARCHAR2(128);
    l_raw_data       RAW(512);
    l_clob_response  CLOB;

BEGIN
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url => 'https://martin-mares.com', method => 'GET', http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(r => l_http_request, name => 'User-Agent', value => 'Mozilla/4.0');
    UTL_HTTP.set_header(l_http_request, 'Host', 'martin-mares.com');
    l_http_response := UTL_HTTP.get_response(l_http_request);
    DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
    DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
    DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');

    BEGIN

        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
            l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
        END LOOP response_loop;

        EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
    DBMS_OUTPUT.put_line(CHR(10) || '=== Print first ' || l_lines_count || ' lines of HTTP response... ===' || CHR(10) || CHR(10));

    <<print_response>>
    FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) - 1 LOOP
        l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
        DBMS_OUTPUT.put_line('[' || LPAD(i, 2, '0') || ']: ' || SUBSTR(TRIM(l_line),1,50) || '...');
        EXIT WHEN i > l_lines_count - 1;
    END LOOP print_response;

    IF l_http_request.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_request);
    END IF;

    IF l_http_response.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_response);
    END IF;

END;
/

The output of this call (when “set serveroutput on” in SQL*Plus) will:

DBMS Output (Session: [1] SCOTT@TEST_DB.WORLD at: 20.08.2010 00:43:57):
----------------------------------------------------------------------------
Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "28003"

=== Print first 10 lines of HTTP response... ===

Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "28003"

=== Print first 10 lines of HTTP response... ===

[00]: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Trans...
[01]: ://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt...
[02]: http://www.w3.org/1999/xhtml" dir="ltr" lang="cs-C...
[03]: head profile="http://gmpg.org/xfn/11">
	<title>Ma...
[04]: itle>
	<meta http-equiv="content-type" content="t...
[05]: F-8" />
	<link rel="stylesheet" type="text/css" h...
[06]: com/wp-content/themes/the-erudite/css/erudite.css"...
[07]: 6]> <link rel="stylesheet" href="http://martin-mares.co...
[08]: /the-erudite/css/ie6.css" type="text/css">
	<sty...
[09]: edia="screen">
		.hr {behavior: url(http://since7...
[10]: mes/the-erudite/library/iepngfix.htc); }
	</style...

Next time we’ll look at how to consume data via HTTP POST from a WebService Weather (CDYNE)

SQL*Plus and command history in Linux using rlwrap

2010/08/17Oracle (SQL, PL/SQL)Oracle, rlwrap, SQL*PlusMartin Mareš

In the SQL*Plus itself is not possible to browsing history commands (except the last command executed, he can re-create using the “r” + Enter). On Windows, this can be quite well made via a function key F7, as shown in the picture. Elegantly can scroll through the history commands performed.

[Česká verze článku – SQL*Plus a historie příkazů v Linuxu pomocí rlwrap]

SQL*Plus history in Windows command line

The Linux operating system can implement this by adding a wrapper for SQL*Plus as a program rlwrap (the program creates an intermediary between Bash and the environment of SQL*Plus).

UPDATE 20.08.2010 – If you are using Ubuntu, you can simply install rlwrap via “sudo aptitude install rlwrap“

Installation procedure is as follows:

1. First download the rlwrap program from the server freshmeat – actual version. Perhaps using wget (URL of the current version of the program may change over time. Version of that show this installation is 0.37 and the name of the downloaded file is “rlwrap-0.37.tar.gz”):

oracle@ubuntu-server$ wget http://freshmeat.net/urls/de7d8482e030110354012880805e76fd

2. Unpack the downloaded file

oracle@ubuntu-server:~$ tar zxvf rlwrap-0.37.tar.gz
rlwrap-0.37/
rlwrap-0.37/completions/
rlwrap-0.37/completions/testclient
rlwrap-0.37/completions/coqtop
rlwrap-0.37/doc/
rlwrap-0.37/doc/rlwrap.man.in
rlwrap-0.37/doc/Makefile.am
rlwrap-0.37/doc/Makefile.in
...

3. Go to the directory with the program

oracle@ubuntu-server:~$ cd rlwrap-0.37/

4. Compile and run (when compiling I used the option “-prefix= usr”. By default, the program installs in /usr/local/bin, in this case I prefer /usr/bin, so I used the option “- prefix=…”)

oracle@ubuntu-server:~/rlwrap-0.37$ ./configure --prefix=/usr && make
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... no
checking for mawk... mawk
...

If the configuration script fails:

configure: error:

You need the GNU readline library(ftp://ftp.gnu.org/gnu/readline/ ) to build
this program!

You must either (a) – download the readline library from the address ftp://ftp.gnu.org/gnu/readline/, unpack and compile it manually or (b) – as in my case, if you use Ubuntu (or similar Linux distribution), just install this library by using aptitude:

oracle@ubuntu-server:~/rlwrap-0.37$ sudo aptitude install libreadline6-dev

… and then start compiling rlwrap:

oracle@ubuntu-server:~/rlwrap-0.37$ ./configure --prefix=/usr && make

5. If the compilation completes without errors, you just install the program.

oracle@ubuntu-server:~/rlwrap-0.37$ sudo make install
Making install in doc
make[1]: Entering directory `/home/oracle/rlwrap-0.37/doc'
make[2]: Entering directory `/home/oracle/rlwrap-0.37/doc'
make[2]: Nothing to be done for `install-exec-am'.
test -z "/usr/share/man/man1" || /bin/mkdir -p "/usr/share/man/man1"
 /usr/bin/install -c -m 644 rlwrap.1 '/usr/share/man/man1'
make[2]: Leaving directory `/home/oracle/rlwrap-0.37/doc'
make[1]: Leaving directory `/home/oracle/rlwrap-0.37/doc'
Making install in src
make[1]: Entering directory `/home/oracle/rlwrap-0.37/src'
make[2]: Entering directory `/home/oracle/rlwrap-0.37/src'
test -z "/usr/bin" || /bin/mkdir -p "/usr/bin"
  /usr/bin/install -c rlwrap '/usr/bin'
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/home/oracle/rlwrap-0.37/src'
make[1]: Leaving directory `/home/oracle/rlwrap-0.37/src'
Making install in filters
make[1]: Entering directory `/home/oracle/rlwrap-0.37/filters'
make[2]: Entering directory `/home/oracle/rlwrap-0.37/filters'
make[2]: Nothing to be done for `install-exec-am'.
test -z "/usr/share/man/man3" || /bin/mkdir -p "/usr/share/man/man3"
 /usr/bin/install -c -m 644 RlwrapFilter.3pm '/usr/share/man/man3'
make[2]: Leaving directory `/home/oracle/rlwrap-0.37/filters'
make[1]: Leaving directory `/home/oracle/rlwrap-0.37/filters'
make[1]: Entering directory `/home/oracle/rlwrap-0.37'
make[2]: Entering directory `/home/oracle/rlwrap-0.37'
make[2]: Nothing to be done for `install-exec-am'.
test -z "/usr/share/rlwrap" || /bin/mkdir -p "/usr/share/rlwrap"
/bin/mkdir -p '/usr/share/rlwrap/filters'
 /usr/bin/install -c -m 644  filters/README filters/RlwrapFilter.pm filters/RlwrapFilter.3pm filters/count_in_prompt filters/pipeto filters/logger filters/null filters/unbackspace filters/pipeline filters/ftp_filter filters/history_format filters/simple_macro filters/template filters/scrub_prompt filters/paint_prompt filters/censor_passwords filters/listing '/usr/share/rlwrap/filters'
/bin/mkdir -p '/usr/share/rlwrap/completions'
 /usr/bin/install -c -m 644  completions/testclient completions/coqtop '/usr/share/rlwrap/completions'
make  install-data-hook
make[3]: Entering directory `/home/oracle/rlwrap-0.37'
chmod a+x /usr/share/rlwrap/filters/*
make[3]: Leaving directory `/home/oracle/rlwrap-0.37'
make[2]: Leaving directory `/home/oracle/rlwrap-0.37'
make[1]: Leaving directory `/home/oracle/rlwrap-0.37'

6. Remember to set an alias for the program sqlplus

oracle@ubuntu-server:~/rlwrap-0.37$ vi ~/.bashrc

… and add a row:

alias sqlplus='/usr/bin/rlwrap sqlplus'

… as shown in the picture:

After re-logon account under which you were logged in compiling (and for whom do you show the SQL*Plus), you should be able to use the arrows “up /down” scroll through the history of performed commands run from within SQL*Plus.

PIPELINED FUNCTION – how to get data dynamically

2010/08/16Oracle (SQL, PL/SQL)Oracle, PIPELINED, PL/SQL, SQLMartin Mareš

The resulting SQL query in Oracle database is up to dynamically influence/generate a so called pipelined FUNCTION. Their use, we will see the following example:

[Česká verze článku – PIPELINED FUNCTION aneb jak na data dynamicky]

Create new data types PersonType and PersonTypeSet

Within these data types, we store our data.

CREATE TYPE PersonType AS OBJECT
(
  id number,
  first_name varchar2(2000),
  last_name varchar2(2000),
  description varchar2(2000)
)
/

CREATE TYPE PersonTypeSet AS TABLE OF PersonType
/

Create a pipelined FUNCTION

CREATE OR REPLACE FUNCTION GET_PERSONS RETURN PersonTypeSet
PIPELINED
IS
    l_one_row PersonType := PersonType(NULL, NULL, NULL, NULL);

BEGIN

    FOR i IN 1..10 LOOP
        l_one_row.id := i;
        l_one_row.first_name := 'Johnny (' || i || ')';
        l_one_row.last_name := 'English';
        l_one_row.description := 'British Super Agent';
        PIPE ROW(l_one_row);
    END LOOP;

    RETURN;
END GET_PERSONS;
/

Looking at the data (SQL query over the pipelined function!)

SQL> SELECT * FROM TABLE(GET_PERSONS());

UPDATE 14.09.2010 – If Oracle exception occured “ORA-22905: cannot access rows from a non-nested table item” it’s necessary to CAST table object like this: “SELECT * FROM TABLE(CAST(GET_PERSONS() AS PersonTypeSet));”

Martin Mareš : blog

Senior Oracle Developer (SQL, PL/SQL), IT enthusiast, Geocaching fan – from the Czech Republic