SQL

SQL, PL/SQL, Oracle

Using package variables in SQL

TweetShareSharePin0 SharesI often want to use the constants defined in packages also in my SQL. It has in the past never worked and it for the most part still does not. If I have a a package like this to simplify my code for what department it is I’m looking at. This now allows using dept_info.c_accounting in PL/SQL instead of just hardcoding 10 and having to remember what department 10 is. This is how we usually use PL/SQL and especially […]

Database, SQL, Performance, DBA

Add hint to SQL you cannot modify

TweetShareSharePin0 SharesThe last post showed how to lock the plan for a SQL that switched plan every now and then. Another common issue one can encounter is that of a SQL that uses a suboptimal plan and for which you’d want it to use a different plan. The way to instruct the optimiser to use a different plan is to use a hint to cause it to change how it accesses the table. But often the SQL is in application […]

SQL, Performance, DBA

Lock the plan for a SQL

TweetShareSharePin0 SharesA SQL that sometimes just uses a plan you prefer it to not use is fairly common. There are always reasons for unstable plans, but more important than knowing the reason is often to make it stick to a certain plan. This has historically been done with a lots of ways from changing the SQL, adding hints, adding incorrect stats to influence the optimizer, using any of the techniques that came before SQL Plan Management (SPM). All of these […]

SQL

Set operators – Union, intersect and minus

TweetShareSharePin0 SharesYou’ve probably combined two queries with “union”, but have you looked at the different options for how to combine queries? The set operators are more combining result sets than they are combining queries even if we often think about it is combined queries. Thy need to return the same number of columns of the same datatypes. The lengths of the columns can differ but not the datatypes. Let’s start with a couple of tables and data so you have […]

Database, SQL

Longs in SQL

TweetShareSharePin0 SharesUsing long datatype is a problem we seldom face with new applications. But some old may still have it if they have not managed to convert to somethings easier to work with. But where I most often encounter it is in Oracles tables. The place that by far most often pops up its ugly head is high_value in *_tab_partitions. Just the other day a colleague was stuck on how to grab a portion of its content in SQL. “You […]

SQLcl, SQL Developer, Oracle Cloud, SQL, Performance

Grabbing sql_id for the SQL

TweetShareSharePin0 SharesI read Daniel’s recent blog about five ways to grab the sql_id and remembered reading Connor’s about getting it before you run a SQL. At the time it hit me that one ought to be able to use this to simplify the workflow. Often we run a SQL and then use some marker – typically in the form of a comment in the SQL – to look up the sql_id to then use for other lookups. It would of […]

SQL, PL/SQL

Accessing a website from the database

TweetShareSharePin0 SharesOne of the things people want more and more often is to somehow read from a web-resource using a database. It could be to just get the HTML off of a website or access REST via PL/SQL, or grab a JSON-file residing on a public website. No matter which it is we need to declare that it is a website we want the database user to reach. To have a simple example I’ll just show how to read the HTML off […]

SQL

Finding the query you never saved

TweetShareSharePin0 SharesYes, I admit it. I am sometimes not at all as structured as I would like to think. Recently I had worked on a query that had taken me many hours to get just right. When I was done I celebrated with a coffee break (I’m a Swede after all) and after it I started working on other pressing issues. A couple of weeks later that query is desperately needed as the same test needs be be done again and […]

SQL, Bug

The bug that wasn’t

TweetShareSharePin0 SharesThis is a short update to the presumed bug I wrote about recently in this post. After a few back and forth with Oracle support it turns out this is a well-known (to them) effect of using dbms_random in SQL. The base for this seems to be a bug report in 10g that was declared “not a bug”. You can read the bug report on MOS. It is also covered in this doc that references the same bug. It turns out that a […]

Bug, SQL

Bug with prefactoring and nondeterministic SQL

TweetShareSharePin0 SharesSometimes the results of a SQL is not at all what you expected. Most of the time it is a simple error you’ve made. Very rarely is it an issue that after hours of looking at it still looks like SQL does something wrong, But it still happens. This post is about one such situation that occurred last week. If you read the last blog post, you’ll recognize some of this. It turned up when I looked at transforming […]