Development

v$blog #funny #friday

XTended Oracle SQL - Sat, 2021-04-24 18:22
select title, short_url 
from v$blog 
where pubDate>=systimestamp - interval '5' month;
with v$blog as (
    select  
        title
       ,to_timestamp_tz(pubDate,'DY, dd mon yyyy hh24:mi:ss TZR') pubDate
       ,short_url
       ,description
    from xmltable(
            '/rss/channel/item'
            passing
                xmltype(httpuritype('http://orasql.org/feed/').getclob())
            columns
                 title       varchar2(70)  path 'title'
                ,pubDate     varchar2(40)   path 'pubDate'
                ,url         varchar2(128)  path 'link'
                ,short_url   varchar2(50)   path 'guid'
                ,description varchar2(500)  path 'fn:substring(./description,1,500)'
    ) 
)
select title, short_url from v$blog where pubDate>=systimestamp - interval '5' month;
Categories: Development

Murder in the Age of Enlightenment

Greg Pavlik - Fri, 2021-04-09 17:51

I had a few days of downtime to deal with some medical issues and turned to some short story collections to fill the time. My companions for a bit were Ryūnosuke Akutagawa and Anton Chekhov. I was quite delighted with a new translation of Akutagawa from Pushkin Press, Murder in the Age of Enlightenment. What sparse but sharp imagery - taken from Japanese history, European literature, Mahayana Buddhism, Christianity, Chinese writings - it was a bit of a smorgasbord. Akutagawa can be dark: his preoccupation with suicide in his writing no doubt reflected in his own suicide at age 35; I found his piece Madonna in Black on a peculiarly evil Maria-Kannon to be troubling, not least because I have a kind of devotional fascination with Maria-Kannon as our Lady of Mercy. But still Akutagawa is deeply humanistic and wide-ranging. The Karetnyk translation can be digested in an afternoon, no doubt time well spent.

My Chekhov choice was the recent translation of fifty-two stories by the unsurpassable translator pair Richard Pevear and Larissa Volokhonsky. These two are artists in their own right... I can't say enough good things about their portfolio of translations. They are so good I've been forced to re-read a number of novels just to digest their interpretative readings over the years.

But back to Akutagawa. Here I post a translation done under Creative Commons license* of the story The Spider's Thread. I don't know if this is a re-telling of Dostoevsky's "Tale of the Onion" in Karamazov for sure, though the story line is so close that I find it impossible to believe otherwise: Lord Buddha Shakyamuni simply replacing the Guardian Angel. Get the Pushkin Press book to read it in a slightly more refined form, but I found this a wonderful read as well:


ONE


One day, the Buddha was strolling alone along the edge of a lotus pond in Paradise. The blooming lotus flowers in the pond were each pure white like jewels, and the place was filled with the indescribably wondrous fragrance continually emitted from each flower’s golden center. It was just morning in Paradise.

After a time, the Buddha paused at the edge of the pond and from between the lotus leaves that covered it saw a glimpse of the state of things below. Now this celestial pond just happened to lie directly over Hell, and peering through that crystal-clear water was like looking through a magnifying glass at the River of Death and the Mountain of Needles and such.

The Buddha saw there, in the depths of Hell, a single man writhing along with the other sinners. This man was named Kandata, and he had been a notorious thief who had performed murder and arson and other acts of evil. In his past, however, he had performed just one good deed: one day, when walking through the deep forest, he saw a spider crawling along the road. At first he raised his foot to crush it, but suddenly he changed his mind and stopped, saying, “No, small though it may be, a spider, too, has life. It would be a pity to meaninglessly end it,” and so did not kill it.

Looking down upon the captives in Hell the Buddha recalled this kind act that Kandata had performed, and thought to use his good deed as a way to save him from his fate. Looking aside, there on a jade-colored lotus leaf he saw a single spider, spinning out a web of silver thread. The Buddha carefully took the spider’s thread into his hand, and lowered it straight down between the jewel-like white lotuses into the depths of Hell.


TWO


Kandata was floating and sinking along with the other sinners in the Lake of Blood at the bottom of Hell. It was pitch black no matter which way he looked, and the occasional glimpse of light that he would see in the darkness would turn out to be just the glint of the terrible Mountain of Needles. How lonely he must have felt! All about him was the silence of the grave, the only occasional sound being a faint sigh from one of the damned. Those who were so evil as to be sent to this place were tired by its various torments, and left without even the strength to cry out. Even the great thief Kandata could only squirm like a dying frog as he choked in the Lake of Blood.

But one day, raising up his head and glancing at the sky above the lake, in the empty darkness Kandata saw a silver spider’s thread being lowered from the ceiling so far, far away. The thread seemed almost afraid to be seen, emitting a frail, constant light as it came down to just above Kandata’s head. Seeing this, Kandata couldn’t help but clap his hands in joy. If he were to cling to this thread and climb up it, he may be able to climb out of Hell! Perhaps he could even climb all the way to Paradise! Then he would never be chased up the Mountain of Needles, nor drowned in the Lake of Blood again.

Thinking so, he firmly grasped the spider’s thread with both hands and began to climb the thread, higher and higher. Having once been a great thief, he was used to tasks such as this. But the distance between Hell and Paradise is tens of thousands of miles, and so it would seem that no amount of effort would make this an easy journey. After climbing for some time Kandata tired, and couldn’t climb a bit higher. Having no other recourse, he hung there from the thread, resting, and while doing so looked down below.

He saw that he had made a good deal of progress. The Lake of Blood that he had been trapped in was now hidden in the dark below, and he had even climbed higher than the dimly glowing Mountain of Needles. If he could keep up this pace, perhaps he could escape from Hell after all. Kandata grasped the thread with both hands, and laughingly spoke in a voice that he hadn’t used in the many years since he had come here, “I’ve done it! I’ve done it!”

Looking down, however, what did he see but an endless queue of sinners, intently following him up the thread like a line of ants! Seeing this, surprise and fear kept Kandata hanging there for a time with mouth open and eyes blinking like a fool. How could this slender spider’s web, which should break even under just his weight, support the weight of all these other people? If the thread were to snap, all of his effort would be wasted and he would fall back into Hell with the others! That just would not do. But even as he thought these thoughts, hundreds more, thousands more of the damned came crawling up from the Lake of Blood, forming a line and scurrying up the thread. If he didn’t do something fast, surely the thread would snap in the middle and he would fall back down.

Kandata shouted out, “Hey! You sinners! This thread is mine! Who said you could climb up it? Get off! Get off!”

Though the thread had been fine until just then, with these words it snapped with a twang right where Kandata held it. Poor Kandata fell headfirst through the air, spinning like a top, right down through the darkness. The severed end of the silver thread hung there, suspended from heaven, shining with its pale light in that moonless, starless sky.


THREE


The Buddha stood in Paradise at the edge of the lotus pond, silently watching these events. After Kandata sank like a stone to the bottom of the Lake of Blood, he continued his stroll with a sad face. He must have been surprised that even after such severe punishment Kandata’s lack of compassion would lead him right back into Hell.

Yet the lotus blossoms in the lotus ponds of Paradise care nothing about such matters. Their jewel-like white flowers waved about the feet of the Buddha, and each flower’s golden center continuously filled the place with their indescribably wondrous fragrance. It was almost noon in Paradise.


(16 April 1918)

* Translation http://tonygonz.blogspot.com/2006/05/spiders-thread-akutagawa-ryunosuke.html

Silence in 4 Movements

Greg Pavlik - Wed, 2021-03-24 12:05

"What is the relation of [contemplation] to action? Simply this. He who attempts to act and do things for others or for the world without deepening his own self-understanding, freedom, integrity and capacity to love will not have anything to give others. He will communicate to them nothing but the contagion of his own obsessions, his aggressiveness, his ego-centered ambitions, his delusions about ends and means, his doctrinaire prejudices and ideas."
—Thomas Merton


"Those who know do not talk.

Those who talk do not know.

~

Stop talking,

block off your senses,

meditate in silence,

release your worries,

blunt your sharpness,

untie your knots,

soften your glare,

harmonise your inner light

and unite the world into one whole!

This is the primal union or secret embrace."

Tao Te Ching 56

"Make stillness your criterion for testing the value of everything, and choose always what contributes to it."

-Evagrius Ponticus




"Silence and Beauty - Eco" (Minerals and gesso on canvas, 2016) by contemporary Japanese American artist Fujimura Makoto (藤村真, born in Boston, Massachusetts in 1960). Abstract expressionist piece done with nihonga techniques. Picture found online.

I read Fujimura's book Silence and Beauty last year, which was inspired by the Endo Shusaku's 20th century novel Silence (itself adapted to film by the great Martin Scorsese). Fujimura reflects on his relationship with Japanese culture in the light of Shusaku's work, the Hiroshima bombing, and his own experience as a Japanese-American: most importantly how it has manifested in his work as an artist. Shusaku's work itself dwells on "silence" as absence. But I think this painting shows absence-as-presence: something is there, something beautiful, but its not clear what or even why - in fact that presence changes over time for the viewer, depending on vantage point or even focus.

Version Control for Oracle Webinar Video

Gerger Consulting - Wed, 2021-02-24 05:34

 

On February 16th, we hosted a webinar about Gitora 5. Many thanks to everyone who attended. The recording of the webinar is below.

There were a few questions we received several times during the webinar. We'd like to reiterate our answers below:

How does Gitora work with GitLab, GitHub or similar services...?
Gitora repos work with these services just like any other local Git repo does. From the point of GitLab, GitHub or similar services, Gitora repos are just local repos that you can push to these services.

How can I create a merge/pull request with Gitora?
Merge/Pull request is not a Git feature. It is a GitHub/GitLab feature. You can continue creating merge/pull requests on these services using the same workflow you use for any other local repo. To create a merge/pull request for a Gitora repo, first push your repo to GitHub/GitLab using the Gitora web app (or use Git directly). Next, go to your GitHub/GitLab account and create your merge/pull request.

How can I work on two different projects with different deadlines in the same development database and send only code related to one project to the test database?
You can't. Gitora cannot do magic. :-) There will never be a tool that will help you to do this because it is impossible. What Gitora empowers you to do is to have multiple databases per project. Then you can work on the same logical objects in different databases and use Gitora to merge code bases.
You can watch a full workflow and detailed explanation of this process at this link.

Categories: Development

Source Control Management for the Oracle Database

Gerger Consulting - Thu, 2021-02-11 03:28

Managing the source code of objects in the Oracle database and moving them between databases has been a problem for many years.

DBA's and database developers did not enjoy the benefits of prominent source control software such as Git and have been largely left behind.

Gitora is a unique tool that bridges the Oracle database and Git and enables DBA's and database developers to benefit from Git's capabilities in managing database objects.

On February 16th we are hosting a webinar to show you how you can use Gitora to manage your database objects.

In this webinar we'll cover the following topics:

- How to use Git for database object DDL statements
- How to use Gitora to move code between databases
- How to implement modern development workflows using Gitora
- How to use Pluggable Databases for a more productive development environment

You can sign up at this link.

Categories: Development

Gitora 5 Source Control for Oracle is Out

Gerger Consulting - Wed, 2021-01-27 05:14

We are very excited to announce that Gitora 5 is now available for download.

Gitora 5 is a huge step forward for version management of database objects.

Gitora 5 has many new features. Below are just six of our favorites:

  • A Brand New User Interface and Application
  • One Gitora Installation to Manage Any Number of Databases
  • Gitora Now Manages Tables
  • No-login development experience
  • A Comprehensive Developer Privileges Management Module
  • Full API To Completely Automate Your Workflow

Let’s go over these new features briefly:

A Brand New User Interface and Application

We redesigned every screen in Gitora from the ground up. The new design gives Gitora 5 a beautiful modern look with large components, easy to read, fresh colors and updated icons. 

We also rebuilt the application with Java. This reduced Gitora’s database footprint by more than 95% making it much easier to install and manage.

One Gitora Installation to Manage Any Number of Databases

This was probably one of the most requested features. Previously, if you wanted to manage a database with Gitora, you needed a new Gitora installation. This is no longer the case.

Thanks to Gitora 5’s new architecture, users can now manage any number of databases with Gitora 5 from a single application.

This makes moving code and switching between databases much easier. Maintenance and upgrade tasks are also much simpler because there is only one application to manage.

Moreover, we made installing Gitora much easier. No more running hundreds of database scripts or changing database settings. Once you create a database user for Gitora, the application will automatically install its database objects.

Gitora Now Manages Tables

Tables are now first class citizens in Gitora. You can manage every table’s DDL in Git repos along with its constraints and indexes, in its own file with its complete history, automatically.

No-login Development Experience

Previously, signing in to Gitora required an extra step for database developers. Every time they opened a database session, they had to execute one line of code to sign in to Gitora. Gitora 5 can be configured to recognize selected database users as valid Gitora users. This way, Gitora 5 becomes completely transparent during regular development activities.

A Comprehensive Developer Privileges Management Module

Gitora 5 Enterprise comes with a sophisticated authorization module with which you can manage who can edit which database object and perform which Git operation. You can define privileges for actions such as check out, commit, pull, push etc… at database level and at repo level. You can even restrict access to certain objects to specific developers.

Full API To Completely Automate Your Workflow

Last but not least, Gitora 5 Enterprise comes with a set of API’s so that you can fully automate your workflow. The API’s are easy to use and support the restrictions you specify in the authorization module.

These are our top six features but there are many more. Here is just a few of them:

  • A new and improved diff screen
  • A new way to track every DDL you execute in the database
  • Table DDL generation that is master-detail relationship aware
  • Support for sequences, materialized views

Gitora 5 comes with the features you are already familiar with:

  • Create Git repos with any of your database code objects such as Packages, Views, Types, Triggers etc…
  • Gitora doesn’t miss a change. Capture every change in these objects automatically and commit them to Git with a single click.
  • Perform Git operations such as reset, branch, merge, pull etc.. These operations will update your database objects automatically.
  • Push your repos to any cloud service such as GitHub, GitLab etc…

Gitora is a unique tool that integrates Git to the Oracle Database. It enables you to use Git just like a Java, JavaScript or C# developer.  

Gitora 5 is by far our biggest and most important release since releasing the first version of Gitora in 2015. Gitora 5 is an indispensable tool to improve developer productivity, implement modern development workflows and cut delivery times significantly.

Try Gitora 5 today.

Categories: Development

pySyncOracleStandby – Simple sync service for Oracle manual standby

XTended Oracle SQL - Thu, 2021-01-07 05:55

I created this simple service a couple of years ago. It’s pretty simple, small and intuitive Python app, so you can easily modify it to suit your own needs and run on any platform: https://github.com/xtender/pySync

Categories: Development

Format SQL or PL/SQL directly in Oracle database

XTended Oracle SQL - Tue, 2020-12-22 16:59

Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text’s from v$sqlarea. And thanks to Oracle SqlCL and Oracle SQL Developer, we can easily use oracle.dbtools.app.Format function from their Java library dbtools-common.jar, so if you use SqlCL or SQL Developer, you can use the same formatting options.

1. load appropriate java library into Oracle

You may have already installed SQLDeveloper or SqlCL on your database server, just check $ORACLE_HOME/sqldeveloper or $ORACLE_HOME/sqcl directories. If – not, you need to download appropriate SqlCL version that matches your java version in Oracle. For 12.2 – 19.8 you can download latest SqlCL 20.3. In fact we need just dbtools-common.jar from lib directory. I put it into $ORACLE_HOME/sqlcl/lib directory on the server and load it using loadjava:

loadjava -u login/pass@pdb1 $ORACLE_HOME/sqlcl/lib/dbtools-common.jar

Just change your login credentials and dbname.

2. set java permissions

Then you need to grant required Java permissions:

exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
3. create java functions

As you can see, in the code below you can specify own formatting options (function getFormat()). For example, formatting options from Trivadis: https://github.com/Trivadis/plsql-formatter-settings/blob/main/settings/sql_developer/trivadis_advanced_format.xml

You can copy the code below, but it would be better to take latest code from https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter

CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS
/* Imports */
import oracle.dbtools.app.Format;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import java.io.StringWriter;
import java.io.PrintWriter;


public class SQLFormatter {

    private static String getStackTrace(Exception e) {
       StringWriter writer = new StringWriter();
       PrintWriter printWriter = new PrintWriter( writer );
       e.printStackTrace( printWriter );
       printWriter.flush();

       return writer.toString();
    }

    public static Format getFormat() {
        oracle.dbtools.app.Format format = new oracle.dbtools.app.Format();
        
        format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
        format.options.put("kwCase", Format.Case.UPPER);
        format.options.put("idCase", Format.Case.NoCaseChange);                             // default: Format.Case.lower
        format.options.put("adjustCaseOnly", false);                                        // default: false (set true to skip formatting)
        format.options.put("formatThreshold", 1);                                           // default: 1 (disables deprecated post-processing logic)
        // Alignment
        format.options.put("alignTabColAliases", false);                                    // default: true
        format.options.put("alignTypeDecl", true);
        format.options.put("alignNamedArgs", true);
        format.options.put("alignEquality", false);
        format.options.put("alignAssignments", true);                                       // default: false
        format.options.put("alignRight", false);                                            // default: false
        // Indentation
        format.options.put("identSpaces", 3);                                               // default: 4
        format.options.put("useTab", false);
        // Line Breaks
        format.options.put("breaksComma", Format.Breaks.Before);                            // default: Format.Breaks.After
        format.options.put("breaksProcArgs", false);
        format.options.put("breaksConcat", Format.Breaks.Before);
        format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
        format.options.put("breaksAfterSelect", true);                                      // default: true
        format.options.put("commasPerLine", 1);                                             // default: 5
        format.options.put("breakOnSubqueries", true);
        format.options.put("breakAnsiiJoin", true);                                         // default: false
        format.options.put("breakParenCondition", true);                                    // default: false
        format.options.put("maxCharLineSize", 120);                                         // default: 128
        format.options.put("forceLinebreaksBeforeComment", false);                          // default: false
        format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);   // default: Format.BreaksX2.X2
        format.options.put("flowControl", Format.FlowControl.IndentedActions);
        // White Space
        format.options.put("spaceAroundOperators", true);
        format.options.put("spaceAfterCommas", true);
        format.options.put("spaceAroundBrackets", Format.Space.Default);
        //format.options.put("formatProgramURL", "default");
        
        return format;
    }
    
  public static String format(String str) 
  {
    String res;
    try {
       //res = new Format().format(str);
       Format f = SQLFormatter.getFormat();
       res = f.format(str);
       }
    catch (Exception e){
       res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
    }
    return res;
  }

  public static CLOB formatClob(oracle.sql.CLOB clob) 
  throws SQLException
  {
    String str = clob.getSubString(1, (int) clob.length());
    String res = SQLFormatter.format(str);
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
    CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
    resClob.setString(1L, res);
    
    return resClob;
  }
}
/
4. Create PL/SQL package for it
create or replace package SQLFormatter as

  FUNCTION Format(str in varchar2) RETURN VARCHAR2
  AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';

  FUNCTION FormatClob(str in clob) RETURN CLOB
  AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
  
end;
/

Now we can test it:

SQL> select SQLFormatter.format('select 1 a,2 /*123 */ b,3 c, d from dual, dual d2') qtext from dual;

QTEXT
----------------------------------------------------------------------------------------------------
SELECT
   1 a
 , 2 /*123 */ b
 , 3 c
 , d
FROM
   dual
 , dual d2


SQL> select sql_id,SQLFormatter.format(sql_text) qtext from v$sqlarea where rownum<3 and sql_text is not null;

SQL_ID        QTEXT
------------- -------------------------------------
fbbm59qban13m DELETE FROM idl_sb4$
              WHERE
                    obj# = :1
                 AND part = :2
                 AND version <> :3

1gfaj4z5hn1kf DELETE FROM dependency$
              WHERE
                 d_obj# = :1

Categories: Development

Funny friday Oracle SQL quiz: query running N seconds

XTended Oracle SQL - Fri, 2020-12-11 06:37

Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.

My solution

with v(start_hsecs, delta, flag) as (
  select
    hsecs as start_hsecs, 
    0 as delta,
    1 as flag
  from v$timer
  union all
  select
    v.start_hsecs,
    (t.hsecs-v.start_hsecs)/100 as delta,
    case when (t.hsecs-v.start_hsecs)/100 &gt; :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
  from v, v$timer t
  where v.flag&gt;0
    and t.hsecs&gt;=v.start_hsecs
)
select delta
from v
where flag+rownum&lt;=0;

[collapse]
SQL&gt; var N number
SQL&gt; exec :N := 3 /* seconds */;

PL/SQL procedure successfully completed.

SQL&gt; select...

     DELTA
----------
      3.01

1 row selected.

Elapsed: 00:00:03.01

Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:

select count(*) from dual 
connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;
Categories: Development

Pretzel Logic

Greg Pavlik - Sat, 2020-10-17 18:12

 

Getting Additional Values from a Select-Single Component

Shay Shmeltzer - Wed, 2020-09-30 18:11

With newer versions of Oracle JET we are recommending using the new select-single instead of the old (and deprecated) select-one component. In a past blog entry about tips for using lists in Visual Builder, I showed how to get additional values from the record you selected in a select single. I figured I owe you an update that shows how to achieve the same thing with a select-single. 

When you bind a select-single to a set of options, beyond selecting the value and the label, you are also able to select additional fields that will construct the row of data. In the demo below for example I use id for the value, name for the label, and then add salary as the additional field. The select-single expose an event for value-item change. The payload for this event include not just the key selected, but also "data" - which has all the fields in the record you chose.

You can then simply get their value in the action chain using something like $action.variables.data.fieldName - so in the video below I'm using the $variables.data.salary to get the value of the salary for the selected employee and then assign it to another variable on the page.

Categories: Development

Multi-row Selection with JET 9 in Visual Builder

Shay Shmeltzer - Thu, 2020-09-24 21:29

With the new support for Oracle JET 9 in Oracle Visual Builder, it's much simpler to create a multi-row selection enabled table that with checkboxes for row selection. Below is a quick video showing how this is done. This is much simpler compared to the approach you had to take before.

The table automatically adds a selection checkbox column when you set the selection-mode.row="multiple" attribute. There is an on-selected-changed event on the table - and you can attach an action chain to it. The event gets an array of the selected row keys as an input parameter. In the video, I'm simply looping over this array and show a notification with the row key referring to them with the expression - [[ $variables.keys[$current.index] ]]. You can of course use this array for any follow up steps you want to do on the rows.

Note that this will only work if your app is using the Redwood theme (the default for new apps in VB). If your app is still using an Alta based theme you won't see the checkboxes, you'll need to switch your app to Redwood to get them.

Categories: Development

The Island

Greg Pavlik - Tue, 2020-09-15 23:19

What is guilt? Who is guilty? Is redemption possible? What is sanity? Do persons have a telos, a destiny, both or neither? Ostrov (The Island) asks and answers all these questions and more.

A film that improbably remains one of the best of this century: "reads" like a 19th century Russian novel; the bleakly stunning visual setting is worth the time to watch alone.



Simple function returning Parallel slave info

XTended Oracle SQL - Tue, 2020-09-15 07:38

You can add also any information from v$rtsm_sql_plan_monitor if needed

create or replace function px_session_info return varchar2 parallel_enable as
   vSID int;
   res varchar2(30);
begin
   vSID:=userenv('sid');
   select 
           to_char(s.server_group,'fm000')
    ||'-'||to_char(s.server_set,'fm0000')
    ||'-'||to_char(s.server#,'fm0000')
    ||'('||s.sid||','||s.degree||'/'||s.req_degree||')'
    into res
   from v$px_session s 
   where s.sid=vSID;
   return res;
exception when no_data_found then
   return 'no_parallel';
end;
/

Simple example:

select--+ parallel
  px_session_info, count(*)
from sys.obj$
group by px_session_info
/
PX_SESSION_INFO           COUNT(*)
------------------------  --------
001-0002-0001(630,2/2)     38298
001-0002-0002(743,2/2)     34706
Categories: Development

Need to extend Oracle APEX? Welcome Plug-ins Pro!

Dimitri Gielis - Wed, 2020-09-02 09:22
After APEX Office Print (AOP), APEX Media Extension (AME), and APEX Project Eye (APE), we launched our newest product and service, Plug-ins Pro, in June.


Since 2004 I've used Oracle Application Express (APEX) and I just love it. With every new release of APEX things become easier and new features get added going from new item types like switches to rich components like charts. But the web is moving fast, and customers want more and more which means you need to go outside of what APEX provides. The main building blocks of APEX are the Oracle database, SQL, PL/SQL, HTML, CSS, and JavaScript. You can use any of those technologies to extend Oracle APEX with custom components. This is a real benefit of APEX, you can go from Low Code to High Code in no time. Really, there's no limit... only your imagination and skills.

Over time I've created many extensions for APEX, going from building reusable PL/SQL packages to integrating some JavaScript library. The issue with building those one-off customizations is that you have to maintain them. Where plain Oracle APEX applications stand the test of time very well, for example, an app built in 2006 still runs today, even after upgrading to the latest Oracle APEX version. Whenever I used custom code, especially JavaScript, it was less obvious to keep that current, and it would be one of the first things that break. Next to that, you make the code work specifically for a project, and in the next project you need something similar, so you copy the solution and make changes specific for that project. It's always in the context of a project that things are built, never looking at the bigger picture.

As the Oracle APEX team identified the need to extend APEX and wanted to provide a way that makes it more reusable and easier to maintain between APEX applications, they introduced plug-ins in APEX 4.0, this is now 10 years ago!

The result was that the awesome APEX community started to create and share plug-ins. Most people offer those plug-ins as open-source. Initially, there was an APEX plug-ins website maintained by Itium that had a catalog of all APEX plug-ins, but today you find the plug-ins catalog on apex.world.


There are many great plug-ins from people like Bartosz Ostrowski, Daniel Hochleitner, and Ronny Weiß to name a few, but there are also many plug-ins now that are built for a specific purpose but not maintained any longer. If you included one of those plug-ins in your app, you are on your own. But even if it's still maintained, you can't expect the author to add new features to the plug-in when you need them or when you upgrade the APEX version and it breaks. 

Creating plug-ins and maintaining plug-ins takes a lot of time, especially when you want to think about the bigger picture and make them so good that they can be used in many different projects, they are fully documented and you have some sample code, so it's easy to include in any project, even when it's been a while since you last looked at it.

And this is how Plug-ins Pro was born... we want to take away the pain of having to build and maintain APEX plug-ins. These are not just plug-ins, those are rock-solid, state-of-the-art, professionally built plug-ins that behave just like native Oracle APEX components. They are accessible, translatable, universal theme ready, and most importantly fully documented, with many samples on how to use them AND supported! If you have an issue or you want an extension, you know who to go to.

With AOP we made printing and exporting of data easy in the Oracle database and APEX. With Plug-ins Pro we want to make extending the Oracle database and APEX easy!



Once I knew we wanted to bring high-quality, supported plug-ins to the APEX community, I knew we would need the best people to bring them to life.  Bartosz Ostrowski joined the team in January of this year and began creating our awesome plug-ins.  When we launched Plug-ins Pro we started with 3 highly demanded extensions for Oracle APEX:
And we didn't go light on those plug-ins... the client-side validation plugin for example took over 400h to build!

We started from a requirements document:


We built the plug-in, tested it in on the different APEX versions (starting with 18.1), and finished off by creating extensive documentation and a full-fledged sample application



The goal of Plug-ins Pro is to make you successful, so you can turn your ideas into reality with Oracle APEX and the Database.

Our goal is not to have all the possible plug-ins, instead, we want to create plug-ins that make a difference, and that would take a lot of time to build yourself. We have some plug-ins in the pipeline, some I'm super excited about and can't wait to share with you :) but just like with APEX Office Print (AOP), we especially want to listen to you, what are you searching for, and what would make a difference for you.

This is why on our website, you can submit ideas for new plug-ins and vote on already submitted ideas!


I will write some more blog posts on the plug-ins we offer, but if you already want to try them, head over to the Plug-ins Pro website and try them 1 month for free!


Last but not least... through September 30th, 2020, we are running an Early Adopter Special Offer. Show your support for Plug-ins Pro and help us get off the ground running!  Get all the details at plug-ins-pro.com.



Categories: Development

Free Oracle Cloud: Custom Domain Name (URL) with your own ORDS on the Webserver

Dimitri Gielis - Mon, 2020-08-31 16:19
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

One of the most popular posts in this series is how to Setup a web server on the Virtual Machine.

In that blog post we setup Nginx as our web server, and as a proxy in front of Oracle APEX running on the Autonomous Database Cloud, so we can serve our own domain name e.g. dgielis.com.

Since the release of ORDS 19.4.6 (and higher), Oracle supports that we run our own ORDS version in front of the Oracle Autonomous Database Cloud. This is really cool, for a couple of reasons:
  • We can tune ORDS. Oracle limits our Free ATP to 20 concurrent connections, having our own ORDS we can allow more simultaneous connections to our database by changing the config files.
  • We are able to create a reverse proxy on the same network as where ORDS is running, which will result in a more secure solution.
  • We can have multiple ORDS running against the same database.
  • We have more security options as we can choose where we run ORDS.
But before we go into the details on how to run ORDS on your own Compute VM and let it talk to the Autonomous Database Cloud, I also want to highlight a few consequences of doing this.
There are two main points of attention running your own ORDS in front of your APEX instance:
  • You are responsible to keep your own ORDS version the same as the on the Autonomous Database Cloud. Whenever Oracle upgrades ORDS, you have to upgrade your own ORDS too.
  • You have to update your images folder whenever a patch or upgrade is done for APEX.
Here's a small script I use to check the version of both Oracle APEX and ORDS:

select 'APEX' as product, version_no, api_compatibility, case when patch_applied = 'APPLIED' then (select listagg('Patch ' || to_char(patch_number) || ' (' || patch_version || ') installed on ' || installed_on, ', ') within group (order by installed_on) as patches from apex_patches) end as applied_patches from apex_release
union all
select 'ORDS' as product, ords.installed_version as version_no, null as api_compatibility, null as applied_patches from dual;

This results in:


You could automate the check, and in case something changed that it will email you for example.

Now, let's get started with the installation of ORDS on the same machine as our Nginx webserver.

# Log in as the opc user to the Compute VM and install Nginx and ORDS:
ssh 150.136.245.144 -l opc -i .ssh/oraclecloud

# connect as root
sudo su

# as a best practice, update all packages on Linux
yum update


# install Nginx webserver
yum install -y nginx


# load repo which holds ORDS
yum-config-manager --enable ol7_oci_included

# in case you get an error, add the repo first manually
/etc/yum.repos.d

vi oci-included-ol7.repo

[ol7_oci_included]
name=Oracle Software for OCI users on Oracle Linux $releasever ($basearch)
baseurl=https://yum$ociregion.oracle.com/repo/OracleLinux/OL7/oci/included/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

yum-config-manager --enable ol7_oci_included

# install ORDS
yum install -y ords

Let's also make sure we have the latest versions of SQLcl and the Oracle software and wallet.
Download SQLcl here and check this blog post about the software and wallet and let's make sure we can still connect from our compute instance to our Autonomous Database.

# uninstall old Oracle Instant client
yum remove oracle-instantclient18.5-tools.x86_64

# install latest Oracle Instant client
yum install oracle-instantclient19.8-basic.x86_64 

# connect as oracle 
su - oracle

# set environment variables
export PATH=/usr/lib/oracle/19.8/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.8/client64/lib
export TNS_ADMIN=/usr/lib/oracle/19.8/client64/lib/network/admin

# connect with SQLcl to see if we can connect from the VM to the DB
./sql admin@dbdimi_high


If for some reason something is not working any longer, follow the steps again in the blog post how to run sqlcl from the compute instance to ATP.

We first have to create an ORDS user we will connect at, and it won't be the normal ORDS_PUBLIC_USER as that is used by ATP itself. We will create an ORDS_PUBLIC_USER2 as specified in the Installing and Configuring Customer Managed ORDS on Autonomous Database documentation.

CREATE USER "ORDS_PUBLIC_USER2" IDENTIFIED BY "changeme";

GRANT "CONNECT" TO "ORDS_PUBLIC_USER2";

BEGIN
     ORDS_ADMIN.PROVISION_RUNTIME_ROLE(
         p_user => 'ORDS_PUBLIC_USER2',
         p_proxy_enabled_schemas => TRUE);
END;
/


Now let's configure ORDS to connect to this user in the database:

# make a base64 string of the zip
mkdir /opt/oracle/ords/wallet
cd /opt/oracle/ords/wallet
cp /tmp/wallet_DBDIMI.zip .
base64 -w 0 wallet_DBDIMI.zip > wallet_DBDIMI.zip.b64
WALLET_BASE64=`cat wallet_DBDIMI.zip.b64`

# create the apex_pu.xml and defaults.xml configuration files for ORDS
cat << EOF > /opt/oracle/ords/config/ords/conf/apex_pu.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="db.username">ORDS_PUBLIC_USER2</entry>
  <entry key="db.password">!changeme</entry>
  <entry key="db.wallet.zip.service">dbdimi_low</entry>
  <entry key="db.wallet.zip"><![CDATA[$WALLET_BASE64]]></entry>
</properties>
EOF

cat << EOF > /opt/oracle/ords/config/ords/defaults.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
  <entry key="plsql.gateway.enabled">true</entry>
  <entry key="jdbc.InitialLimit">10</entry>
  <entry key="jdbc.MaxLimit">20</entry>
</properties>
EOF


Let's start ORDS manually first, so we can see if there are any errors:

cd /opt/oracle/ords
java -jar ords.war standalone


Note: In case you get java.lang.OutOfMemoryError: Java heap space, you can fix that by doing: 
export JAVA_OPTIONS=-Xmx512M 
In July I got this error, when I tried again last week I didn't anymore.


Copy Oracle APEX images folder.

Download Oracle APEX and upload the zip file with your favorite SFTP program to your compute instance. E.g. I use Transmit on OSX or you can use wget or something else.

# Unzip the file in the /opt/oracle folder
unzip /tmp/apex_20.1_en.zip -d /opt/oracle/

You need to make sure your APEX Images folder is in sync with the version of APEX running on ATP.
Typically Oracle will apply some patches, so download from Oracle Support, the patch for Oracle APEX and download it to your Compute Instance:

# Unzip the file in the /tmp
unzip p30990551_2010_Generic.zip

# Overwrite the files from the original Oracle APEX folder
cp -rf /tmp/30990551/images /opt/oracle/apex

# Edit the standalone.properties to add the reference to the images folder
vi /opt/oracle/ords/config/ords/standalone/standalone.properties

# Add
standalone.static.context.path=/i
standalone.static.path=/opt/oracle/apex/images


Restart ORDS again, and once we are sure all is working, let's start ORDS as a service and enable it to autostart on reboot:

# make sure you are root user
exit

# run ORDS service and enable with startup
systemctl start ords
systemctl enable ords


Next, we want to configure Nginx as a reverse proxy in front of ORDS.
ORDS by default is running on port 8080. You may want to read my previous blog post on how to get started with Nginx. I didn't configure the domain yet, but I just wanted to access the IP.

The different bit is :

vi /etc/nginx/conf.d/dgielis.com.conf

Add the following:

server {
    listen         80;
    listen         [::]:80;
    server_name    150.136.245.144;
    root           /usr/share/nginx/html/dgielis.com;
    index          index.html;
    try_files $uri /index.html;

 location /i/20.1.0.00.13/ {
  alias /opt/oracle/apex/images/;
 }

 location /ords/ {
  proxy_pass http://localhost:8080/ords/;
  proxy_redirect off;
  proxy_set_header Host $host;
  proxy_set_header X-Real-IP $remote_addr;
  proxy_set_header X-Forwarded-Proto  $scheme;
  proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
 }

}

Next, you want to reload Nginx:

nginx -s reload

The first time I tried to access http://150.136.245.144/ords/ I got a bad gateway error.

Looking at the logs I saw what was going on: 

tail -30 /var/log/nginx/error.log


To fix the issue I did:

cat /var/log/audit/audit.log | grep nginx | grep denied | audit2allow -M mynginx
semodule -i mynginx.pp

And there we go... it worked! Now we can access Oracle APEX with our own ORDS :)



Note: instead of using Nginx, you can also configure a Load Balancer in the Oracle Cloud. 
Adrian did a nice blog post on how to do that.


There you go... now you can choose to have your preferred domain name (custom URL) with your own ORDS in the Oracle Cloud.

Categories: Development

Improving Perceived Performance for Visual Builder Pages

Shay Shmeltzer - Tue, 2020-08-04 22:18

Perceived performance is how your end user perceived the speed of your application. There are all sorts of "tricks" you can use to influence this perception and help your application seem faster in the eyes of the user. In this blog we are going to cover two quick ideas - the first is shifting delays from initial page load to a later time, and the second is providing the user with a distraction while a long running action is taking place.

Control Data Fetch Timing

In the first part of the demo below I show how you can control the timing of data fetching in your application, delaying data fetch until it is actually shown on the page. This is a common technique you can use to improve the initial rendering of a page. There is a tradeoff to take into account between slow initial rendering and slowness later on when working with the page. In the demo we use an oj-bind-if component to hide a table/list until it is actually shown on the page. Since the table is not rendered, the SDP backing the table not executing the REST call initially. When we actually need to show the data, we update the variable controlling the oj-bind-if and only then is the REST call executed and the data fetched and displayed. Another advantage of this technique is that if the user never actually require to see the specific component, the REST call won't be executed and you saved overall time. In the demo I'm demoing how to do this with a collapsible area, but similar approach could be implemented for other hidden areas - such as tabs on a page.

Notify The User About Delays

The second part of the video shows how to notify a user that the delay they are experiencing is because the app is actually busy doing something. Again there is a psychological aspect here - a user is more likely to accept a delay if they know that something is happening, and in addition the user mind is busy reading the text which distract them from noticing the delay. To do this we add a dialog to the page, and open it at the start of an action chain, closing it as the last step in the action chain using the call component method action.

As you can see there are various things you can do to help tune your application as well as your end user experience working with the app.

Categories: Development

Automating Data Load into Visual Builder Business Objects

Shay Shmeltzer - Wed, 2020-07-29 22:54

Visual Builder is often used to create apps that let users review and update data located in an external system. When the external system lacks proper REST APIs to support directly reading and writing the data, a common solution is to extract data from the origin system and load it into business objects in the VB app. Users can then directly modify the data in the BOs, and the data is synched later back to the original system. 

In this blog I show how to create an automated data loading process into the business objects leveraging Visual Builder Studio. The blog doesn't cover extracting the data from the origin system - there are many ways to do this including Oracle Integration Cloud. We start from a situation where the data we need to upload is available in csv files.

Data Management Build Tasks

Among the new build tasks added to Visual Builder Studio specifically for Visual Applications are tasks for import and export of data. These will allow you to load the data into the business objects and export it later on.

Import Step

If you are working on a live production application, you will need to first lock the application in order to bulk load the data. You achieve this using the lock and unlock build steps. This is needed since the import process replaces the data currently in the business objects - so you don't want people working on that data at this point of time and losing their work.

In the demo video below, we added a Git repository to our project into which we'll be uploading a zip file containing csv files with the data we want to load into business objects. You can generate an initial zip file with the correct structure of the files using the export option in the data manager in Visual Builder.

We create a build job with 3 build steps: we first lock the live app, then import data, and then unlock the app. The steps for doing this require the Root URL and version number for the deployed app - which you can see in the deployment tab in the Environments section of Visual Builder Studio (or in the visual-application.json file in the root directory of your git repo).

In the video I show how to define a periodic schedule to trigger these build job. But if you prefer, you can also trigger the build automatically when the zip file in the git repository is changed.

Note that the import functionality replaces the content of the BOs - so be sure to first export the data if you need a copy.

The export step can also be used to get the updated data out of the BOs, so you can then load it into the source system or leverage it in other places.

 

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development