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 […]
SQL
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 […]
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 […]
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 […]
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 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 […]
LUHNs algoritm – Three ways with SQL and PL/SQL
TweetShareSharePin0 SharesHave you encountered LUHNs algorithm? I can almost guarantee it even if you’ve never heard the name before. It is part of all of our lives every single day. It is used to check that various numbers are correctly entered. From ID numbers for persons in Sweden, Greace, and Israel to credit card numbers and IMEI numbers and misc other things. It is a very simple checksum function not intended to be cryptographically secure hash function. Due to the […]
Quicktip: Don’t grant access to a synonym
TweetShareSharePin0 SharesI’ve seen this in a few places lately. When I ask the database guys 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 […]
Does recreating a view remove the privileges?
TweetShareSharePin0 SharesShort 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 […]
An old dog learns a new trick
TweetShare1SharePin1 SharesReports of this blogs death have been greatly exaggerated. It has been very quiet here though while I worked on getting the Swedish part of Miracle started. It is now rocking the Stockholm market so it’s time to get back into more geeky stuff. Talking of which. I have encountered Liquibase for database versioning time after time and always come to the conclusion that it is not what a DBA want to use. I recently took it out for a […]