Development

Developing an Application with PL/SQL, the Formspider Way

Gerger Consulting - Thu, 2015-11-12 03:05
On November 24, attend our webinar to watch Professional Consultant Nicholas Mwaura as he talks about his experience with Formspider and shows you how you can build first class applications to impress your prospective customers and end users.

Watch a short demo of the application Nicholas will show you how to build:



During the webinar the following topics will be discussed:
  • The importance of Formspider for PL/SQL developers and Oracle customers
  • Why Oracle Forms applications are very easy to migrate to Formspider
  • Introduction to the demo application
     
  • Building the demo application: How to design the UI, how to work with windows, panels, tabs, trees and various other Formspider components, working with LOV’s, application structure in the database. 
At the end of the webinar, the attendees will get priority access to the source code of the demo application.

During the webinar, each attendee will receive a discount offer for Formspider developer licenses! :-)
 
Sign up to the webinar now!
Categories: Development

SQL Needs a Sister (Broken Link Corrected)

Gerger Consulting - Wed, 2015-11-04 01:28
I've just published an article on Medium about the missing sister of SQL. I think there is a fundamental mistake we’ve been making in using SQL. We use it both to ask a question and format the answer and I think this is just wrong. You can read the article at this link. (Sorry for the broken link in the previous post and a big thank you to the person who sent us a comment about the issue. :-) )
Categories: Development

Goodbye Old Bike, Hello Old Bike

FeuerThoughts - Sat, 2015-10-31 09:53
In 1977, one of my best buddies told me about a friend he had, who had just custom-built a bicycle for him, from components. This guy would do the same thing for me. "How cool is that?" thought I.

A few weeks later, for (according to my admittedly leaky memory) about $200, I received a bicycle that became one of my all-time favorite machines.

It was built around a black Raleigh Competition Reynolds 531 double butted steel tubing. It was powered by a lovely, engraved, all-metal Shimano 600 Arabesque derailleur ("One of the most decorative groups Shimano ever released"):


The bike was a beautiful ride and all-around excellent transportation. It came to Chicago with me in 1981. It took me downtown for my first job as a programmer in Chicago, at First National Bank (I commuted by bike whenever possible). It accompanied me on day trips all over Chicagoland. It took me back and forth to the CISPES office, a cause and organization that consumed way too much of my life for too little in return. Whatever foolishness I chose to engage in, this bike helped me do it in style (my style: it was scratched up and usually dirty, but the chain and gears were clean and lubricated).

But for some reason (yes, that's right, I do not recall), about fifteen years ago, I decided to buy a new bicycle, and chose a Jamis Coda. I hung the black bike (I don't know what else to call it. It used to be a Raleigh Competition, but that was long before I took possession) in the back of my shed, and very much enjoyed the Coda. It was new, it was shiny, and it was a hybrid, better suited for city riding. After a while it wasn't new or shiny anymore - just my style, but it was still a great ride. I didn't think very often about my black bike, even when I noticed it hanging behind garden gear and abandoned kids' toys.

Well, a couple months ago, I got careless with my Coda and it was stolen. Arrrrggggh.

I figured I would buy a new bicycle, which I dreaded in part because everything has gotten so over-built, optimized for convenience, unnecessarily fancy, etc. Not my style. Started looking around and found myself at Bucephalus Bikes, where they do a brisk, proud business in reconditioning older bicycles to better-than-new.

That got me thinking: maybe I should bring in the Reynolds 531 frame (by this time, I was thinking that was all that was left of the bike) and see what it might cost to make it a beauty, once again.

I hauled it out of the shed and was delighted to see it was intact as a bicycle, but with understandably flat tires, rusty chain, and who knows what else wrong with it. I did, after all, choose to stop riding it long ago.

Feeling slightly guilty at not returning to Bucephalus, I brought the black bike to my neighborhood bike shop, Roberts Cycle. They made appreciative noises. They probably do that with all their customers. :-)

Well, in any case, a few weeks went by and yesterday I rode the bike home - smoooothly. Here are the parts Roberts needed to properly restore the bike:
  • Chain
  • Front and rear brake cables and housing
  • Front and rear shift cables
  • Seat binder bolt
  • Schwalbe 27-1/4 K-guard (Kevlar) tires
  • Sealed bottom bracket
  • Headset bearings (29, loose, had to be individually packed in)
  • Rear wheel bearings (22, loose, had to be individually packed in)
  • Front alloy wheel
  • Used rear derailleur
Yes, you read that right. They removed the Shimano 600 and gave me a "good enough" replacement for a very fair price. He never asked if I wanted to replace it. WTF? The guy who runs the shop explained that the 600 was actually not compatible with the rear gear assembly on the bicycle; I couldn't get to all the gears using it. So he swapped it out.

Gee, I'd never really noticed any problem. I rarely needed the very low settings. I started to feel aggrieved and righteous about the whole thing, but he so sincerely seemed to consider it an act of necessity. So in the end I accepted with grace (or so I told myself) the Shimano 600 (and every other part, including the old ball bearings - !!) in a plastic bag.

I tried not to be too sad. It really is pretty.

No, I won't tell you how much all those parts (and the accompanying, thoroughgoing overhaul) cost me. I suppose I could've gotten a new bicycle for the amount, but nothing as elegant or satisfying as my black bike. 

And as I said in an earlier post, "New is BadBuying things new is the way to consume the most resources and have the worst impact on our world. So I am going to make every effort to avoid buying new things and instead by used."

Hurray! I acted on my principles. I have lots of principles and I never feel like I act enough in accordance with them. I also am not sure how many I've forgotten.

So this act of reuse and redemption felt - and feels - really good. 

Now if only it would stop raining so I could go out for a ride!

Categories: Development

EM12c Agent generating heapDump_*.hprof

Arun Bavera - Tue, 2015-10-27 14:43
By default Em12c Agent has enabled -XX:+HeapDumpOnOutOfMemoryError. We need to change the log directory to other directory or disable the Dump before it fills up Agent partition.
  --  -XX:-HeapDumpOnOutOfMemoryError   or  -XX:HeapDumpPath=/home/oracle/logs/
 
You can put these entries in AGENT_INST/sysman/config/s_jvm_options.opt
Agent has an auto tuning feature which increases Xmx automatically whenever it goes out of memory and dumps the memory.
We  disabled the Dump by putting this entry   -XX:-HeapDumpOnOutOfMemoryError     in file  AGENT_INST/sysman/config/s_jvm_options.opt and did emctl stop agent;emctl start agent.
 
Also, if you think if the agent memory auto tuning is not working you can disable that .. if you see a frequent restart or failing to start due to outOfMemory errors.
You can change  in emd.properties
#Enable auto tuning out of the box
enableAutoTuning=true false

Change the default Xmx
# These are the optional Java flags for the agent
#
agentJavaDefines=-Xmx512M -XX:MaxPermSize=96M
Note: This is not recommended by Oracle Dev team.








Categories: Development

Responsive UI in Oracle ADF 12.2.1 with the MatchMediaBehavior Tag

Shay Shmeltzer - Tue, 2015-10-27 08:42

Another very powerful addition to the responsive capabilities of ADF Faces in version 12.2.1 is the new af:matchMediaBehavior tag. It allows you to control almost every bit of your UI and change properties in response to changes in the viewport size.

Check out what it can do in this video:

Note that in the mediaQuery you can use other type of CSS media queries

The code for the page in the demo is

       <af:panelSplitter id="ps1" orientation="horizontal" splitterPosition="201">
                <f:facet name="first">
                    <af:panelFormLayout id="pfl1" labelAlignment="start">
                        <af:matchMediaBehavior matchedPropertyValue="top" propertyName="labelAlignment"
                                               mediaQuery="screen and (max-width: 768px)"/>
                        <af:inputText label="Label 1" id="it1"/>
                        <af:inputText label="Label 2" id="it2"/>
                        <f:facet name="footer"/>
                    </af:panelFormLayout>
                </f:facet>
                <f:facet name="second">
                    <af:panelFormLayout id="pfl2" labelAlignment="start" rows="2">
                        <af:matchMediaBehavior matchedPropertyValue="top" propertyName="labelAlignment"
                                               mediaQuery="screen and (max-width: 900px)"/>
                        <af:matchMediaBehavior matchedPropertyValue="4" propertyName="rows"
                                               mediaQuery="screen and (max-width: 800px)"/>
                        <af:inputText label="Label 1" id="it3"/>
                        <af:inputText label="Label 2" id="it4"/>
                        <f:facet name="footer"/>
                        <af:inputListOfValues label="Label 1" popupTitle="Search and Result Dialog" id="ilov1"/>
                        <af:inputFile label="Label 1" id="if1"/>
                    </af:panelFormLayout>
                </f:facet>
                <af:matchMediaBehavior matchedPropertyValue="vertical" propertyName="orientation"
                                       mediaQuery="screen and (max-width: 768px)"/>
                <af:matchMediaBehavior matchedPropertyValue="100" propertyName="splitterPosition"
                                       mediaQuery="screen and (max-width: 768px)"/>
            </af:panelSplitter>
     
Categories: Development

Introducing the Masonry Layout in Oracle ADF 12.2.1

Shay Shmeltzer - Fri, 2015-10-23 14:34

One of the focus areas for Oracle ADF 12.2.1 was simplifying the creation of responsive UIs, and one of the new components that supports this is the af:masonryLayout component. It allows you to define areas on your page that act as tiles and automatically re-arrange themselves to match the browser's window size.

This layout is very useful for dashboard type of pages where you are showing all sorts of information. 

In the video below I show you the very basic way to use this out of the box.

As mentioned in the video there are built in styleClasses in ADF for the size of masonry tiles.

They are in the format of AFMasonryTileSize1x1 with support for 1x2,1x3,2x1,2x2,3x1,3x2

The Masonry Layout can contain any component you want (not just group layout as shown in the page), as long as you give that component the right AFMasonryTileSize style. 

You can further control the sizing and coloring of the tiles using styleClasses that you define in a skin file.

For example in the video I used the following style to make sure you can actually see the panelGroupLayout tiles on the page. 

.tileStyle{
background-color:gray;
border: black;
border-width: 2px; 
} 

The actual code in the JSF page is:

<af:masonryLayout id="ml1">
  <af:panelGroupLayout id="pgl1" styleClass="AFMasonryTileSize2x1 tileStyle"/>
  <af:panelGroupLayout id="pgl2" styleClass="AFMasonryTileSize2x1 tileStyle"/>
  <af:panelGroupLayout id="pgl3" styleClass="AFMasonryTileSize1x1 tileStyle"/>
  <af:panelGroupLayout id="pgl4" styleClass="AFMasonryTileSize3x1 tileStyle"/>
</af:masonryLayout>

Categories: Development

OOW and JavaOne 2015 - Where I'll Be

Shay Shmeltzer - Thu, 2015-10-22 10:41

It's that time of the year again, and next week is going to be crazy busy for us at Oracle OpenWorld and at JavaOne.

In case you want to catch me, here is a list of the locations where I'll be:

Building iOS Apps with Java 8 [CON1588]

Monday, Oct 26, 12:30 p.m. | Hilton—Continental Ballroom 4

Build Responsive Web Applications with Oracle ADF [HOL10380]

Monday, Oct 26, 5:00 p.m. | Hotel Nikko—Nikko Ballroom III (3rd Floor)

General Session: Revolutionizing Application Development with Oracle Cloud [GEN9500]

 Tuesday, Oct 27, 11:00 a.m. | Moscone South—103 

Meet the Experts: Oracle’s Development Tools and Frameworks [MTE10022]

Tuesday, Oct 27, 6:15 p.m. | Moscone South—300

Oracle Application Development Framework and Oracle JDeveloper—What’s New and How to Use It [CON8333]

Wednesday, Oct 28, 1:45 p.m. | Moscone South—304

Building iOS Apps with Java 8 [CON1588]

Thursday, Oct 29, 9:00 a.m. | Hilton—Continental Ballroom 1/2/3

Moving Oracle ADF to the Cloud—Development and Deployment in the New Age [CON8332]

Thursday, Oct 29, 1:15 p.m. | Moscone South—270

In between you could also catch me at the Oracle demo ground in Moscone south (mostly in the Oracle Application Builder Cloud Service), and at the JavaOne GeekBar on Monday.

At the rest of the time, I'll try and catch some of the sessions on this list.

See you there...

Categories: Development

Select all Checkboxes in IR - APEX 5

Denes Kubicek - Thu, 2015-10-22 06:54
If <input type="checkbox" onclick="$f_CheckFirstColumn(this)"> doesn't work for you in APEX 5 Interactive Reports then you will need to disable Fixed Headings (standard). Fixed headings splits the report into two tables and the function won't work.

Categories: Development

Oracle Live SQL

Denes Kubicek - Thu, 2015-10-22 06:45
Oracle Live SQL is a great source for learning SQL and PL/SQL. It has a lot of good examples and interactive educational tutorials. The best thing is, it runs on APEX ;) Add this site to your bookmarks.

Categories: Development

Oracle APEX 5.0.2 now available

Patrick Wolf - Wed, 2015-10-21 02:51
Oracle Application Express 5.0.2 is now released and available for download. If you wish to download the full release of Oracle Application Express 5.0.2, you can get it from the Downloads page on OTN. If you have Oracle APEX 5.0.0 … Continue reading
Categories: Development

Thanks to all #odevchoice voters!

XTended Oracle SQL - Fri, 2015-10-16 17:11

And special thanks to all the great people who voted for me! :)
I want to list all voters for all nomenees in one page: http://orasql.org/odevchoice/all-the-voters.html
The query (using xt_http of course :):

Spoiler

with 
  finalists(category, userid, name) as (
      --                               SQL~ Voting:
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'SQL'     , 6899,  'Stew Ashton      ' from dual union all
      select 'SQL'     , 6900,  'Sean Stuber      ' from dual union all
      select 'SQL'     , 6901,  'Sayan Malakshinov' from dual union all
      select 'SQL'     , 6902,  'Matthias Rogel   ' from dual union all
      select 'SQL'     , 6903,  'Kim Berg Hansen  ' from dual union all
      select 'SQL'     , 6904,  'Justin Cave      ' from dual union all
      select 'SQL'     , 6905,  'Erik Van Roon    ' from dual union all
      select 'SQL'     , 6906,  'Emrah Mete       ' from dual union all
      --                               PL/SQL~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'PL/SQL'  , 6907,  'Sean Stuber      ' from dual union all
      select 'PL/SQL'  , 6908,  'Roger Troller    ' from dual union all
      select 'PL/SQL'  , 6909,  'Patrick Barel    ' from dual union all
      select 'PL/SQL'  , 6910,  'Morten Braten    ' from dual union all
      select 'PL/SQL'  , 6911,  'Kim Berg Hansen  ' from dual union all
      select 'PL/SQL'  , 6912,  'Bill Coulam      ' from dual union all
      select 'PL/SQL'  , 6913,  'Adrian Billington' from dual union all
      --                               ORDS ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6881,  'Tim St. Hilaire  ' from dual union all
      select 'ORDS'    , 6882,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6883,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6884,  'Dimitri Gielis   ' from dual union all
      select 'ORDS'    , 6885,  'Dietmar Aust     ' from dual union all
      select 'ORDS'    , 6886,  'Anton Nielsen    ' from dual union all
      --                               APEX ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6887,  'Trent Schafer    ' from dual union all
      select 'ORDS'    , 6888,  'Paul MacMillan   ' from dual union all
      select 'ORDS'    , 6889,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6890,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6891,  'Karen Cannell    ' from dual union all
      select 'ORDS'    , 6893,  'Juergen Schuster ' from dual union all
      select 'ORDS'    , 6894,  'Jari Laine       ' from dual union all
      --                             DatabaseDesign ~ Voting
      ------ ----------------- -------------------------------------------------------------------------------------
      select 'DbDesign', 6896,  'Michelle Kolbe   ' from dual union all
      select 'DbDesign', 6897,  'Mark Hoxey       ' from dual union all
      select 'DbDesign', 6898,  'Heli Helskyaho   ' from dual union all
      select 'DbDesign', 6919,  'Rob Lockard      ' from dual
)
select 
       f.category
      ,f.name         as Nominee
      ,row_number()over(partition by f.category,f.name 
                        order by 
                        case 
                           when regexp_like(t.column_value,'^user\d+$')                      then 2
                           when regexp_like(t.column_value,'^\d+$')                          then 3
                           when regexp_like(t.column_value,'\w{6}-\w{4}-\w{4}-\w{4}-\w{12}') then 4
                           else 1
                        end
                        ) n
      ,t.column_value as VoterName 
from finalists f,
     table(
         xt_http.get_matches(
            pUrl     => 'https://community.oracle.com/voting-history.jspa?ideaID='||to_char(f.userid,'fm0000')||'&start=0&numResults=1000'
           ,pPattern => 'alt="([^"]+)"'
           ,pGroup   => 1
         )
    ) t
order by 1,2,3,4

[collapse]

Categories: Development

Opening of APEX R&D New Zealand

Dimitri Gielis - Thu, 2015-10-15 02:20
We're really excited to announce that next to our Belgium office, we have opened an office in New Zealand too. This will allow us to cover multiple timezones and better serve the Australian and New Zealand market. You can read more about it on Lino's blog

 APEX R&D

Contact our team of experienced professionals for innovative, reliable and cost effective Oracle Application Express solutions. 

Regarding any custom application development, training/coaching or consulting related to Oracle APEX technology we would love to hear from you.

For more information please contact our Belgium or New Zealand office.

We look forward working with you.

Categories: Development

Like a white stone in the depths of a well...

Greg Pavlik - Tue, 2015-10-13 13:26
Like a white stone in the depths of a well...
Anna Akhmatova
working translation by Greg Pavlik

Like a white stone in the depths of a well
within me there lies one memory.
I can not - and do not - want to expel
this, my greatest joy and my agony.

I think that anyone who closely looks
can see this recollection reads
as harrowing sadness in a tragic book -
a warning, and a sign of need.

I know the gods forever strive
to wreck the body but cannot touch the mind;
assurance that you will forever live
as a memory I can’t leave behind.

Original:
Как белый камень в глубине колодца,
Лежит во мне одно воспоминанье.
Я не могу и не хочу бороться:
Оно - веселье и оно - страданье.

Мне кажется, что тот, кто близко взглянет
В мои глаза, его увидит сразу.
Печальней и задумчивее станет
Внимающего скорбному рассказу.

Я ведаю, что боги превращали
Людей в предметы, не убив сознанья,
Чтоб вечно жили дивные печали.
Ты превращен в мое воспоминанье.

Submit your abstract for KScope 16 - only 3 days left

Dimitri Gielis - Mon, 2015-10-12 08:01
Only 3 days left to submit your abstract for KScope 16... so don't wait longer, submit now! :)


My first ODTUG conference was in 2006 and it was awesome. On this blog you'll find different blog posts about my experience and why I love it, so I won't go over that... but from time to time I get some questions about being a presenter at the event.

"What should I submit, I don't know what to talk about?"

The topics in the Oracle Application Express track are following:
  • Integration: Relates to any work where other products are integrated with APEX or a gap is bridged between platforms. Perhaps is integration with EBS, SAP, Raspberry Pi, Node.js, OAuth, REST, etc...
  • New Release: Presentations that relate to the latest version of APEX. Typically the Oracle development team is presenting those topics.
  • Real World: Everybody loves real life examples. This is anything where a real life system is presented. The problem is explained and how APEX was used to solve it.
  • Detail Plunge: These are presentations on any topic, but the topic, even if it seems narrow, is explored in detail and with focus. It doesn't have to be complicated. I could be building plugins or Dynamic Actions.
  • Other Application Express: Everything else goes here. This is the catch-all bucket.
You can submit up to 4 abstracts.

The way I chose my topics typically fall in one of following categories:



"Why even bother, I won't be selected"

Well, without submitting you don't know. What do you have to lose? I'm sure I don't speak for myself when I say, I would love to meet new people and hear about what they do and discover. There's not a single day I don't learn anything, tech or non-tech. This weekend I learned something new and the feature has been in APEX for many years! (but that's for another blob post)
The level of presentation is very broad as well. We need presentations of all levels; beginners, intermediate and advanced.


"I don't know how to write an abstract"

On the internet there're many links how to present and write an abstract. On the ODTUG site you'll find some tips and examples.


Give it a go, you'll feel good when you've done it. Here's the link. It's all about the learning experience!

All the best and I look forward meeting you in Chicago.
Categories: Development

Requiem Fragment

Greg Pavlik - Sun, 2015-10-11 16:59
Requiem Fragment
Anna Akhmatova
translation by Greg Pavlik

10 Crucifixion

      Do not lament me, O Mother,
      seeing me in the tomb.


The angelic chorus glorified the hour of eternity,
when the heavens convulsed in a river of fire:
He cried to His Father “Eli, Eli, lama sabachthani?”
And to His Mother, he spoke: “Do not lament me”…

1938

Mary Magdalena writhed, and wept
as the beloved John froze like stone (or salt).
Where the Mother stood in silence -
no one dared to look.

1940

Translator notes. I chose to translate this poem much less loosely than my interpretative translation of Akhmatova’s Lot’s Wife – there Russian readers will recognize that the final stanza is essentially my own poem (with an explicit reference to the unrelated work of Scott Cairns, in fact).

Instead, this section of Requiem is much closer to the original (1), with only minimal augmentation. Nonetheless, it is also very different from Akhmatova’s in certain critical respects. Requiem itself is a difficult and evocative work – its melancholy is inseparable from the suffering of both Akhmatova herself and the Soviet people under Stalin. In some sense, I have abandoned this context in my translation. Educated Russian readers would have recognized Akhmatova’s work as using the imagery of the hymnography of the Paschal Nocturnes, the final liturgical setting of Great and Holy Saturday chanted before the entombed body of the dead Christ, which includes a deeply moving dialogue with his Mother. (2)

I have chosen to deepen the liturgical elements of the poem and play off themes that recur in and around the Lenten Triodion. The text of the Slavonic service is rendered here in English as it is commonly used in American parishes within the Russian Orthodox tradition. Second, I use the Hebrew directly in quotation from the Psalter, emphasizing its position as a liturgical prayer. While the dialogic element from the Nocturnes service is repeated, maintaining the liturgical connection, here the address to the Mother is clearly one of human filial affection.

The river of fire is evocative of the image God as “consuming fire”, which, St Isaac says is experienced as bliss by the pure in heart. The second stanza – and I do not believe there is any intention at all in the original to do this – also points back to the story of Lot and Sodom and implicitly re-invokes the image of fire. Akhmatova makes no association with salt. I have tried intentionally not to recall the poetics of Stabat Mater in the final lines.

(1) Original Russian:

10
РАСПЯТИЕ

   Не рыдай Мене, Мати,
   во гробе зрящия.

Хор ангелов великий час восславил,
И небеса расплавились в огне.
Отцу сказал: "Почто Меня оставил!"
А матери: "О, не рыдай Мене..."

1938

Магдалина билась и рыдала,
Ученик любимый каменел,
А туда, где молча Мать стояла,
Так никто взглянуть и не посмел.

1940, Фонтанный Дом

(2) From the Eastern Orthodox Liturgical Service of Paschal Nocturnes:

Do not lament me, O Mother, seeing me in the tomb, the Son conceived in the womb without seed, for I shall arise and be glorified with eternal glory as God. I shall exalt all who magnify thee in faith and in love.

Glory to Thee, our God, glory to Thee!

I escaped sufferings and was blessed beyond nature at Thy strange birth, O Son, who art without beginning. But now, beholding Thee, my God, dead and without breath, I am sorely pierced by the sword of sorrow. But arise, that I may be magnified.

Glory to Thee, our God, glory to Thee!

By my own will, the earth covers me, O Mother, but the gatekeepers of hell tremble at seeing me clothed in the blood-stained garments of vengeance; for when I have vanquished my enemies on the cross, I shall arise as God and magnify thee.

Glory to the Father and to the Son and to the Holy Spirit, now and ever and unto ages of ages. Amen.

Let creation rejoice, let all born on earth be glad, for hateful hell has been despoiled, let the women with myrrh come to meet me, for I am redeeming Adam and Eve and all their descendants, and on the third day shall I arise.

Oracle package for HTTPS/HTTP[version 0.2]

XTended Oracle SQL - Sun, 2015-10-11 16:30

A couple days ago i created simple package for HTTPS/HTTP, but I’ve decided now to improve it:

  1. Timeout parameter – it would be better to control connection time;
  2. Simple page parsing with PCRE regular expressions – to speed up and simplify page parsing, because if you want to get big number matched expressions from CLOB with regexp_xxx oracle functions, you have to call these functions many times with different [occurance] parameters, passing/accessing to the clob many times. But within java procedure it will be just one pass.
  3. Support of plsqldoc – the tool for automatically generating documentation in HTML format.(javadoc analogue)

Upd 2015-10-11:

  1. added HttpMethod parameter – so you can choose POST or GET method
  2. added function get_last_response – returns last HTTP response code.

You can download new version from github: https://github.com/xtender/xt_http
Also it may be interesting if you want to see how to get collection of CLOBs/varchar2 from JAVA stored procedure.

So with new functions I can even more easy get UpVoters list from prevous post:

select * 
from table(
         xt_http.get_matches(
            pUrl     => '&url'
           ,pPattern => 'alt="([^"]+)"'
           ,pGroup   => 1
         )
    ) t
Results
select * 
from table(
         xt_http.get_matches(
            pUrl     => '&url'
           ,pPattern => 'alt="([^"]+)"'
           ,pGroup   => 1
         )
    ) t
/
Enter value for url: https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000

NAME
--------------------------------------------------
Denes Kubicek
Pavel Luzanov
Martin Preiss
AlexAA
scherbak
TimHall
Toon Koppelaars
Dom Brooks
mweedman
BluShadow
Dmitry-Oracle
Mahir M. Quluzade
SA2
Dmitry A. Bogomolov
SQL*Plus
Alexander.Ryndin-Oracle
Mohamed Houri
Randolf Geist
ctrieb
UltraBlast
Kot Dmitriy
user9506228
Timur Akhmadeev
Franck Pachot
pudge
user12068799
user11933056
user11994768
iRAV
user12228999
nicher100
vva
Alexander Semenov
Dmitry_Nikiforov
Bud Light
user7111641
dbms_photoshop
AcidMan
achervov
GokhanAtil
user2616810
Harun Kucuksabanoglu
_Nikotin
Maki
user9066618
user10487079
IgorUsoltsev
edw_otn
Vigneswar Battu
user11198823
be here now
869219
user7543311
VladimirSitnikov
kamineff
Asmodeus
djeday84
Oleh Tyshchenko
87Rb-87Sr
911978
KoTTT
Konstantin
945154
953255
user12217223
Alexander_Anokhin-Oracle
oragraf.sql
Jack10154746
user9502569
Yury Pudovchenko
Sergey Navrotskiy
985277
xifos
MatthiasRogel
Mikhail Velikikh
user12134743
1025067
Oren Nakdimon
1051964
1094595
1209426
user11211533
user6115180
user3990689
d.nemolchev
user8925862
user11222376
user882251
user12279047
1284785
1323138
1336159
Grigory-OC
1373320
1373354
1411786
1421824
user13287062
1442254
1443436
1447180
KSDaemon
user12097700
Sergei Perminov
user3539222
Victor Osolovskiy
1560322
user13609377
user6672754
Alfredo Abate
user11315510
user12245839
1636030
user5399907
user3983717
user12276855
1744386
user9171605
ksAsmodeus
user12058508
1856219
user3214869
Trihin Pavel
1879578
1886567
user8869337
1890583
1913344
user11978061
user1438531
Menno Hoogendijk
2616420
2646629
rpc1
user4770257
user5217858
user11949728
al.netrusov
2693742
AKalugin
2721788
RZGiampaoli
2728073
2769955
2786798
2789541
BobDJ
2816036
user10123230
2831055
DBA_Hans_007
2880604
2896072
2902504
ApInvent
Pinto Das
natalka
2913706
2924025
2957186
2960288
-KE-
user5337688
3001391
3004351
oleksii.kondratenko
zeynep.samsa
Mehmet Kaplan
adbee6ae-dcb4-4c59-915b-487b33773a3f
6648e909-359d-4a32-b7bd-6cea3fd9fec3
mehmetyalcin
60e3f71b-bd5d-422c-b479-7a087d5b3827
3032511
c09042fd-15da-494b-b7b0-243ff4ceeb4b
5e087e18-4789-4923-92cb-cce149ba3072
5291adcf-242b-4f01-bbe6-b7ce44db1aac
9c0321bf-a358-49f9-88e4-462bd4fd5674
03b9266b-53f4-4503-ae04-b339c039bffd
user12044736
4a89f5a6-cc35-424e-8e3c-59964ad8d56b
54113fd4-6592-4a32-9920-663813bdd4c5
590d4b9f-32b6-43ad-b0c4-3b767407c055
ffb03087-d390-4068-98e5-4cfd73e66d00
3fa47a10-7b28-4857-9274-d175f3b7fd48
user5814569
d0b20163-7a58-4d4d-9a7d-01ec973bc3e1
8aaa1ed8-e0f6-4712-bef7-6b1e6579798f
1aee554c-832b-4fa5-bead-0680a53d1cc5
5daa756f-80aa-4260-b91d-10d2c51b78d6
e7897e6a-993d-46db-ad93-215b61b715ad
9f982eda-2b58-4d61-aad9-4c6a50d2dab7
user2503867
3032876
f3012cfb-62b5-4c86-a102-2172c3640d5b
68fe6d1e-d41c-4528-b076-ac3bc5289cc5
41232c56-5a29-442c-a1c6-d5b94477be1d
0682b6e1-8662-498c-8455-629032a25cea
user6592033
59961cb1-b4a6-470c-9802-44432911a7ff
user7345691
ab7980ce-71ba-4ec1-a578-6b716f2ae1ae
user5844404
723b639c-f6c6-4780-8ad6-0315564ef937
28e651f6-c9c3-4d2a-af03-001837eb99ba
3032942
841b96b4-ab84-461e-aed9-58f9df710406
user9961876
d7e48e5c-868f-4b2d-88b5-8614e9d35c80
3033022
f095cbb6-707a-4f40-8f18-a6a9dc37894d
3033091
9e9d3c99-9b5e-4fa2-89ac-4e6216209566
a9e702d3-f8e5-43ee-8e6c-0fe722d9ab50
bdd07d60-c467-4115-8149-8ef2af880d9a
d5571104-4726-4f06-b529-293dc667ae6e
user10865764
petrelevich
87a6503f-5717-4887-ac77-cd916002f53a
user7355088
user6083916
user2300447
user12299863
81ddc21a-7cb3-4298-a96b-ea7c9774b2c4
3405d13c-9d19-4903-8eb0-14a2544cb32b
user2427414
4670adeb-1c9d-4ce4-98eb-962bc4c68f5d
oldhook
1e4428aa-3a63-4a1a-90b3-f2b74292f502
user6367919
7c75e315-487d-4797-8e5b-f3dee58bbc79
user8828289
1a8ad4e7-759f-48ee-8054-c449540d0573
6f3b1262-c9d6-42d6-b703-fc4e6a40b7a1
037f209b-f643-4642-a059-79988d19d77b
PL99
3034106
3034166
user5489918
user2340616
naeel maqsudov
3036157
user2626322
52e8d732-4289-4d0d-b8c5-80e701f3c07d
4b2deedd-84c4-4b8b-8724-837c54dd764e
user12569643
07292d40-2bab-4e94-b68e-cfaae6c093a8
8ec394ac-fd54-4896-9810-0381bb75260e
caec3a42-0f98-440b-ad71-9522cb1e0a0e
9c52d45b-e2e4-489c-9a32-548c77f159b3
f6e966be-8576-4da9-a0df-8fec374b6cd3
5214be2e-d761-4a4e-aeba-23ff7bb4cf4e
f070b484-017d-4d4c-a740-f6ad9db37286
d4f322d3-0265-458d-948d-83bd66d5c7e3

[collapse]
Categories: Development

Oracle Database Developer Choice Awards (2nd)

Denes Kubicek - Fri, 2015-10-09 00:49
I was just looking at the voting results for ORDS and APEX. In the ORDS category Dietmar Aust is leading and in the category APEX it is Morten Braten. So far so good. These men deserve the award for sure - they did a lot for the community and the most of us are using (or have used) their solutions. Think of Morten's PL/SQL Utility Library or Dietmar's PDF Printing...

The only thing I have a problem with is the absolutely microscopic number of votes. The most votes in the category ORDS has Dietmar - 134. In APEX it is 98 votes for Morten. Even Jari Lane who is very well known in the APEX community and has helped numerous people with his solutions has received all together 103 votes - obviously almost the half of it as down-voitng. To my point of view this is incredible. But if I think back and do a comparison with my own experience then this is actually normal. In the last seven years I have received more than 9.000 emails from the community asking either for access to my workspace, my Demo Application or asking for an APEX solution of some kind. As a result of that I have created almost 4.000 accounts and answered 95% of those emails. But all together, maybe 50 receivers responded saying thank you.

Conclusion:

Shame on us. We are lazy and cynic. We are constantly talking about how big the APEX community and how strong it is. Maybe, because there are a few people out there like Dietmar, Jari, Jürgen Schuster, Morten and few others. The rest seem to be there only when they need something.

Categories: Development

Oracle Database Developer Choice Awards: Up-Voters list

XTended Oracle SQL - Thu, 2015-10-08 20:13

It’s very easy to get and analyze voters list using my new XT_HTTP package :)
We can get up-voters list by the URL:

https://community.oracle.com/voting-history.jspa?ideaID=NNNN&start=0&numResults=1000

where NNNN is Idea ID from nomenee’s page.
For example my page – https://community.oracle.com/ideas/6901 so my voters page will be https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000
BTW, though this page is called “VotingHistory”, but it shows up-voters only :)

-- create table upvoters as
with 
  finalists(category, userid, name) as (
      --                               SQL~ Voting:
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'SQL'     , 6899,  'Stew Ashton      ' from dual union all
      select 'SQL'     , 6900,  'Sean Stuber      ' from dual union all
      select 'SQL'     , 6901,  'Sayan Malakshinov' from dual union all
      select 'SQL'     , 6902,  'Matthias Rogel   ' from dual union all
      select 'SQL'     , 6903,  'Kim Berg Hansen  ' from dual union all
      select 'SQL'     , 6904,  'Justin Cave      ' from dual union all
      select 'SQL'     , 6905,  'Erik Van Roon    ' from dual union all
      select 'SQL'     , 6906,  'Emrah Mete       ' from dual union all
      --                               PL/SQL~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'PL/SQL'  , 6907,  'Sean Stuber      ' from dual union all
      select 'PL/SQL'  , 6908,  'Roger Troller    ' from dual union all
      select 'PL/SQL'  , 6909,  'Patrick Barel    ' from dual union all
      select 'PL/SQL'  , 6910,  'Morten Braten    ' from dual union all
      select 'PL/SQL'  , 6911,  'Kim Berg Hansen  ' from dual union all
      select 'PL/SQL'  , 6912,  'Bill Coulam      ' from dual union all
      select 'PL/SQL'  , 6913,  'Adrian Billington' from dual union all
      --                               ORDS ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6881,  'Tim St. Hilaire  ' from dual union all
      select 'ORDS'    , 6882,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6883,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6884,  'Dimitri Gielis   ' from dual union all
      select 'ORDS'    , 6885,  'Dietmar Aust     ' from dual union all
      select 'ORDS'    , 6886,  'Anton Nielsen    ' from dual union all
      --                               APEX ~ Voting
      ------ ------------------ -------------------------------------------------------------------------------------
      select 'ORDS'    , 6887,  'Trent Schafer    ' from dual union all
      select 'ORDS'    , 6888,  'Paul MacMillan   ' from dual union all
      select 'ORDS'    , 6889,  'Morten Braten    ' from dual union all
      select 'ORDS'    , 6890,  'Kiran Pawar      ' from dual union all
      select 'ORDS'    , 6891,  'Karen Cannell    ' from dual union all
      select 'ORDS'    , 6893,  'Juergen Schuster ' from dual union all
      select 'ORDS'    , 6894,  'Jari Laine       ' from dual union all
      --                             DatabaseDesign ~ Voting
      ------ ----------------- -------------------------------------------------------------------------------------
      select 'DbDesign', 6896,  'Michelle Kolbe   ' from dual union all
      select 'DbDesign', 6897,  'Mark Hoxey       ' from dual union all
      select 'DbDesign', 6898,  'Heli Helskyaho   ' from dual union all
      select 'DbDesign', 6919,  'Rob Lockard      ' from dual
)
,finalists_with_voters_pages as (
      select finalists.*
            ,xt_http.get_page('https://community.oracle.com/voting-history.jspa?ideaID='||to_char(userid,'fm0000')||'&start=0&numResults=1000') page
      from finalists
      where rownum>0
)
,finalists_with_voters as (
      select fp.category
            ,fp.userid
            ,fp.name
            ,voters.column_value as voter
      from finalists_with_voters_pages fp
          ,table(cast(multiset(
                      select regexp_substr(page,'alt="([^"]+)"',1,level,'c',1)
                      from dual
                      connect by level<=regexp_count(page,'<li>')
                      ) as ku$_vcnt)) voters
)
select *
from finalists_with_voters
Categories: Development

Very simple oracle package for HTTPS and HTTP

XTended Oracle SQL - Thu, 2015-10-08 19:54

I don’t like to import certificates, so i cannot use httpuritype for HTTPS pages and I decided to create package which will work with https as http.
It was pretty easy with java stored procedures :)
github/XT_HTTP

java source: xt_http.jsp
create or replace and compile java source named xt_http as
package org.orasql.xt_http;

import javax.net.ssl.HttpsURLConnection;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.HttpURLConnection;

import java.sql.Connection;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;
 

public class XT_HTTP {

   /**
    * Function getPage
    * @param String Page URL
    * @return String
    */
    public static CLOB getPage(java.lang.String sURL)
    throws java.sql.SQLException
     {
        OracleDriver driver = new OracleDriver();
        Connection conn     = driver.defaultConnection();
        CLOB result         = CLOB.createTemporary(conn, false, CLOB.DURATION_CALL);
        result.setString(1," ");
        try {
            URL url = new URL(sURL);
            HttpURLConnection con = (HttpURLConnection)url.openConnection();
            //HttpsURLConnection con = (HttpsURLConnection)url.openConnection();
            if(con!=null){
                BufferedReader br =
                        new BufferedReader(
                                new InputStreamReader(con.getInputStream()));
                StringBuilder sb = new StringBuilder();
                String line;
                while ((line = br.readLine()) != null){
                    sb.append(line);
                }
                br.close();
                result.setString(1,sb.toString());
            }
        } catch (MalformedURLException e) {
            result.setString(1, e.getMessage());
        } catch (IOException e) {
            result.setString(1, e.getMessage());
        }
        return result;
    }
    
    public static java.lang.String getString(java.lang.String sURL) {
        String result="";
        try {
            URL url = new URL(sURL);
            HttpURLConnection con = (HttpURLConnection)url.openConnection();
            if(con!=null){
                BufferedReader br =
                        new BufferedReader(
                                new InputStreamReader(con.getInputStream()));
                StringBuilder sb = new StringBuilder();
                String line;
                while ((line = br.readLine()) != null){
                    sb.append(line);
                }
                br.close();
                result = sb.toString().substring(0,3999);
            }
        } catch (MalformedURLException e) {
            return e.getMessage();
        } catch (IOException e) {
            return e.getMessage();
        }
        return result;
    }
}
/

[collapse]

package xt_http
create or replace package XT_HTTP is
/**
 * Get page as CLOB
 */
  function get_page(pURL varchar2)
    return clob
    IS LANGUAGE JAVA
    name 'org.orasql.xt_http.XT_HTTP.getPage(java.lang.String) return oracle.sql.CLOB';

/**
 * Get page as varchar2(max=4000 chars)
 */
  function get_string(pURL varchar2)
    return varchar2
    IS LANGUAGE JAVA
    name 'org.orasql.xt_http.XT_HTTP.getString(java.lang.String) return java.lang.String';
    
end XT_HTTP;
/

[collapse]

We have to grant connection permissions:

dbms_java.grant_permission(
   grantee           => 'XTENDER'                       -- username
 , permission_type   => 'SYS:java.net.SocketPermission' -- connection permission
 , permission_name   => 'ya.ru:443'                     -- connection address and port
 , permission_action => 'connect,resolve'               -- types
);

And now we can easily get any page:

USAGE example:
declare
  c clob;
  s varchar2(8000);
begin
  --- Through HTTPS as CLOB:
  c:=xt_http.get_page('https://google.com');

  --- Through HTTP as CLOB
  c:=xt_http.get_page('http://ya.ru');
  
  --- Through HTTPS as varchar2:
  s:=xt_http.get_string('https://google.com');

  --- Through HTTP as varchar2
  s:=xt_http.get_string('http://ya.ru');
end;
/
select length( xt_http.get_page('https://google.com') ) page_size from dual
Categories: Development

How to delete older emails from GMAIL

Arun Bavera - Wed, 2015-10-07 09:40

image

image

 

Other category:

category: social older_than:45d

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development