Category: Uncategorized

May 15th, 2019 by mathias

I’ve seen this in a few places lately. When I ask the database gays there about it, they think this DDL would never work or “it does nothing but does not return an error”.

Here is an example, All of the following is performed by a DBA user. We create a table, let’s call it TB. It is owned by user A. User B is granted select privilege to A.TB. A synonym is created for user B pointing to A.TB, let’s call it SN. Now we grant select on B.SN to user C.

User C => (user B) B.SN => (user A) A.TB

C can log on and do a select from B.SN and see the data from A.TB. Life is good. However, now it is decided to remove the synonym B.SN so that C can no longer see the data in A.TB. It always work to just drop a view so why not?

Well… A view is an object that has much more weight than a synonym. A synonym is essentially a lightweight pointer. Think a file link in Unix/Linux or a shortcut in Windows.

Same thing in the database, the privilege does not belong to a synonym the way it does for a view. After revoking the view, a crafty user can still select data from A.TB.

One client though that by removing their API-schema all access from another system was shut down. It was not an some accesses had by mistake been coded as direct (as if user C wrote a select from A.TB instead of from B.SN)

How can this be helped? Use views when creating objects that should carry privileges of their own. Use the user that owns objects to run all DDL making it harder to do this by mistake, B cannot grant access on A.TB to C. Catch grants on synonym in peer reviews and add reports showing grants going further than you intend. In the example, only user B which is the API-user should have access to objects user A has created. So if there are any other privileges then SQL can be used to find issues like this.

Posted in Uncategorized

May 13th, 2019 by mathias

Are you using multi tenant with multiple containers and you’re not familiar with application containers?

It allows you to set up PDBs that belong to a CDB-like PDB that “owns” them. With that you can install applications (database objects) in the container, and then decide into which PDB to install, upgrade or patch that particular application,

So you could have an application that is a set of schemas with objects that belong together in a logical sense. Once you have the application created in the application container, you can go to an PDB and sync that application there to get it to the latest version.

It is a very nice feature you should take a look at if you are licensed for the multi tenant option. I like to think of this feature as kind of “EBR light”. It is for sure not a full EBR, but it is a very neat feature to install the next version in the application container, verify it there before you sync it into your PDB where the system is actually running.

Start with the Oracle documentation.

Posted in Uncategorized

May 13th, 2019 by mathias

Short answer: No

This blog post is pretty much just to document what seems to have been hard to find online.

At work there was a big rework of a liquibase project due to the perception that “create or replace view” on an existing view would make the granted privileges be dropped. That was of course not what happened.

The issue was that using liquibase the new version of the view was installed. Then the rollback was triggered and it executed the manual version of undoing the create view. Unfortunately the way this was done was by issuing a “drop view”. After that the update via Liquibase was executed again.

This ended up giving people the impression that replacing a view results in privileges being lost. They are not lost and once the conclusion was questioned it was easily proven that the database does not lose privileges on a replaced view, it does however drop them if you drop the view.

Running a few google searches I found some bad information, primarily on some large forums not too trafficked by Oracle or even database experts.

I just wanted to put this out in case it may help someone struggling with this in the future and hopefully someone finds this and not the erroneous forum posts about how databases work. They do not implement replacing a view as drop view and create view thus losing privileges…

The Internet is a funny and scary place.

Posted in Uncategorized

May 9th, 2018 by mathias

Yes, I’m finally back. The last post was written right before a complete period of offline before it was even scheduled to be published. That followed with slowly getting back. I have been fully recovered for a while but not gotten back enough to find time to blog. That changes now. So what causes almost a half years time to recover. Going out with the trash. Yes, it really is dangerous and should be avoided at all costs. What was really dangerous was the part of falling and acquiring a serious concussion. That was not made any better when I fell again a week later to get another severe concussion. I strongly advise against acquiring even mild concussions.

I’ll finish up this series about help texts with customized help text. The dedicated, in-line, modal, and non modal variants I’ve covered has all been declarative. Now we’ll take a look at what can be done if we want to not just modify with how it is shown to the user, but how it also looks.

For this blog I used version 5.1.4.00.08 of APEX on apex.oracle.com.

As I’ve stated before, my preferred way to deal with help text in APEX applications is to do it with inline help as the this blog post discussed. I have honestly never had to use this custom version in any real application. I imagine a lot can be done with just customizing CSS to make the help text show as one ants. But I guess for a customer that really want to control this, this option may come in handy.

There is a procedure provided with APEX for this. It is APEX_APPLICATION.HELP. it has this signature (taken from the linked documentation).

p_request              IN VARCHAR2 DEFAULT NULL,
p_flow_id              IN VARCHAR2 DEFAULT NULL,
p_flow_step_id         IN VARCHAR2 DEFAULT NULL,
p_show_item_help       IN VARCHAR2 DEFAULT 'YES',
p_show_regions         IN VARCHAR2 DEFAULT 'YES',
p_before_page_html     IN VARCHAR2 DEFAULT '<p>',
p_after_page_html      IN VARCHAR2 DEFAULT NULL,
p_before_region_html   IN VARCHAR2 DEFAULT NULL,
p_after_region_html    IN VARCHAR2 DEFAULT'</td></tr></table></p>',
p_before_prompt_html   IN VARCHAR2 DEFAULT '<p><b>',
p_after_prompt_html    IN VARCHAR2 DEFAULT '</b></p>:&nbsp;',
p_before_item_html     IN VARCHAR2 DEFAULT NULL, 
p_after_item_html      IN VARCHAR2 DEFAULT NULL

p_request is not used.

p_flow_id and p_step_id is application id and page id respectively.

p_show_item_help controls if help about individual items should be shown.

P_show_regions controls if information about each regions should be shown.

The remaining parameters have one before and one after parameter of each kind. The control what html we want to have injected before or after a certain element on the page.

p_xxxxxx_page_html controls what is put before the page info and what is put after the whole help text (the page info is considered to include regions and items).

The remaining parameters are conditional, such that the content is ignored when one of p_show_item_info and p_show_regions is set to something other than ‘YES’.

p_show_reqions has to be YES for these to be considered:

  • p_before_region_html
  • p_after_region_html

They control what HTML to inject before and after each region help text.

p_show_item_help has top be yes for these to be considered:

  • p_before_promt_html
  • p_after_prompt_html
  • p_before_item_html
  • p_after_item_html

The prompt ones controls what is injected before and after the label for each page item. The item ones control what is injected before and after the actual help text for each item.

Rather than describing how to build this, I recommend that you look at it on my demo application. The demo application allows you to play with all the above parameters and see the effect it has.

Watch it live

Take a minute and check out this live in my demo application to see for yourself how this can be used. Log in with demo/demo.

Posted in Uncategorized

December 27th, 2017 by mathias

Setting up help text in APEX is not hard but I often see it not done at all or implemented using regions with static content and then toggled on or off. That is unfortunate when there is declarative support for providing help texts.

In the last post I show how to set up a specific page to be the landing page for showing help text for any page in the application. In this post I’ll show how to get help text displayed inline with a page the user is on.

For this blog I used version 5.1.4.00.08 of APEX on apex.oracle.com. The following has been the same since at least release 3.2 of Apex while where and how you enter the needed properties may not be identical in previous and future releases. I don’t think it has changed much over the years.

Show help in-line

It is often preferable to be able to see the help on the page you want to know how it works. If you have it on another page like I showed in the last post, you often end up going there reading a bit and then go back to look only to repeat that a number of times.

What if you could see the help on the page you’re on? Sometimes that is much better.

Setting up the global page

The basis for this is using the global page to have a halp region be available on every page in the application (unless you restrict it).

Create a global page if you do not have one in your application. Then add a “help text region” to the Content body of the global page. Name it “Info” and a sequence of 0 to make sure it is the very first region in the content body.

If you run the application now, every page shows the help text for it’s page. So it is already working, but we want the user to select when it is to be displayed.

We will use a page item to define when the help is shown and when it is not.

Creating the page item

Lets create a page item “AI_SHOW_HELP” to let us control when help is shown.

Go to the shared objects:

  • Click on “Shared Components”.
  • Click on “Application Items”.
  • Click Create
  • Name = AI_SHOW_HELP
  • Session State Protection = Checksum Required – Session Level
  • Click Create Applöication Item

Set default value for AI_SHOW_HELP

To make sure the application shows with help not being displayed, we’ll set up an application computation to default the application item to N.

  • Click on “Shared Components”.
  • Click on “Application Computations”.
  • Click Create
  • Computation Item = AI_SHOW_HELP
  • Computation Point = On New Instance (New Session)
  • Computation Type = Static Assignment
  • Computation = N
  • Click Create Computation

Set a condition for when to show the help text region

With the Application Item in place and dafaulting it to not show help, we just need to set the condition on the help text region on the global page to only show when AI_SHOW_HELP has a value different from N. To make sure it is only shown when we have requrésted it, we check for it being Y rather than just not being N.

  • Open the global page in the page designer (editing the page)
  • Click on the help text region (named Info above)
  • Scroll down to  Server Side Condition group among the regions properties
  • Type = “Item = Value”
  • Item = AI_SHOW_HELP
  • Value = Y
  • Click Save

If you now run the application, the help will no longer show on any of your pages. The reason is of course that we default AI_SHOW_HELP to “N” while only showing the help text when it is set to “Y” without any means to set it to “Y”.

What we need now is a way for the user to toggle AI_SHOW_HELP between “Y” and “N”

Navigation bar entry to toggle help on or off

The way to create a toggle in the navigation bar is to have two entries and only show the one the reverses the current selection, i.e. if AI_SHOW_HELP is “Y” then let it be “N” and vice versa.

Head back to shared components and click on “Navigation Bar List”, and click on “Navigation Bar List” in the report to go to editing the entries in the navigation bar.

  • Click “Create Entry”
  • Sequence = 30
  • List Entry Label = Show Help
  • Target Type = Page in this Application
  • Target = &APP_PAGE_ID.
  • Set these items = AI_SHOW_HELP
  • With these values = Y
  • Conditions = Value of Item/Column in Expression 1 is != Expression 2
  • Expression 1 = AI_SHOW_HELP
  • Expression 2 = Y
  • Click Create List Entry

Now we have a navigation bar entry (high up right where the logout link is) that toggles help on and that is only shown when help is not shown.

Run the application and click “Show Help” in the navigation bar to see the inline help being visible again. There is then – yet – no means to turn off the help.

Now that it works we need to create one more entry that does the reverse. It is shown when help is visible to allow the user to hide the help again.

Return to the edit window and lets set up a reverse navigation bar entry of the one we just created.

The easy way to do it is to click the copy icon on the right side on the row for “Show Help”. Do that and enter the following values.

  • Sequence = 40
  • New List Entry Label = Hide Help
  • Click Copy List Entry

Now lets edit the few things we need.

  • Click “Hide Help” in the report over navigation bar entries.
  • In Target section, set “With these values” = N
  • In Conditions, set “Expression 2” = N
  • Click Apply Changes

That is it, the application now has a toggle between Show Help and Hide Help to toggle showing help about pages inline in the application. Note that it is an application wide setting, so once on it remains active until the user turns it off. Thus, if you turn it on and move around in the application, the help will show on every page until you decide to turn it off and not show help anymore.

Watch it live

As I said in the last post I have set up a demo-app I’ll use to show the effects when I blog about things APEX where it makes sense to have a an app to show the feature. For the above, take a look at it. Log in with demo/demo.

 

Posted in Uncategorized

December 20th, 2017 by mathias

Setting up help text in APEX is not hard but I often see it not done at all or implemented using regions with static content and then toggled on or off. That is unfortunate when there is declarative support for providing help texts.

In this post I’ll show how to set up a specific page to be the landing page for showing help text for any page in the application. It is the precursor to the next post where I’ll take it a step further and show how to get help text displayed inline with a page the user is on.

For this blog I used version 5.1.4.00.08 of APEX on apex.oracle.com. The following has been the same since at least release 3.2 of Apex while where and how you enter the needed properties may not be identical in previous and future releases. I don’t think it has changed much over the years.

Show help using another page

The following information is to a large extent a click stream version of Oracles official documentation.

The number in the parenthesis are example page numbers just to make sure there is no confusion of what page is referenced. It is the page numbers you’d end up with in a brand new application

Set up two pages

Create a blank page (2) and then add a help text region to the content body of the page. This is the page that will be used to display the halp for any page in the application.

Create another blank page (3) and scroll down to the bottom and fill in help text about the page in the “Help Text” property.

Help text location

Now we have everything needed in place, we just have to add any means of navigation to get the help page (2) to be loaded with the helptext of this page (3). Typically this is done with a link in the navigation bar as we’ll see later, but it is often easier to just try out navigation with a plain button.

Add Navigation

Add a button and label it OtherPage. Set the target to be the page number of the help page you just created (2). Set the request (in 5.1 under the advanced catagory) to “&APP_PAGE_ID.”.

Run the page (3) and click the button. You will be sent to your help page (2) where the helptext you entered for the page (3).

If you add items on your page (3) the help text for those will also be shown on your help page. You will however not want to create a help button on every page in your application. It would both wast real estate on your page as well as time to set it up on every single page.

Navigation bar

To make the help for every page in your application be displayed with no additional work per page other than writing the help text, let’s set up an entry on the navigation bar.

Go to shared components and clock on Navigation Bar List, and then on Desktop Navigation Bar. Click Create Entry and set:

  • Sequence = 20
  • List Entry Label = Help Page
  • Target Type = Page in this Application
  • Page = Your help page (2)
  • Request = &APP_PAGE_ID.

Editing navbar for help page

If you run the application now you will see “Help Page” up in the navigation bar, and clicking it takes has the same exact result as the button. It navigates to he help page (3) and shows the help text for your page (2).

Now any new page you create for which you write help text will let the user clock on the navigation bar and get the help text with no extra effort from you.

Watch it live

I have just set up a demo-app I’ll use to show the effects when I blog about things APEX where it makes sense to have a an app to show the feature. For the above, take a look at it. Log in with demo/demo.

 

Posted in APEX, Uncategorized

October 1st, 2017 by mathias

So every year before and during Oracle Open World we all complain about how data and database is brushed to the side.

This year it is the feature. It is the one thing the big sign on Moscone West screams. “The Autonomous Database”

Here is a picture from showing it. It is all about database.

Every year there is talk about how Oracle needs to return to data and databases. Now that they do, I think we should be very happy even if we suspect the actual feature isn’t what we would have requested.

Any day Oracle talks about database is a day they’re not spending on forgetting it.

Posted in Uncategorized

March 14th, 2017 by mathias

As I wrote a while back I was accepted to speak both at the User Group Leader summit at DOAG16 and at RMOUG Training Days.

The first one was a short presentation where I talked about a large bug in Oracle security and the need to patch and upgrade to not have that exposure. It was great fun as it was limited to a four-minute talk. I learned a lot from preparing for it as that short time allows for no questions and no spur of the moment comments. Each slide has to be carefully timed to make sure the time is enough for all slides.

The last one was about a customer case where a severe performance issue was handled where I talk about all the assumptions we challenged in the process of resolving it. The job took 36 hours and it could only use 8 and soon the amount of work was expected to double. It ended up taking just a few minutes when we were done. Part regular tuning and part using the “magic” of the EXADATA.

While the talk ended up having few attendees – competing with Maria Colgan and Graham Wood is tough – it was a great experience. I have not presented at a conference this big before. Training Days is also a conference that scares me to present at. I lived in Denver and my respect for the conference, the presenters and the quality expected is almost at an unhealthy level. So being there to present was a way to slay a dragon of mine. I had a great time at the conference and I enjoyed presenting. Even though I did not have an oversubscribed room, those who came seems to have enjoyed the session as I was rewarded with a 9.0 rating for the talk.

If you’re thinking about maybe going to Training Days next year, my advice is to do it. It is a great conference and it is extremely well-organized. It is small enough to know the layout and the rooms fast, while still being big enough to have a lot of great talks to choose from every session. There were several where I wanted to go to three and I still regret having missed those where 2-3 fantastic sessions were held at the same time.

I really liked the effort made to make the biggest names available and approachable by everyone by having them have their own tables at lunch time and letting people sit at the table where one of the persons they respect the most sits. I really enjoyed my lunch at Cary Millsap’s table. It was a great group and a very inspiring discussion about performance and discussing old battles in the field.

It is far away, but I’ll be back. I had a blast.

Posted in Uncategorized

March 11th, 2017 by mathias

I have for a few years created a play environment in a similar way. Getting a database installed in a VM is not too hard. But I found that I did it fairly often and not always in the same way and not always without having to resolve problems I know I had solved before but since forgotten the solution for.

Right, write everything down. Yes, it was time for that.

So I now have my notes in order so I can run through the creation of a new VM when a new version of any software I want to have installed becomes available.

Since 12c is out and more importantly 12.2 was released just a week ago it was time to include multitennant in the model.

This is what I want to have in my VM  I use to test and evaluate Oracle Database stuff.

Thus, with this I have three PDBs simulating dev, test , and prod when I want to model  migrating things through an environment. Each of them has APEX so that too can be tested for migrating through an environment.

Including ORDS and glassfish makes then installation complete and not only allows using all features of APEX but also prepares the VM to be used for REST services.

For database work this is to me a very complete VM to test and learn new things with.

I’d recommend that you do the same for what you want. There is the Oracle Developer VM you can get and lots of other ones too. But I find you learn a lot by creating your own. If you install yourself you also get it to be set up the way you want it.

The one I create, I set up as a template and then I clone it to have one that I con make changes to. That allows me to have a pristine version to clone from. Yes, I could use snapshots to achieve much of the same. Having a separate template makes maintaining it much easier in my experience.

When you do install, remember to make a snapshot at least after each individual product has been installed and configured. You will end up going back and redoing it when you run into problems or think of improvements to your install.

The template I create I share with friends and coworkers who may not want to do their own installation. While installing teaches you lots, for some it is not worth it. They just want to play. For those people using mine or the Oracle App Development VM is a quick way to do that. Oracle provides a lot of different ones for that or when you want to try something new. All of them can be found here.

To finish up, I just have to link to Tim Hall. He has a fantastic number of articles and for this stuff his writeup of installing Oracle DB 12.2. When I find a problem it is most of the time already covered in his article. Even more impressing is that he gets it out before you even get your VirtualBox  loaded. I think he had the 12.2 version of this updated and posted within four hours of the release of 12.2.

GO! Create your own VM and get hacking. 12.2 has too much cool stuff in it to not keep you hacking for a long time.

Make hacking a legal sport. 🙂

Posted in Uncategorized

November 6th, 2016 by mathias

So I subscribe to the idea that the only way to improve is to dive in on the deep end. Sink or swim.

With that in mind I sent in an abstract to RMOUG and actually got it accepted. Now, this is a conference I used to attend every year when I lived in Denver. I know the quality they have in most presentations nad I know that lots of people with “important” names in the community attends. I have to up my game and give myself a chance to be embarrassed. It scares and motivates me in equal amounts.

Talking about that feeling, I watched the quick presentation at the OOW 2016 – “EOUC Database ACES Share Their Favorite Database Things”. I was impressed by how they managed to keep those presentations to just five minutes and still get a great message across. I figured that would be a great thing to practice. I’m going to the DOAG conference in a week and before it starts there is a day for user group leaders. I’ll attend that and on the agenda there is a 30-minute slot with 4-minute presentations. Even less than they gave the ACED presenters at OOW. Not really knowing what I’m getting myself into, I tossed my name into that ring also.

Hopefully I get to make a try at that too. There will be just a few days of prep. Maybe that is just as well so I do not find time to chicken out. Standing in front of all the group leaders in Europe with just a couple of evenings to prep will be nerve-wracking. But again, if one wants to improve, one has to test those wings.

Hopefully I’ll get through those without too many scars. I look forward tremendously to both conferences for the meetings as well as for the chance at possibly present at both conferences.

Posted in Uncategorized