Having played around with PL/SQL Developer, one of the frustrations I’ve found is that the extensibility of the tool relies on you being able to create dlls.
Not being a Microsofty, I’ve found it a bit limiting when compared with SQLDeveloper, which allows some fairly significant add-ons by the simple application of a bit of XML.
Don’t get me wrong, I quite like PL/SQL Developer, not least because it allows you to run SQL*Plus scripts pretty much unedited.
Now, I wanted a way of displaying the information held in my CRUD application without having to go to the trouble of typing the statement in each time.
The answer to my problem – the PL/SQL Developer custom report.
In PL/SQL Developer, go to File / New/ Report Window.
This is where you type in your SQL statement.
As with SQL*Plus, any runtime parameters are prefixed by an ‘&’.
The way you name and define these parameters is a little different however.
In my case, I want to give my variables a name that will show up when the user is prompted to enter them at runtime. Both of the variables are mandatory, and both should be converted to uppercase.
The end result is a query that looks like this :
SELECT object_owner, object_name, object_type,
create_flag, read_flag, update_flag, delete_flag
FROM crud_owner.db_crud
WHERE table_owner = '&<name="Table Owner" required="yes" uppercase="yes">'
AND table_name = '&<name="Table Name" required="yes" uppercase="yes">'
ORDER BY 1,2,3
There are a (bewildering) number of configuration options for the report, but I’m quite happy with the default output so I simply have to save the report in a file with a .rep extension.
Back at the main menu, select Tools / Configure Reports…
In the Configure Reports dialog box, click on the yellow folder icon and navigate to where you saved your .rep file.
Make sure that the Report as main menu item checkbox is checked then click OK.
When you next open the Reports menu, you should see your new report at the bottom of the list.
I don’t think I’ll ever learn to love PL/SQL Developer ( or any other Oracle IDE for that matter), but at least this sort of thing makes life a little more bearable.
It’s coming up to that time of year once more. Games are running out, permutations are being permutated, bottoms are beginning to squeak.
The question on everyone’s lips ( well, Simon’s actually) is, who will fill the Play-Off places in the Conference ?
For those of you not acquainted with the lower echelons of English Professional Football ( or Soccer, if you really must), The Conference is the fifth tier of English football and the pinnacle of non-League. Each year, the winners gain automatic promotion to the promised land of the Football League with those finishing second to fifth playing-off for the right to join them.
All of which gives me the perfect opportunity to forsake my old habits and bring my SQL bang up-to-date. (more…)
When Oracle first introduced ANSI syntax to become ANSI SQL 92 compliant, the general idea was that the traditional Oracle syntax would do exactly the same as this new fangled ANSI stuff.
You wouldn’t have to start coding LEFT INNER JOINs everywhere and you could pretty much go on your way unmolested by so much syntactic furniture.
This was the approach I’d followed quite happily for many years. Sure, I had moved away from DECODE toward CASE as I think the code tends to be more easy to follow, But all that extra typing to code an ANSI Join – a waste of valuable typing molecules….or so I thought.
Recently, I came across a situation where I needed to outer join a table to two other tables.
Now, in honour of the man who brought this to my attention – a Business Analyst…and an Irishman…on the day that Ireland beat England, yes, at cricket…(thanks Gavan)… (more…)
Recently, I came across one of those situations where you need to get some data from a Production instance and transform it in some way. The catch is that you only have read access on the database in question. All of the usual methods of extracting data are blocked by either an extremely long and tedious battle to get the required access or are prohibited by various security policies.
I will need to do this quite regularly, so it looks like I’ll have to write some slave SQL to extract the data and then load it into a non-production database, where I have more privileges.
Once again, I’m going to have to struggle to remember the new Oracle Q quote syntax.
But before all that, a brief history of quotes.
In the beginning, there was the escape character. In SQL*Plus, this happens to be a single quote … (more…)
During a recent difference of opinion, my girlfriend said to me “Ooohhh, you’re so logical!” The atmosphere was not improved by the fact that she had to then explain to me that this wasn’t a compliment.
All of which has nothing to do with the purpose of this post (although it may help to solve the mystery as to why I was single for so long).
The other day, someone asked me over to have a look at why they were getting an error when running a particular query. Now, as quizzes seem to be en vogue in the Oracle world at the moment ( have a look at that nice Mr Feuerstein’s site ), here’s a quick one for you. See if you can spot what’s wrong with this query (more…)
It’s been quite an eventful week. Deb has got her results back and is now officially a lady of “Distinction” (two of them, no less). Even Wales’ narrow defeat to England in the Rugby hasn’t put a dent in her good mood.
I, on the other hand, found myself doing my Marvin-the-paranoid-android-as-a-DBA impression the other day….”Synonyms. Loathe them or hate them, you can’t ignore them”.
Now, whilst synonyms definitely have their uses, they can be something of a double-edged sword.
The cause of this particular downbeat assessment of their merits was the fact that I’d deployed my CRUD tool on a new database, but it had failed to pick up some dependencies.
Let’s have a closer look at this issue and see how Oracle’s own DBMS_UTILITY copes with these circumstances. (more…)
If you spend you working life playing with databases, sooner or later, you’ll come across a situation where you want to have a running total of something or other ( usually money) included in the result set of a query.
For many years, this required quite a bit of messing about. Such techniques a covered nicely in this article by Kevin Meade.
Also mentioned by Kevin, and what I’m covering here, is the method of achieving this without too much fuss, using Oracle’s Analytic Functions. Yes, the syntax is a bit arcane on the face of it, but bear with me here. (more…)
Database Design – Denormalization, Codd and the Copa America
May 11, 2011Deb’s quite keen on the story of Goldilocks and the Three Bears ( although my version does end up with Goldilocks being charged with breaking and entering and criminal damage). How is this fairy tail linked to Database design ? Well, a good database should ideally be to Goldilock’s taste – not too fre-form and ad-hoc, not too rigid Third Normal Form, but just right.
To demonstrate this, we’re going to do a quick tour through the first three normal forms, give an airing to an ancient Geek joke, and relive past footballing glory. At this point Scottish readers will be relieved that I plan to demonstrate the concept of denormalization without referring to 1966. Instead, I will take as my example, the apogee of Columbian Footballing achievemnt, the 2001 Copa America. (more…)
Tags:check constraints, column comments, denormalization, dml triggers, join table, locigal database design, RI constraints, synthetic key, table comments, third normal form, views
Posted in Oracle, PL/SQL, SQL | 3 Comments »