Development

Filtering a Table, List or Other Collections in Oracle Visual Builder Cloud Service

Shay Shmeltzer - Wed, 2018-05-16 18:36

A common use case when working with data is to try and filter it.

For example if you have a set of records shown in a table in the UI the user might want to filter those to show specific rows.

In the video below I show you the basic way to achieve this using the filterCriterion of ServiceDataProvider variables - the type of variable that populates tables and lists.

Basically each SDP has an attribute called filterCriterion that accepts a structure that can contain arrays of conditions. You can then map a criteria that leverage for example a page level variable connected to a field.

FilterCriterion setting

In the criteria you'll specify

  • an attribute - this will be the column id (not title) of your business object
  • a value - this is the value you are filtering based on - usually a pointer to a variable in your page
  • An operator (op) - the filterCriterion is using operators like $eq or $ne - these are based on the Oracle JET AttributeFilterOperator - a full list of the operators is here.

In the video I end up with a filterCriterion that is:

{ "criteria": [ { "value": "{{ $page.variables.filterVar }}", "op": "{{ \"$eq\"\n }}", "attribute": "{{ \"traveler\"\n }}" } ], "op": "{{ \"$or\"\n }}" }

Since you can have multiple criteria you can also specify an operator on them - either an or ($or) or an and ($and) - these are CompoudOperators from Oracle JET.

By the way, this will work automatically for your Business Objects, however if you want to apply this to data from a random REST service - then that service will need to have filtering transformation defined for it.

 

Categories: Development

Reflecting Changes in Business Objects in UI Tables with Visual Builder

Shay Shmeltzer - Wed, 2018-05-16 11:52

While the quick start wizards in Visual Builder Cloud Service (VBCS) make it very easy to create tables and other UI components and bind them to business objects, it is good to understand what is going on behind the scenes, and what the wizards actually do. Knowing this will help you achieve things that we still don't have wizards for.

For example - let's suppose you created a business object and then created a UI table that shows the fields from that business object in your page. You probably used the "Add Data" quick start wizard to do that. But then you remembered that you need one more column added to your business object, however after you added that one to the BO, you'll notice it is not automatically shown in the UI. That makes sense since we don't want to automatically show all the fields in a BO in the UI.

But how do you add this new column to the UI?

The table's Add Data wizard will be disabled at this point - so is your only option to drop and recreate the UI table? Of course not!

 

If you'll look into the table properties you'll see it is based on a page level ServiceDataProvider ( SDP for short) variable. This is a special type of object that the wizards create to represent collections. If you'll look at the variable, you'll see that it is returning data using a specific type. Note that the type is defined at the flow level - if you'll look at the type definition you'll see where the fields that make up the object are defined.

Type Definition

It is very easy to add a new field here - and modify the type to include the new column you added to the BO. Just make sure you are using the column's id - and not it's title - when you define the new field in the items array.

Now back in the UI you can easily modify the code of the table to add one more column that will be hooked up to this new field in the SDP that is based on the type.

Sounds complex? It really isn't - here is a 3 minute video showing the whole thing end to end:

As you see - a little understanding of the way VBCS works, makes it easy to go beyond the wizards and achieve anything.

Categories: Development

Leveraging "On Field Value Changes" Event in Visual Builder Cloud Service - Redone

Shay Shmeltzer - Tue, 2018-05-15 16:08

With the new Oracle Visual Builder Cloud Service (VBCS) released this month, some of my past how-to's that shows tricks in VBCS are no longer valid/needed.

The direct access we provide to both REST services and the UI components in your application make things that in the past required code or hacking much simpler.

Here is one example - reacting to value change events on fields and modifying other UI components based on them.

Input component have a "value" event that you can hook into and provide an action chain that will be executed when the value change.

In the video below you see for example how I can use a value selected in a drop down list to control whether other components on the page are shown or hidden.

To do this, you define a page variable that you can change in the "value" event. You can then rely on that page variable to control another component behavior.

As you can see - no coding needed - just drag and drop your way to create the functionality.

action chain

 

 

Categories: Development

Why Are You So Quiet?

Shay Shmeltzer - Wed, 2018-05-09 12:19

You might have noticed that this blog didn't post new entries in the past couple of months, and you might have wondered why.

Well the answer is that I've been publishing content on some other related blogs around the Oracle blogsphere.

If you want to read those have a look at my author page here:

https://blogs.oracle.com/author/shay-shmeltzer

As you'll see we have new versions of both Visual Builder Cloud Service and Developer Cloud Service - both with extensive updates to functionality.

Working and learning those new versions and producing some demos is another reason I wasn't that active here lately.

That being said, now that both are out there - you are going to see more blogs coming from me.

But as mentioned at the top - these might be published in other blogs too.

So to keep up to date you might want to subscribe to this feed:

https://blogs.oracle.com/author/shay-shmeltzer/rss

See you around,

Shay

Categories: Development

All About Oracle Autonomous Data Warehouse Cloud

Gerger Consulting - Tue, 2018-05-08 00:06
Oracle has just released their Autonomous Data Warehouse Cloud Service (ADW). There is no one better to tell you all about it than the Senior Principal Product Manager Yasin Baskan who's been leading the project.


Attend the free webinar by ADW Senior Principal Product Manager Yasin Baskan on May 22nd and learn all about the Autonomous DWH Cloud from the people who created it.


About the Webinar

Oracle’s new cloud service, Autonomous Data Warehouse Cloud, is now generally available. This is a fully-managed service that automates many day-to-day operations for DBAs and DWH developers.

We will talk about the functionality Autonomous Data Warehouse provides and go into details about the technical aspects of the service.

You will be able to learn how you can provision new databases, load data, and run queries. We will walk through the automated functionality like database configuration, security, backup and recovery, and performance. This session will provide you the information you need to start trying out the service.

Sign up for the webinar.

About the Presenter

Yasin has been with Oracle for nine years in different roles such as pre-sales and product management. Before working for Oracle, he had been an Oracle DBA in the financial industry starting with Oracle Database version 7.3. He has 20 years of IT and Oracle Database experience. Since 2014 he has been part of the data warehousing product management team at Oracle HQ.

In his current role, he is leading the Autonomous Data Warehouse Cloud as the Senior Principal Product Manager.

Sign up for the webinar.
Categories: Development

CockroachDB for Oracle Professionals

Gerger Consulting - Tue, 2018-04-10 19:05
Many Oracle DBA's are adding new databases to their toolbox. Oracle Certified Master Ronald Rood is one of them.


In this presentation, Ronald will talk about the success he had in implementing solutions with CockroachDB.
CockroachDB is a colud-native database built on the ideas of old SQL, combined with no SQL giving DBA's an entirely new SQL database. In this webinar Ronald will talk about:
  • What is CockroachDB? What problems is it good for?
  • How to get started?
  • How to setup a secure cluster where each node is open for read and for write.
  • How to connect client applications to the database in such a way that the loss of a node does not matter.
  • A quick explanation of how raft consensus ensures integrity.
  • How to use availability zones for even better availability.
Register now.
Categories: Development

My top 3 to gather user feedback in an Oracle APEX app

Dimitri Gielis - Tue, 2018-03-13 10:21
.gist-file .gist-data {max-height: 450px;}
In every Oracle APEX application we create, we add a feedback mechanism. We're not only doing this during the development phase, but we also leave it enabled in production.

We want to give the users the ability to give feedback to the team in a structured way. It should not be an hassle to give feedback, one click for the user where he can say what it's going; if he likes it, if something can be improved, if there's a bug... but at the same time we want as much info as possible at the time the user enters this feedback. What app are they in, which page are they on, what session information was there...

Most of the users really like to be heard and the feedback mechanism in our apps helped us a lot to engage with our customers.

Here're my top 3 ways of providing such feedback mechanism in an Oracle APEX app:

Feedback link and Team Development (pre APEX 18.1)

This method I started to use with Oracle APEX 4.2 and beyond. I blogged about using this feature here and here before.

In APEX you can create a new page; called the Feedback page. It will not only create a page, but also a navigation bar entry. Some screenshots of the wizard:


Which will result in:


I prefere to use the Modal dialog option as it feels more integrated in the app. This page is a normal APEX page, so you can further customise. For example you can make the Application and Page fields hidden.

The feedback that is entered is stored in Team Development - an area in APEX where you can do your project management.



Feedback feature in Blueprint and the new APEX 18.1 app creation wizard

In Oracle APEX 18.1 the application wizard got an overhaul and is now inline with the blueprint feature which was enabled previously on apex.oracle.com.


Enabling feedback is a matter of ticking a checkbox... and the result looks awesome to me :)


The user can share his feeling by selecting a smily, enter some feedback and include an attachment. When you use APEX 18.1, you will see the nice floating label (label inside box) for the feedback text item. Also when you submit feedback you get a nicer message that the feedback was submitted, instead that the dialog will automatically disappear as with the standard feedback page in 5.1 and earlier.

In Blueprint on apex.oracle.com and in APEX 5.2 EA1 (which became now APEX 18.1 EA2) the feedback was stored in its own table, but since APEX 18.1 EA2 the feedback is stored again in Team Development. In the Administration section there are a couple of screens that query the apex_team_feedback view. It looks like many of the Blueprint features that were showcased before, became native APEX features (e.g. email framework, feedback).


When clicking on User Feedback and the pencil you see the details for every entry.


You can enter a response which will update Team Development by using the APEX API  apex_util.reply_to_feedback.

What I find interesting is that APEX 18.1 made a change in their pages; it catalogs the pages now as Component or Feature. A feature contains more than just a page, so the Feedback page became the Feedback feature and will create more pages than just the feedback page itself. Just like if you click the box with Blueprint, it can add the administration section and navigation bar entry.


The feedback mechanism we knew in APEX 5.1 and before, got a nice update in Oracle APEX 18.1, it might be worth doing an upgrade once 18.1 hits production.
One nice addition would be the ability to add a screenshot and annotate the screen like Martin and I build a long time ago. It would save the user creating a screenshot and uploading the file.

Feedback with REST API

If you are using another issue or ticketing system it might be worthwhile to gather the feedback there. You can still create the feedback page, but add some additional processes (or replace the team development process) so the feedback is stored in your favourite tracking system like Bugzilla, Jira, Redmine and others. In the next section I'll show how to integrate with two issue tracking systems we use.

Oracle Developer Cloud Issues

In the projects where we use Oracle Exadata Express, we use Oracle Developer Cloud service, which you get with your Exadata Express account to manage our project.


The nice thing is that you have a Git repo and Issues all available and all the other things around to mange and streamline your project. In the feedback page I added a call to a PL/SQL procedure, so an issue is created whenever feedback is given.

The PL/SQL package I wrote to create an Oracle Developer Cloud issue:

Bitbucket Git Issues

When we started in 2015 with the development of APEX Office Print, we used Team Development in Oracle APEX to manage the development and for version control we used Bitbucket (Git repo). We use different technologies like PL/SQL, APEX, Node.js, Markdown, CSS and HTML. A bit later we decided to use the issues in Git for our node.js code as it made it easier to track an issue/feature and a certain commit (we enter the issue number when we commit).


Bitbucket has also some nice features to integrate Trello boards and Bitbucket cards.

At one stage I wanted to add all our Team Development features as Bitbucket issues, so I wrote a small script that calls the REST API and creates the issues for you:

Nothing stops you to add a process on your Feedback page in Oracle APEX to create a Bitbucket issue automatically. If you copy everything inside the for loop, you are golden.

I hope this post helps you to get user feedback... and don't hesitate to put in the comments what you do to engage with your users.
Categories: Development

Unit Testing for PL/SQL

Gerger Consulting - Wed, 2018-03-07 14:48
Hi PL/SQL Developers! 
We all could use more units tests for our PL/SQL code. :-)
Attend the free webinar by utPLSQL lead architect Jacek Gebal and learn how to implement robust unit tests for PL/SQL using the free and open source utPLSQL unit testing framework. 

Register at: 
http://www.prohuddle.com/webinars/jacek/unit-testing-plsql.php

Categories: Development

Oracle Backup and Recovery in the Cloud

Gerger Consulting - Tue, 2018-01-30 10:17
Attend our free webinar on February 13th and learn how you can implement a robust backup and recovery solution for your Oracle database running in the cloud.

About the Webinar:
A common misconception is that once you move your database to the cloud, you are done and data protection stops being your problem; it automatically backs itself up, recovers magically and of course it all happens securely. Sadly, nothing can be further from the truth.
In reality, the cloud opens up new opportunities but it also comes with its own challenges, especially when it comes to implementing a secure, reliable and fast database backup and recovery solution.
In this webinar you'll learn about how the cloud changes your database backup&recovery strategy and what solutions are available to you to use with your Oracle Database in the cloud.
Specifically, we'll cover the following topics:
  • Backup and recovery strategies for databases running in the cloud
  • Pros and cons of various backup and recovery strategies
  • How to do native, multi-cloud data protection
  • How to streamline and automate backups in the cloud to reduce costs and improve efficiency
We will also do a live demo with Oracle database running in the Amazon Cloud.



Categories: Development

Quick SQL: from Packaged App to built-in feature in Oracle APEX 5.2

Dimitri Gielis - Tue, 2018-01-02 04:26
I blogged about Quick SQL already a few times as I saw not many developers knew about it.

In Oracle APEX 5.1 you can install Quick SQL by going to the Packaged Apps section and install it from there:



I really love Quick SQL as it allows me to build my data model very fast, but also shows me the structure of the tables in a very efficient way. That is why I created a script that can reverse engineer existing tables into the Quick SQL format.

From Oracle APEX 5.2 onwards you won't find Quick SQL in the packaged app section anymore... but no worries, it's not gone, it's now built-in the APEX framework itself :)

Go to SQL Workshop - SQL Scripts:


Hit the Quick SQL button:


Here you have Quick SQL :)


You can run your script after you save, straight from this interface.

Note: the screenshots are taken from Oracle APEX 5.2 Early Adopter, so things might change in the final release of APEX 5.2.
Categories: Development

Karamazov

Greg Pavlik - Sat, 2017-12-23 17:36
 Chapter 3  -  Conversations and Exhortations of Father Zosima

(e) The Russian Monk and His Possible Significance.

FATHERS and teachers, what is the monk? In the cultivated world the word is nowadays pronounced by some people with a jeer, and by others it is used as a term of abuse, and this contempt for the monk is growing. It is true, alas, it is true, that there are many sluggards, gluttons, profligates, and insolent beggars among monks. Educated people point to these: "You are idlers, useless members of society, you live on the labour of others, you are shameless beggars." And yet how many meek and humble monks there are, yearning for solitude and fervent prayer in peace! These are less noticed, or passed over in silence. And how surprised men would be if I were to say that from these meek monks, who yearn for solitary prayer, the salvation of Russia will come perhaps once more! For they are in truth made ready in peace and quiet "for the day and the hour, the month and the year." Meanwhile, in their solitude, they keep the image of Christ fair and undefiled, in the purity of God's truth, from the times of the Fathers of old, the Apostles and the martyrs. And when the time comes they will show it to the tottering creeds of the world. That is a great thought. That star will rise out of the East.
  That is my view of the monk, and is it false? Is it too proud? Look at the worldly and all who set themselves up above the people of God; has not God's image and His truth been distorted in them? They have science; but in science there is nothing but what is the object of sense. The spiritual world, the higher part of man's being is rejected altogether, dismissed with a sort of triumph, even with hatred. The world has proclaimed the reign of freedom, especially of late, but what do we see in this freedom of theirs? Nothing but slavery and self-destruction! For the world says:
  "You have desires and so satisfy them, for you have the same rights as the most rich and powerful. Don't be afraid of satisfying them and even multiply your desires." That is the modern doctrine of the world. In that they see freedom. And what follows from this right of multiplication of desires? In the rich, isolation and spiritual suicide; in the poor, envy and murder; for they have been given rights, but have not been shown the means of satisfying their wants. They maintain that the world is getting more and more united, more and more bound together in brotherly community, as it overcomes distance and sets thoughts flying through the air.
  Alas, put no faith in such a bond of union. Interpreting freedom as the multiplication and rapid satisfaction of desires, men distort their own nature, for many senseless and foolish desires and habits and ridiculous fancies are fostered in them. They live only for mutual envy, for luxury and ostentation. To have dinners visits, carriages, rank, and slaves to wait on one is looked upon as a necessity, for which life, honour and human feeling are sacrificed, and men even commit suicide if they are unable to satisfy it. We see the same thing among those who are not rich, while the poor drown their unsatisfied need and their envy in drunkenness. But soon they will drink blood instead of wine, they are being led on to it. I ask you is such a man free? I knew one "champion of freedom" who told me himself that, when he was deprived of tobacco in prison, he was so wretched at the privation that he almost went and betrayed his cause for the sake of getting tobacco again! And such a man says, "I am fighting for the cause of humanity."
  How can such a one fight? What is he fit for? He is capable perhaps of some action quickly over, but he cannot hold out long. And it's no wonder that instead of gaining freedom they have sunk into slavery, and instead of serving, the cause of brotherly love and the union of humanity have fallen, on the contrary, into dissension and isolation, as my mysterious visitor and teacher said to me in my youth. And therefore the idea of the service of humanity, of brotherly love and the solidarity of mankind, is more and more dying out in the world, and indeed this idea is sometimes treated with derision. For how can a man shake off his habits? What can become of him if he is in such bondage to the habit of satisfying the innumerable desires he has created for himself? He is isolated, and what concern has he with the rest of humanity? They have succeeded in accumulating a greater mass of objects, but the joy in the world has grown less.
  The monastic way is very different. Obedience, fasting, and prayer are laughed at, yet only through them lies the way to real, true freedom. I cut off my superfluous and unnecessary desires, I subdue my proud and wanton will and chastise it with obedience, and with God's help I attain freedom of spirit and with it spiritual joy. Which is most capable of conceiving a great idea and serving it- the rich in his isolation or the man who has freed himself from the tyranny of material things and habits? The monk is reproached for his solitude, "You have secluded yourself within the walls of the monastery for your own salvation, and have forgotten the brotherly service of humanity!" But we shall see which will be most zealous in the cause of brotherly love. For it is not we, but they, who are in isolation, though they don't see that. Of old, leaders of the people came from among us, and why should they not again? The same meek and humble ascetics will rise up and go out to work for the great cause. The salvation of Russia comes from the people. And the Russian monk has always been on the side of the people. We are isolated only if the people are isolated. The people believe as we do, and an unbelieving reformer will never do anything in Russia, even if he is sincere in heart and a genius. Remember that! The people will meet the atheist and overcome him, and Russia will be one and Orthodox. Take care of the peasant and guard his heart. Go on educating him quietly. That's your duty as monks, for the peasant has God in his heart.

(f) Of Masters and Servants, and of whether it is possible for them to be Brothers in the Spirit

Of course, I don't deny that there is sin in the peasants too. And the fire of corruption is spreading visibly, hourly, working from above downwards. The spirit of isolation is coming upon the people too. Money-lenders and devourers of the commune are rising up. Already the merchant grows more and more eager for rank, and strives to show himself cultured though he has not a trace of culture, and to this end meanly despises his old traditions, and is even ashamed of the faith of his fathers. He visits princes, though he is only a peasant corrupted. The peasants are rotting in drunkenness and cannot shake off the habit. And what cruelty to their wives, to their children even! All from drunkenness! I've seen in the factories children of nine years old, frail, rickety, bent and already depraved. The stuffy workshop, the din of machinery, work all day long, the vile language and the drink, the drink- is that what a little child's heart needs? He needs sunshine, childish play, good examples all about him, and at least a little love. There must be no more of this, monks, no more torturing of children, rise up and preach that, make haste, make haste!
  But God will save Russia, for though the peasants are corrupted and cannot renounce their filthy sin, yet they know it is cursed by God and that they do wrong in sinning. So that our people still believe in righteousness, have faith in God and weep tears of devotion.
  It is different with the upper classes. They, following science, want to base justice on reason alone, but not with Christ, as before, and they have already proclaimed that there is no crime, that there is no sin. And that's consistent, for if you have no God what is the meaning of crime? In Europe the people are already rising up against the rich with violence, and the leaders of the people are everywhere leading them to bloodshed, and teaching them that their wrath is righteous. But their "wrath is accursed, for it is cruel." But God will save Russia as He has saved her many times. Salvation will come from the people, from their faith and their meekness.
  Fathers and teachers, watch over the people's faith and this will not be a dream. I've been struck all my life in our great people by their dignity, their true and seemly dignity. I've seen it myself, I can testify to it, I've seen it and marvelled at it, I've seen it in spite of the degraded sins and poverty-stricken appearance of our peasantry. They are not servile, and even after two centuries of serfdom they are free in manner and bearing, yet without insolence, and not revengeful and not envious. "You are rich and noble, you are clever and talented, well, be so, God bless you. I respect you, but I know that I too am a man. By the very fact that I respect you without envy I prove my dignity as a man."
In truth if they don't say this (for they don't know how to say this yet), that is how they act. I have seen it myself, I have known it myself, and, would you believe it, the poorer our Russian peasant is, the more noticeable is that serene goodness, for the rich among them are for the most part corrupted already, and much of that is due to our carelessness and indifference. But God will save His people, for Russia is great in her humility. I dream of seeing, and seem to see clearly already, our future. It will come to pass that even the most corrupt of our rich will end by being ashamed of his riches before the poor, and the poor, seeing his humility, will understand and give way before him, will respond joyfully and kindly to his honourable shame. Believe me that it will end in that; things are moving to that. Equality is to be found only in the spiritual dignity of man, and that will only be understood among us. If we were brothers, there would be fraternity, but before that they will never agree about the division of wealth. We preserve the image of Christ, and it will shine forth like a precious diamond to the whole world. So may it be, so may it be!
  Fathers and teachers, a touching incident befell me once. In my wanderings I met in the town of K. my old orderly, Afanasy. It was eight years since I had parted from him. He chanced to see me in the market-place, recognised me, ran up to me, and how delighted he was! He simply pounced on me: "Master dear, is it you? Is it really you I see?" He took me home with him.
  He was no longer in the army, he was married and already had two little children. He and his wife earned their living as costermongers in the market-place. His room was poor, but bright and clean. He made me sit down, set the samovar, sent for his wife, as though my appearance were a festival for them. He brought me his children: "Bless them, Father."
  "Is it for me to bless them? I am only a humble monk. I will pray for them. And for you, Afanasy Pavlovitch, I have prayed every day since that day, for it all came from you," said I. And I explained that to him as well as I could. And what do you think? The man kept gazing at me and could not believe that I, his former master, an officer, was now before him in such a guise and position; it made him shed tears.
  "Why are you weeping?" said I, "better rejoice over me, dear friend, whom I can never forget, for my path is a glad and joyful one."
  He did not say much, but kept sighing and shaking his head over me tenderly.
  "What has become of your fortune?" he asked.
  "I gave it to the monastery," I answered; "we live in common."
  After tea I began saying good-bye, and suddenly he brought out half a rouble as an offering to the monastery, and another half-rouble I saw him thrusting hurriedly into my hand: "That's for you in your wanderings, it may be of use to you, Father."
  I took his half-rouble, bowed to him and his wife, and went out rejoicing. And on my way I thought: "Here we are both now, he at home and I on the road, sighing and shaking our heads, no doubt, and yet smiling joyfully in the gladness of our hearts, remembering how God brought about our meeting."
I have never seen him again since then. I had been his master and he my servant, but now when we exchanged a loving kiss with softened hearts, there was a great human bond between us. I have thought a great deal about that, and now what I think is this: Is it so inconceivable that that grand and simple-hearted unity might in due time become universal among the Russian people? I believe that it will come to pass and that the time is at hand.
  And of servants I will add this: In old days when I was young I was often angry with servants; "the cook had served something too hot, the orderly had not brushed my clothes." But what taught me better then was a thought of my dear brother's, which I had heard from him in childhood: "Am I worth it, that another should serve me and be ordered about by me in his poverty and ignorance?" And I wondered at the time that such simple and self-evident ideas should be so slow to occur to our minds.
  It is impossible that there should be no servants in the world, but act so that your servant may be freer in spirit than if he were not a servant. And why cannot I be a servant to my servant and even let him see it, and that without any pride on my part or any mistrust on his? Why should not my servant be like my own kindred, so that I may take him into my family and rejoice in doing so? Even now this can be done, but it will lead to the grand unity of men in the future, when a man will not seek servants for himself, or desire to turn his fellow creatures into servants as he does now, but on the contrary, will long with his whole heart to be the servant of all, as the Gospel teaches.
  And can it be a dream, that in the end man will find his joy only in deeds of light and mercy, and not in cruel pleasures as now, in gluttony, fornication, ostentation, boasting and envious rivalry of one with the other? I firmly believe that it is not and that the time is at hand. People laugh and ask: "When will that time come and does it look like coming?" I believe that with Christ's help we shall accomplish this great thing. And how many ideas there have been on earth in the history of man which were unthinkable ten years before they appeared! Yet when their destined hour had come, they came forth and spread over the whole earth. So it will be with us, and our people will shine forth in the world, and all men will say: "The stone which the builders rejected has become the cornerstone of the building."
  And we may ask the scornful themselves: If our hope is a dream, when will you build up your edifice and order things justly by your intellect alone, without Christ? If they declare that it is they who are advancing towards unity, only the most simple-hearted among them believe it, so that one may positively marvel at such simplicity. Of a truth, they have more fantastic dreams than we. They aim at justice, but, denying Christ, they will end by flooding the earth with blood, for blood cries out for blood, and he that taketh up the sword shall perish by the sword. And if it were not for Christ's covenant, they would slaughter one another down to the last two men on earth. And those two last men would not be able to restrain each other in their pride, and the one would slay the other and then himself. And that would come to pass, were it not for the promise of Christ that for the sake of the humble and meek the days shall be shortened.
  While I was still wearing an officer's uniform after my duel, I talked about servants in general society, and I remember everyone was amazed at me. "What!" they asked, "are we to make our servants sit down on the sofa and offer them tea?" And I answered them: "Why not, sometimes at least?" Everyone laughed. Their question was frivolous and my answer was not clear; but the thought in it was to some extent right.

(g) Of Prayer, of Love, and of Contact with other Worlds.

Young man, be not forgetful of prayer. Every time you pray, if your prayer is sincere, there will be new feeling and new meaning in it, which will give you fresh courage, and you will understand that prayer is an education. Remember, too, every day, and whenever you can, repeat to yourself, "Lord, have mercy on all who appear before Thee to-day." For every hour and every moment thousands of men leave life on this earth, and their souls appear before God. And how many of them depart in solitude, unknown, sad, dejected that no one mourns for them or even knows whether they have lived or not! And behold, from the other end of the earth perhaps, your prayer for their rest will rise up to God though you knew them not nor they you. How touching it must be to a soul standing in dread before the Lord to feel at that instant that, for him too, there is one to pray, that there is a fellow creature left on earth to love him too! And God will look on you both more graciously, for if you have had so much pity on him, how much will He have pity Who is infinitely more loving and merciful than you! And He will forgive him for your sake.
  Brothers, have no fear of men's sin. Love a man even in his sin, for that is the semblance of Divine Love and is the highest love on earth. Love all God's creation, the whole and every grain of sand in it. Love every leaf, every ray of God's light. Love the animals, love the plants, love everything. If you love everything, you will perceive the divine mystery in things. Once you perceive it, you will begin to comprehend it better every day. And you will come at last to love the whole world with an all-embracing love. Love the animals: God has given them the rudiments of thought and joy untroubled. Do not trouble it, don't harass them, don't deprive them of their happiness, don't work against God's intent. Man, do not pride yourself on superiority to the animals; they are without sin, and you, with your greatness, defile the earth by your appearance on it, and leave the traces of your foulness after you- alas, it is true of almost every one of us! Love children especially, for they too are sinless like the angels; they live to soften and purify our hearts and, as it were, to guide us. Woe to him who offends a child! Father Anfim taught me to love children. The kind, silent man used often on our wanderings to spend the farthings given us on sweets and cakes for the children. He could not pass by a child without emotion. That's the nature of the man.
  At some thoughts one stands perplexed, especially at the sight of men's sin, and wonders whether one should use force or humble love. Always decide to use humble love. If you resolve on that once for all, you may subdue the whole world. Loving humility is marvellously strong, the strongest of all things, and there is nothing else like it.
  Every day and every hour, every minute, walk round yourself and watch yourself, and see that your image is a seemly one. You pass by a little child, you pass by, spiteful, with ugly words, with wrathful heart; you may not have noticed the child, but he has seen you, and your image, unseemly and ignoble, may remain in his defenceless heart. You don't know it, but you may have sown an evil seed in him and it may grow, and all because you were not careful before the child, because you did not foster in yourself a careful, actively benevolent love. Brothers, love is a teacher; but one must know how to acquire it, for it is hard to acquire, it is dearly bought, it is won slowly by long labour. For we must love not only occasionally, for a moment, but for ever. Everyone can love occasionally, even the wicked can.
  My brother asked the birds to forgive him; that sounds senseless, but it is right; for all is like an ocean, all is flowing and blending; a touch in one place sets up movement at the other end of the earth. It may be senseless to beg forgiveness of the birds, but birds would be happier at your side- a little happier, anyway- and children and all animals, if you were nobler than you are now. It's all like an ocean, I tell you. Then you would pray to the birds too, consumed by an all-embracing love, in a sort of transport, and pray that they too will forgive you your sin. Treasure this ecstasy, however senseless it may seem to men.
  My friends, pray to God for gladness. Be glad as children, as the birds of heaven. And let not the sin of men confound you in your doings. Fear not that it will wear away your work and hinder its being accomplished. Do not say, "Sin is mighty, wickedness is mighty, evil environment is mighty, and we are lonely and helpless, and evil environment is wearing us away and hindering our good work from being done." Fly from that dejection, children! There is only one means of salvation, then take yourself and make yourself responsible for all men's sins, that is the truth, you know, friends, for as soon as you sincerely make yourself responsible for everything and for all men, you will see at once that it is really so, and that you are to blame for everyone and for all things. But throwing your own indolence and impotence on others you will end by sharing the pride of Satan and murmuring against God.
  Of the pride of Satan what I think is this: it is hard for us on earth to comprehend it, and therefore it is so easy to fall into error and to share it, even imagining that we are doing something grand and fine. Indeed, many of the strongest feelings and movements of our nature we cannot comprehend on earth. Let not that be a stumbling-block, and think not that it may serve as a justification to you for anything. For the Eternal judge asks of you what you can comprehend and not what you cannot. You will know that yourself hereafter, for you will behold all things truly then and will not dispute them. On earth, indeed, we are, as it were, astray, and if it were not for the precious image of Christ before us, we should be undone and altogether lost, as was the human race before the flood. Much on earth is hidden from us, but to make up for that we have been given a precious mystic sense of our living bond with the other world, with the higher heavenly world, and the roots of our thoughts and feelings are not here but in other worlds. That is why the philosophers say that we cannot apprehend the reality of things on earth.
  God took seeds from different worlds and sowed them on this earth, and His garden grew up and everything came up that could come up, but what grows lives and is alive only through the feeling of its contact with other mysterious worlds. If that feeling grows weak or is destroyed in you, the heavenly growth will die away in you. Then you will be indifferent to life and even grow to hate it. That's what I think.

(h) Can a Man judge his Fellow Creatures? Faith to the End.

Remember particularly that you cannot be a judge of anyone. For no one can judge a criminal until he recognises that he is just such a criminal as the man standing before him, and that he perhaps is more than all men to blame for that crime. When he understands that, he will be able to be a judge. Though that sounds absurd, it is true. If I had been righteous myself, perhaps there would have been no criminal standing before me. If you can take upon yourself the crime of the criminal your heart is judging, take it at once, suffer for him yourself, and let him go without reproach. And even if the law itself makes you his judge, act in the same spirit so far as possible, for he will go away and condemn himself more bitterly than you have done. If, after your kiss, he goes away untouched, mocking at you, do not let that be a stumbling-block to you. It shows his time has not yet come, but it will come in due course. And if it come not, no Matter; if not he, then another in his place will understand and suffer, and judge and condemn himself, and the truth will be fulfilled. Believe that, believe it without doubt; for in that lies all the hope and faith of the saints.
  Work without ceasing. If you remember in the night as you go to sleep, "I have not done what I ought to have done," rise up at once and do it. If the people around you are spiteful and callous and will not hear you, fall down before them and beg their forgiveness; for in truth you are to blame for their not wanting to hear you. And if you cannot speak to them in their bitterness, serve them in silence and in humility, never losing hope. If all men abandon you and even drive you away by force, then when you are left alone fall on the earth and kiss it, water it with your tears and it will bring forth fruit even though no one has seen or heard you in your solitude. Believe to the end, even if all men went astray and you were left the only one faithful; bring your offering even then and praise God in your loneliness. And if two of you are gathered together- then there is a whole world, a world of living love. Embrace each other tenderly and praise God, for if only in you two His truth has been fulfilled.
  If you sin yourself and grieve even unto death for your sins or for your sudden sin, then rejoice for others, rejoice for the righteous man, rejoice that if you have sinned, he is righteous and has not sinned.
  If the evil-doing of men moves you to indignation and overwhelming distress, even to a desire for vengeance on the evil-doers, shun above all things that feeling. Go at once and seek suffering for yourself, as though you were yourself guilty of that wrong. Accept that suffering and bear it and your heart will find comfort, and you will understand that you too are guilty, for you might have been a light to the evil-doers, even as the one man sinless, and you were not a light to them. If you had been a light, you would have lightened the path for others too, and the evil-doer might perhaps have been saved by your light from his sin. And even though your light was shining, yet you see men were not saved by it, hold firm and doubt not the power of the heavenly light. Believe that if they were not saved, they will be saved hereafter. And if they are not saved hereafter, then their sons will be saved, for your light will not die even when you are dead. The righteous man departs, but his light remains. Men are always saved after the death of the deliverer. Men reject their prophets and slay them, but they love their martyrs and honour those whom they have slain. You are working for the whole, are acting for the future. Seek no reward, for great is your reward on this earth: the spiritual joy which is only vouchsafed to the righteous man. Fear not the great nor the mighty, but be wise and ever serene. Know the measure, know the times, study that. When you are left alone, pray. Love to throw yourself on the earth and kiss it. Kiss the earth and love it with an unceasing, consuming love. Love all men, love everything. Seek that rapture and ecstasy. Water the earth with the tears of your joy and love those tears. Don't be ashamed of that ecstasy, prize it, for it is a gift of God and a great one; it is not given to many but only to the elect.

(i) Of Hell and Hell Fire, a Mystic Reflection.

Fathers and teachers, I ponder, "What is hell?" I maintain that it is the suffering of being unable to love. Once in infinite existence, immeasurable in time and space, a spiritual creature was given on his coming to earth the power of saying, "I am and I love." Once, only once, there was given him a moment of active lifting love, and for that was earthly life given him, and with it times and seasons. And that happy creature rejected the priceless gift, prized it and loved it not, scorned it and remained callous. Such a one, having left the earth, sees Abraham's bosom and talks with Abraham as we are told in the parable of the rich man and Lazarus, and beholds heaven and can go up to the Lord. But that is just his torment, to rise up to the Lord without ever having loved, to be brought close to those who have loved when he has despised their love. For he sees clearly and says to himself, "Now I have understanding, and though I now thirst to love, there will be nothing great, no sacrifice in my love, for my earthly life is over, and Abraham will not come even with a drop of living water (that is the gift of earthly active life) to cool the fiery thirst of spiritual love which burns in me now, though I despised it on earth; there is no more life for me and will be no more time! Even though I would gladly give my life for others, it can never be, for that life is passed which can be sacrificed for love, and now there is a gulf fixed between that life and this existence."
  They talk of hell fire in the material sense. I don't go into that mystery and I shun it. But I think if there were fire in material sense, they would be glad of it, for I imagine that in material agony, their still greater spiritual agony would be forgotten for a moment. Moreover, that spiritual agony cannot be taken from them, for that suffering is not external but within them. And if it could be taken from them, I think it would be bitterer still for the unhappy creatures. For even if the righteous in Paradise forgave them, beholding their torments, and called them up to heaven in their infinite love, they would only multiply their torments, for they would arouse in them still more keenly a flaming thirst for responsive, active and grateful love which is now impossible. In the timidity of my heart I imagine, however, that the very recognition of this impossibility would serve at last to console them. For accepting the love of the righteous together with the impossibility of repaying it, by this submissiveness and the effect of this humility, they will attain at last, as it were, to a certain semblance of that active love which they scorned in life, to something like its outward expression... I am sorry, friends and brothers, that I cannot express this clearly. But woe to those who have slain themselves on earth, woe to the suicides! I believe that there can be none more miserable than they. They tell us that it is a sin to pray for them and outwardly the Church, as it were, renounces them, but in my secret heart I believe that we may pray even for them. Love can never be an offence to Christ. For such as those I have prayed inwardly all my life, I confess it, fathers and teachers, and even now I pray for them every day.
  Oh, there are some who remain proud and fierce even in hell, in spite of their certain knowledge and contemplation of the absolute truth; there are some fearful ones who have given themselves over to Satan and his proud spirit entirely. For such, hell is voluntary and ever consuming; they are tortured by their own choice. For they have cursed themselves, cursing God and life. They live upon their vindictive pride like a starving man in the desert sucking blood out of his own body. But they are never satisfied, and they refuse forgiveness, they curse God Who calls them. They cannot behold the living God without hatred, and they cry out that the God of life should be annihilated, that God should destroy Himself and His own creation. And they will burn in the fire of their own wrath for ever and yearn for death and annihilation. But they will not attain to death....
  Here Alexey Fyodorovitch Karamazov's manuscript ends. I repeat, it is incomplete and fragmentary. Biographical details, for instance, cover only Father Zosima's earliest youth. Of his teaching and opinions we find brought together sayings evidently uttered on very different occasions. His utterances during the last few hours have not been kept separate from the rest, but their general character can be gathered from what we have in Alexey Fyodorovitch's manuscript.
  The elder's death came in the end quite unexpectedly. For although those who were gathered about him that last evening realised that his death was approaching, yet it was difficult to imagine that it would come so suddenly. On the contrary, his friends, as I observed already, seeing him that night apparently so cheerful and talkative, were convinced that there was at least a temporary change for the better in his condition. Even five minutes before his death, they said afterwards wonderingly, it was impossible to foresee it. He seemed suddenly to feel an acute pain in his chest, he turned pale and pressed his hands to his heart. All rose from their seats and hastened to him. But though suffering, he still looked at them with a smile, sank slowly from his chair on to his knees, then bowed his face to the ground, stretched out his arms and as though in joyful ecstasy, praying and kissing the ground, quietly and joyfully gave up his soul to God.
  The news of his death spread at once through the hermitage and reached the monastery. The nearest friends of the deceased and those whose duty it was from their position began to lay out the corpse according to the ancient ritual, and all the monks gathered together in the church. And before dawn the news of the death reached the town. By the morning all the town was talking of the event, and crowds were flocking from the town to the monastery. But this subject will be treated in the next book; I will only add here that before a day had passed something happened so unexpected, so strange, upsetting, and bewildering in its effect on the monks and the townspeople, that after all these years, that day of general suspense is still vividly remembered in the town.

Visual Studio Code Extensions I use

Dimitri Gielis - Thu, 2017-12-21 07:36
In my post List of the tools I use and why I use them I already mentioned I use Visual Studio Code as my main editor. Before I used different editors, but VSC replaced them all (core + extensions), so it's easier for me to just use one editor. Next to that, VSC is controlled by a company, gets monthly updates and there's a huge community behind it that provide extensions.

Here's a screenshot of the updates in version 1.19


These are the extensions I've installed:



If you want to search for those extensions this list might be easier:
  • DavidAnson.vscode-markdownlint
  • DotJoshJohnson.xml
  • HookyQR.minify
  • PKief.material-icon-theme
  • PeterJausovec.vscode-docker
  • Shan.code-settings-sync
  • alefragnani.project-manager
  • anseki.vscode-color
  • apng.orclapex-autocomplete
  • buianhthang.xml2json
  • christian-kohler.npm-intellisense
  • christian-kohler.path-intellisense
  • dbaeumer.vscode-eslint
  • donjayamanne.githistory
  • eamodio.gitlens
  • eg2.vscode-npm-script
  • formulahendry.auto-close-tag
  • formulahendry.code-runner
  • gerane.Theme-Blackboard
  • humao.rest-client
  • johnpapa.azure-functions-tools
  • kisstkondoros.vscode-codemetrics
  • mdickin.markdown-shortcuts
  • ms-vscode.azure-account
  • ms-vscode.Theme-MarkdownKit
  • ms-vscode.wordcount
  • msjsdiag.debugger-for-chrome
  • nodesource.vscode-for-node-js-development-pack
  • rafaelmaiolla.remote-vscode
  • robertohuertasm.vscode-icons
  • streetsidesoftware.code-spell-checker
  • wix.vscode-import-cost
  • xyz.plsql-language
If you install the Shell Command Line of VSC you can get the list of extensions by typing: code --list-extensions

In the Oracle APEX community many people are using Visual Studio Code, you can read a nice article of Morten; Using VS Code for PL/SQL development and Christope; Compile PL/SQL with VS Code using SSH. Adrian also created a nice extension for Oracle APEX. I highlighted the two extensions above.

Other editors that are often being used by Oracle APEX developers are Sublime Text and Atom, which have similar features than VSC, so if you read something nice that those editors can do, you can do it in Visual Studio Code most likely too e.g. Jorge's excellent post about Multi-Cursor Editing or Martin's truth about developing with Atom.


Just as a reminder for myself, As there were too many extensions to fit on one screen, I took two screenshots and glued the files together with following command:
convert -append vscode_1.png vscode_2.png vscode_extensions.png
If you wanted to glue them horizontally, you can use +append (I'm on a Mac).

Categories: Development

Top N biggest tables (with lobs, indexes and nested table)

XTended Oracle SQL - Fri, 2017-12-15 17:37

Script for SQL*Plus: https://github.com/xtender/xt_scripts/blob/master/tops/top_seg_by_size.sql

with 
  seg as (
     select 
       owner,segment_name
      ,segment_type
      ,tablespace_name
      ,sum(blocks) blocks
      ,sum(bytes)  bytes
     from dba_segments s
     where  segment_type not in (
       'TYPE2 UNDO'
      ,'ROLLBACK'
      ,'SYSTEM STATISTICS'
     )
     and segment_name not like 'BIN$%' --not in recyclebin
     and owner like '&owner_mask' -- you can specify schema here
     group by owner,segment_name,segment_type,tablespace_name
  )
 ,segs as (
     select 
       owner,segment_name
      ,case when segment_name like 'DR$%$%' then 'CTX INDEX' else segment_type end segment_type
      ,tablespace_name
      ,case 
         when segment_name like 'DR$%$%' 
           then (select table_owner||'.'||table_name from dba_indexes i where i.owner=s.owner and i.index_name = substr(segment_name,4,length(segment_name)-5))
         when segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
            then owner||'.'||segment_name
         when segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
            then (select i.table_owner||'.'||i.table_name from dba_indexes i where i.owner=s.owner and i.index_name=s.segment_name)
         when segment_type in ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
            then (select l.owner||'.'||l.TABLE_NAME from dba_lobs l where l.segment_name = s.segment_name and l.owner = s.owner)
         when segment_type = 'LOBINDEX'
            then (select l.owner||'.'||l.TABLE_NAME from dba_lobs l where l.index_name = s.segment_name and l.owner = s.owner)
         when segment_type = 'NESTED TABLE'
            then (select nt.owner||'.'||nt.parent_table_name from dba_nested_tables nt where nt.owner=s.owner and nt.table_name=s.segment_name)
         when segment_type = 'CLUSTER'
            then (select min(owner||'.'||table_name) from dba_tables t where t.owner=s.owner and t.cluster_name=s.segment_name and rownum=1)
       end table_name
      ,blocks
      ,bytes
     from seg s
  )
 ,so as (
     select
       segs.owner
      ,substr(segs.table_name,instr(segs.table_name,'.')+1) TABLE_NAME
      ,sum(segs.bytes)  total_bytes
      ,sum(segs.blocks) total_blocks
      ,sum(case when segs.segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION','NESTED TABLE','CLUSTER') then segs.bytes end) tab_size
      ,sum(case when segs.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION','CTX INDEX') then segs.bytes end) ind_size
      ,sum(case when segs.segment_type in ('CTX INDEX') then segs.bytes end) ctx_size
      ,sum(case when segs.segment_type in ('LOBSEGMENT','LOBINDEX','LOB PARTITION','LOB SUBPARTITION') then segs.bytes end) lob_size
     from segs
     group by owner,table_name
  )
 ,tops as (
     select 
           dense_rank()over (order by total_bytes desc) rnk
          ,so.*
     from so
  )
select *
from tops
where rnk<=50 -- top 50
/
Categories: Development

“Collection iterator pickler fetch”: pipelined vs simple table functions

XTended Oracle SQL - Tue, 2017-12-12 19:08

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it’s much better to create another pipelined function which will get and return its results through PIPE ROW().

A bit more details:

Assume we need to return collection “RESULT” from PL/SQL function into SQL query “select * from table(function_F(…))”.
If we create 2 similar functions: pipelined f_pipe and simple non-pipelined f_non_pipe,

create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
begin
  ...
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end f_pipe;
/
create or replace function f_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
begin
  ...
  return result;
end f_non_pipe;
/
Full functions definitions
create or replace type to_id_value as object (id int, value int)
/
create or replace type tt_id_value as table of to_id_value
/
create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end f_pipe;
/
create or replace function f_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  return result;
end f_non_pipe;
/
create or replace function f_pipe_for_nonpipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
begin
  result:=f_non_pipe(n);
  for i in 1..result.count loop
    pipe row (result(i));
  end loop;
end;
/
create or replace function f_udf_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end;
/
create or replace function f_udf_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  return result;
end;
/

[collapse]
Test queries

set echo on feed only timing on;
--alter session set optimizer_adaptive_plans=false;
--alter session set "_optimizer_use_feedback"=false;

select sum(id * value) s from table(f_pipe(&1));
select sum(id * value) s from table(f_non_pipe(&1));
select sum(id * value) s from table(f_pipe_for_nonpipe(&1));
select sum(id * value) s from table(f_udf_pipe(&1));
select sum(id * value) s from table(f_udf_non_pipe(&1));
with function f_inline_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
     return result;
end;
select sum(id * value) s from table(f_inline_non_pipe(&1));
/
set timing off echo off feed on;

[collapse]

we’ll find that the function with simple “return result” works at least twice slower than pipelined function:

Function 1 000 000 elements 100 000 elements F_PIPE 2.46 0.20 F_NON_PIPE 4.39 0.44 F_PIPE_FOR_NONPIPE 2.61 0.26 F_UDF_PIPE 2.06 0.20 F_UDF_NON_PIPE 4.46 0.44

I was really surprised that even “COLLECTION ITERATOR PICKLER FETCH” with F_PIPE_FOR_NONPIPE that gets result of F_NON_PIPE and returns it through PIPE ROW() works almost twice faster than F_NON_PIPE, so I decided to analyze it using stapflame by Frits Hoogland.

I added “dbms_lock.sleep(1)” into both of these function after collection generation, to compare the difference only between “pipe row” in loop and “return result”:

Modified functions

create or replace function f_pipe(n int) return tt_id_value pipelined 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  dbms_lock.sleep(1);
  for i in 1..n loop
    pipe row (result(i));
  end loop;
end f_pipe;
/
create or replace function f_non_pipe(n int) return tt_id_value 
as
  result tt_id_value;
  
  procedure gen is
  begin
     result:=tt_id_value();
     result.extend(n);
     for i in 1..n loop
        result(i):=to_id_value(i, 1);
     end loop;
  end;    
begin
  gen();
  dbms_lock.sleep(1);
  return result;
end f_non_pipe;
/

[collapse]

And stapflame showed that almost all overhead was consumed by the function “kgmpoa_Assign_Out_Arguments”:

I don’t know what this function is doing exactly, but we can see that oracle assign collection a bit later.
From other functions in this stack(pmucpkl, kopp2isize, kopp2colsize, kopp2atsize(attribute?), kopuadt) I suspect that is some type of preprocessiong of return arguments.
What do you think about it?

Full stapflame output:
stapflame_nonpipe
stapflame_pipe

Categories: Development

Using an "On Field Value Changes" Event in Oracle Visual Builder Cloud Service

Shay Shmeltzer - Mon, 2017-12-11 16:51

This entry is based on previous entries from John and Shray that deal with the same topic and provide the same type of solution. John's entry was created before VBCS provided the UI id for components, and Shray's entry is dealing with a more complex scenario that also involve fetching new data. So I figured I'll write my version here - mostly for my own future reference if I'll need to do this again.

The Goal is to show how you can modify the UI shown in a VBCS page in response to data changes in fields. For example how to hide or show a field based on the value of another field.

To do this, you need to hook into the HTML lifecycle of your VBCS page and subscribe to events in the UI. Then you code the changes you want to happen. Your gateway into manipulating/extending the HTML lifecycle in VBCS is the custom component available in the VBCS component palette. It provides a way to add your own HTML+JavaScript into an existing page.

The video below shows you the process (along with a couple of small mistakes along the route):

The basic steps to follow:

Find out the IDs of the business object field whose value changes you want to listen to. You'll also need to know the IDs of the UI component you want to manipulate - this is shown as the last piece of info in the property inspector when you click on a component. 

Once you have those you'll add a custom component into your page, and look up the observable that relates to the business object used in the page. This can be picked up from the "Generated Page Model (read-only)" section of the custom component and it will look something like : EmpEntityDetailArchetype

Next you are going to add a listener to your custom component model. Add it after the lines 

//the page view model this.pageViewModel = params.root;

your code would look similar to this:

this._listener = this.pageViewModel.Observables.EmpEntityDetailArchetype.item.ref2Job.currentIDSingle.subscribe(function (value) { if (value === "2") { $("#pair-currency-32717").show(); } else { $("#pair-currency-32717").hide(); } }); CustomComponentViewModel.prototype.dispose = function () { this._listener.dispose(); };

Where you will replace the following:

  • EmpEntityDetailArchetype  should be replaced with the observable for your page model.
  • ref2Job  should be replaced with the id of the column in the business object whose value you are monitoring.
  • pair-currency-32717 should be replaced with the id of the UI component you want to modify. (in our case show/hide the component).

You can of course do more than just show/hide a field with this approach.

Categories: Development

SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

XTended Oracle SQL - Sat, 2017-12-09 16:49

When “serveroutput” is enabled, SQL*Plus executes “BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;” after each command.
That’s why I don’t like when it is always enabled: it adds extra calls and round-trips and it is inconvenient when I want to get a plan of the last executed query:

SQL> set serverout on;
SQL> select * from dual;

D
-
X

SQL> select * from table(dbms_xplan.display_cursor('','','allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  9babjv8yq8ru3, child number 0

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

So usually I switch “serveroutput” on only if needed, but sometimes I can forget to enable it. In such cases I use very simple script that reads the output using dbms_output.get_lines and prints it using refcursor:
https://github.com/xtender/xt_scripts/blob/master/output_print.sql

When you set “serveroutput on“, SQL*Plus also executes “dbms_output.enable” and if you set “serverout off” it executes “dbms_output.disable”, that’s why my glogin.sql contains “call dbms_output.enable(1e6);” and you need to execute it after each “set serverout off” if you want to use this script.

Categories: Development

Bug with integer literals in PL/SQL

XTended Oracle SQL - Fri, 2017-12-08 15:04

This interesting question was posted on our russian forum yesterday:

We have a huge PL/SQL package and this simple function returns wrong result when it’s located at the end of package body:

create or replace package body PKGXXX as
  ...
  function ffff return number is
  nRes number;
  begin        
    nRes :=  268435456;
    return nRes;
  end;
end;
/

But it works fine in any of the following cases:
* replace 268435456 with power(2, 28), or
* replace 268435456 with small literal like 268, or
* move this function to the beginning of package body

The one of the interesting findings was that the returned value is equal to the one of literals in another function.
We can reproduce this bug even with an anonymous pl/sql block. The following test case uses 32768 integer literals from 1000001 to 1032768 and prints 5 other integers:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print: 100000=100000
    dbms_output.put_line('32766 ='||32766);
    dbms_output.put_line('32767 ='||32767);    
    dbms_output.put_line('32768 ='||32768);
    dbms_output.put_line('32769 ='||32769);
end;
Test code
declare
   c clob:='declare n number;begin'||chr(10);
   f varchar2(100):='n:=%s;'||chr(10);
   v varchar2(32767);
   n number:=32768;
begin
   for i in 1..n loop
      v:=v||utl_lms.format_message(f,to_char(1e7+i));
      if length(v)>30000 then
         c:=c||v;
         v:='';
      end if;
   end loop;
   v:=v||q'[
    dbms_output.put_line('100000='||100000);
    dbms_output.put_line('32766 ='||32766);
    dbms_output.put_line('32767 ='||32767);    
    dbms_output.put_line('32768 ='||32768);
    dbms_output.put_line('32769 ='||32769);
   end;
   ]';
   c:=c||v;
   execute immediate c;
end;
/

[collapse]
It produces the following output:

100000=10000001
32766 =32766
32767 =32767
32768 =10000002
32769 =10000003

This test case well demonstrates wrong results:
* instead of 100000 we get 10000001, which is the value from first line after “begin”, ie 1st integer literal in the code,
* for 32766 and 32767 oracle returns right values
* instead of 32768 (==32767+1) it returns 10000002, which is the integer from 2nd line, ie 2nd integer literal in the code,
* instead of 32769 (==32767+2) it returns 10000003, which is the integer from 3rd line, ie 3rd integer literal in the code
After several tests I can make a conclusion:

  • It doesn’t matter what plsql_optimize_level or plsql_code_type you set, was debug enabled or not, the behaviour is the same.
  • It seems that this is a kind of PL/SQL optimization: during parsing, oracle leaves integer literal in place if its value is in range -32768..32767 (16bit signed int), but if its value is out of this range, oracle adds this value into array of integers’ constants and replaces the value with the index of this element in this array. But because of index value overflow in cases when a count of such integer literals becomes larger than 32768, instead of Nth element of this array, oracle returns Mth element, where M is mod(N,32767).

So we can describe this behaviour using first test case:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print 100000, ie 32768th element of array, but prints 10000001
                                             -- where 10000001 is the 1st element of array (1==mod(32768,32767))
    dbms_output.put_line('32766 ='||32766);  -- these 2 lines print right values,
    dbms_output.put_line('32767 ='||32767);  -- because their values are in the range of -32768..32767
    dbms_output.put_line('32768 ='||32768);  -- this line contains 32769th element and prints 2nd element of array (2==mod(32769,32767))
    dbms_output.put_line('32769 ='||32769);  -- this line contains 32770th element and prints 3nd element of array (3==mod(32770,32767))
end;

The following query can help you to find objects which can potentially have this problem:

select
  s.owner,s.name,s.type
 ,sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)')) nums_count -- this regexp counts integer literals >= 30000
from dba_source s 
where 
    owner='&owner'
and type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
group by s.owner,s.name,s.type
having sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)'))>32767 -- filter only objects which have >=32767 integer literal

Workaround:
You may noticed that I wrote about INTEGER literals only, so the easiest workaround is to make them FLOAT – just add “.” to the end of each literal:

declare n number;
begin
  n:=1000001.;
  n:=1000002.;
  n:=1000003.;
   ...       
  n:=1032768.;
    dbms_output.put_line('100000='||100000.);
    dbms_output.put_line('32766 ='||32766.);
    dbms_output.put_line('32767 ='||32767.);    
    dbms_output.put_line('32768 ='||32768.);
    dbms_output.put_line('32769 ='||32769.);
end;
Fixed test cases

declare
   c clob:='declare n number;begin'||chr(10);
   f varchar2(100):='n:=%s.;'||chr(10); -- I've added here "."
   v varchar2(32767);
   n number:=32768;
begin
   for i in 1..n loop
      v:=v||utl_lms.format_message(f,to_char(1e7+i));
      if length(v)>30000 then
         c:=c||v;
         v:='';
      end if;
   end loop;
   v:=v||q'[
    dbms_output.put_line('100000='||100000.); -- .
    dbms_output.put_line('32766 ='||32766.);
    dbms_output.put_line('32767 ='||32767.);    
    dbms_output.put_line('32768 ='||32768.);
    dbms_output.put_line('32769 ='||32769.);
   end;
   ]';
   c:=c||v;
   execute immediate c;
end;
/

[collapse]
Categories: Development

Reverse engineer existing Oracle tables to Quick SQL

Dimitri Gielis - Fri, 2017-12-08 13:21
If you didn't hear about Oracle Quick SQL, it's time to read about it as it's something you have without knowing (it's a packaged app in Oracle APEX) and I believe you should start using :)

Quick SQL enables you to rapidly design and prototype data models using a markdown-like shorthand syntax that expands to standards-based Oracle SQL. You can easily create master detail relationships, check constraints, and even generate sample data.
In my blog post Create the Oracle database objects I go over the history how I created database objects and why I think Quick SQL is great and why I use it.

I guess most people typically use Quick SQL at the start of a new project, as it's the quickest way to create your data model and Oracle database objects. That is my primary use case too, but I started to use Quick SQL even on projects where database objects already exist.

In the project I'm currently involved in, the datamodel was generated by another tool, but as we iterate through the project, tables change, columns get renamed and added, row version were requested, triggers need to be made Oracle APEX aware...

Now we could do those changes manually, but I thought it made much more sense to create the data model in Quick SQL and use the features that come with Quick SQL. By clicking a checkbox we can include a Row version, Quick SQL generates the triggers automatically in an APEX aware form, we can generate as much sample data as we want by adding /insert and we can use all the other features that come with Quick SQL. For example when you want to include a history table in the future it's just another checkbox to click.


It's also easy to check-in the Quick SQL script into source control, together with the generated DDL.
If changes need to be done, we can adapt in Quick SQL and generate the DDL again and we see the changes immediately. It would be nice if Quick SQL could generate the ALTER statements too, but that's not the case yet. But it's easy enough to see the changes that were done by comparing the scripts in source control.

If you also want to reverse engineer an existing model into Quick SQL, here's a script that gives you a head start generating the markdown style format.


I tried the script on the Quick SQL data model itself - the result you see below:


Hopefully you see the benefit of using Quick SQL in existing projects too and the script helps you get there. Also Quick SQL gets frequent updates - in the upcoming release (17.3.4), which is already online, you can add a Security Group ID to every table (to make your app multi-tenant) and you can rename the audit columns to your own naming conventions.
Categories: Development

Oracle issues after upgrade to 12.2

XTended Oracle SQL - Thu, 2017-11-23 16:18

Sometimes it’s really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll about knowledge sharing is still active, I want to share a couple of bugs we have faced after upgrade to 12.2 (and one bug from Eric van Roon). I’m going to remove the bugs from this list when they become “public” or “fixed”.
If you want to add own findings into this list, you can add them into comments. To make this process easier, you can provide just symptomps, short description and the link to own post with details – I’ll add it just as a link.

th.c_symptomps { min-width:100px; max-width:100px; } th.c_description { min-width:200px } .c_links { min-width:150px; max-width:220px; } .c_links ul { margin: 0 0 5px 0 !important; -webkit-padding-start: 5px; } .c_links ul li { margin-left: 0px; -webkit-padding-start: 0px; } td.c_symptomps { font-size:12px;} td.c_description { font-size:12px;} td.c_links { font-size:10px;} .c_body td { vertical-align: text-top; } div.hints_wrapper { border-style: solid; border-width: 1px; padding: 2px; overflow: scroll !important; } div.hints_content { width: 1175px; min-width:1175px; padding: 2px; }


Symptomps Description Links Intermittent ORA-01483 After upgrade to 12.2 we started getting “ORA-01483: invalid length for DATE or NUMBER bind variable” in different applications using different oracle drivers.
Interestingly, that after reconnect, Oracle processes the same statement with same bind variables successfully.
Looking into errorstack dump (alter system set events ‘1483 trace name errorstack level 3, lifetime 5’;) we have found that oracle mixed up all values.
The only similar bug we found in MOS was “OCI Application Fails With ORA-01483/ORA-01461 When Inserting VARCHAR2 Field From 12.2 Database Using Database Link To Lower Database Version. (Doc ID 2309285.1)”, but it shows different symptomps.
Nevertheless, we have tried second workaround from this doc and it helped us.

Workaround:
set “_qkslvc_extended_bind_sz” to 0 and bounce the database. Doc ID 2309285.1 Periodically never ending SQL_ID f1xfww55nj0xp with SYNC(on commit) and SMALL_R_ROW enabled It seems that sometimes ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm, :flg, :smallr)
falls into infinite loop when you have ctx domain text indexes with SYNC(on commit) and SMALL_R_ROW option enabled

Workaround:
Recreate indexes as TRANSACTIONAL Near real-time indexes with SYNC(every…) option. ORA-07445: exception encountered: core dump [keswxCurNbRows()+61] with CURSOR() and DBMS_XMLGEN.getXML() Workaround:
use the following parameters:

  • “_optimizer_use_feedback”=false
  • “_optimizer_gather_feedback”=false
  • “_optimizer_dsdir_usage_control”=0
  • “_iut_enable”=false
“Non-public” Bug 26696342 ORA-01722 on quering ctx_preference_values After upgrade to Oracle 12.2.0.1 simple query “select * from ctx_preference_values” returns error ORA-01722: invalid number

details

If we look into the source of this view ctx_preference_values:

create or replace view ctxsys.ctx_preference_values as 
select /*+ ORDERED INDEX(dr$preference_value) */ 
u.name prv_owner 
,pre_name prv_preference 
,oat_name prv_attribute 
,decode(oat_datatype, 'B', decode(prv_value, 1, 'YES', 'NO'), 
nvl(oal_label, prv_value)) prv_value 
from 
sys."_BASE_USER" u 
,dr$preference 
,dr$preference_value 
,dr$object_attribute 
,dr$object_attribute_lov 
where prv_value = nvl(oal_value, prv_value) 
and oat_id = oal_oat_id (+) 
and oat_id = prv_oat_id 
and prv_pre_id = pre_id 
and pre_owner# = u.user#; 

and check PRV_VALUE from ctxsys.dr$preference_value:

select pre_name, prv_pre_id,prv_value 
from ctxsys.dr$preference 
, ctxsys.dr$preference_value 
, ctxsys.dr$object_attribute 
where prv_pre_id = pre_id 
and oat_id = prv_oat_id 
and oat_datatype='B' 
and not regexp_like(prv_value,'^\d*$') 
/ 
PRE_NAME                          PRV_PRE_ID PRV_VALUE
--------------------------------- ---------- ----------
CTXSYS.JSONREST_GERMAN_LEXER            1098 YES
CTXSYS.JSONREST_GERMAN_DIN_LEXER        1104 YES

we can find that the root cause of the problem is the line with decode(prv_value, 1, ‘YES’, ‘NO’)
and values ‘YES’ for preferences ‘CTXSYS.JSONREST_GERMAN_LEXER’, ‘CTXSYS.JSONREST_GERMAN_DIN_LEXER’.

Oracle tries to compare ‘YES’ with 1 in decode() and raises ORA-01722.

[collapse]

Workaround:

To add predicates to avoid these 2 preferences:

select * from ctx_preference_values
where prv_preference not in (‘CTXSYS.JSONREST_GERMAN_LEXER’,’CTXSYS.JSONREST_GERMAN_DIN_LEXER’); Wrong results with DETERMINISTIC functions in subquery factoring clause Description from Eric van Roon
Workaround:
alter session set “_plsql_cache_enable”=false; Examples

Categories: Development

Scaling Oracle using NVMe flash

Gerger Consulting - Tue, 2017-11-21 07:37
Attend the free webinar by storage expert Venky Nagapudi and learn how to improve the performance of your Oracle Database using new storage technologies such as NVMe flash. 

About the Webinar
Growth in users and data put an ever-increasing strain on transactional and analytics platforms. With many options available to scale platforms, what are the considerations and what are others choosing? Vexata’s VP of Product Management, Venky Nagapudi covers how the latest in storage side technologies, like NVMe flash, can deliver both vast improvements in performance as well as drive down costs and complexity of platforms. He will also cover key use cases where storage-side solutions delivered amazing results for Vexata’s customers.
In this webinar, you will:
  • Hear real-world performance scaling use cases.
  • Review the pros & cons of common scaling options.
  • See specific results of choosing a storage-side solution.


About the Presenter


Venky Nagapudi has 20 years experience in engineering and product management in the storage, networking and computer industries. Venky led product management at EMC and Applied Microsystems. Venky also held engineering leadership roles at Intel, Brocade and holds 10 patents with an MBA from Haas business school at UC Berkeley, an MSEE from North Carolina State University, and a BSEE from IIT Madras.

Sign up now.

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development