It is a very common problem - how do you implement generic search over the table? By generic search I mean that users are provided with tons of different options that could be used in all possible permutations.
It leads to a standard problem - how do you make sure (more...)
I'm busy deriving file layouts from PL/SQL. Probably close to 100 file definitions...each of them slightly different, each of them defined in the code. Fun!
There are a mixture of types too, fixed width, csv, etc. Thankfully, I've read enough of the code now that it's relatively easy to (more...)
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
Just a simple reminder (I tend to forget things over the centuries) when using procedure DBMS_LOB.CONVERTTOCLOB with persistent lob locators:
don't forget to include the FOR UPDATE clause in the SELECT statement fetching (more...)
I've just noticed in one of LinkedIn PL/SQL forums the following question:
- Which one is faster and why SELECT 1 FROM DUAL
or SELECT ROWID FROM DUAL
Yes, it's pretty basic question, but it's interesting enough to be covered. The answer is pretty simple - do not use ROWID (more...)
I've been scratching my eyes out lately trying to reverse engineer
lots of PL/SQL.
One thing I've seen a lot of is calls to dbms_output.put_line
. Fortunately, I've seen some dbms_application_info.set_module
and other system calls too. But back to that first one.
1. When I used dbms_output
In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.
At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.
Let’s start with adding a (more...)
Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…
One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what (more...)
The latest version of Alexandria, the utility library for PL/SQL
, is now available for download
. The previous version has been downloaded more than 2,000 times in the last 8 months.
file contains a fairly detailed list of additions, enhancements and bug fixes. Below are some of the more noteworthy changes:
Improved installation script
You can now choose between installing the full library (close to 50 packages as of this version), by running the main /setup/install.sql
script, or you can install just the core set of packages via setup/install_core.sql
and then choose additional sets of (more...)
In every other profession having a 20 year experience is a good thing, in ours you are treated as a has-been who is kept around only to maintain the legacy system. This is insane.
Many companies tend to have mixed environments, with Oracle databases (and PL/SQL) running business-critical back-office systems, as well as Microsoft products for front-office applications such as email, word processing and collaboration/file sharing (ie Outlook, Office and Sharepoint), and of course the desktop operating system itself (Windows, with Internet Explorer, Internet Information Server, all integrated via Active Directory).
Working in these mixed environments means that you, as an Oracle PL/SQL developer, frequently need to work/integrate with various Microsoft technologies from PL/SQL.
Over the last couple of years, I've written a number of blog posts on this topic. This post is just (more...)
In regular SQL, you can use the DISTINCT qualifier with the COLLECT function.
Here it removes the duplicates for PETER and DAVID.SQL> select cast(collect(first_name) as sys.dbms_debug_vc2coll) 2 from HR.employees 3 where job_id = 'SA_REP' 4 and manager_id in (147,145);
CAST(COLLECT(FIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)--------------------------------------------------------------------------------DBMS_DEBUG_VC2COLL('Peter', 'David', 'Peter', 'Christopher', 'Nanette', 'Oliver', 'Clara', 'Danielle', 'Mattea', 'David', 'Sundar', 'Amit')
SQL>SQL> select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll) 2 from HR.employees 3 where job_id = 'SA_REP' 4 and manager_id in (147,145);
CAST(COLLECT(DISTINCTFIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)--------------------------------------------------------------------------------DBMS_DEBUG_VC2COLL('Amit', 'Christopher', 'Clara', 'Danielle', 'David', 'Mattea' (more...)
Last week the OTN SQL and PL/SQL Forum hosted of those threads which generate heat and insight without coming to a firm conclusion: this one was titled WHEN OTHERS is a bug
. Eventually Rahul, the OP, complained that he was as confused as ever. The problem is, his question asked for a proof of Tom Kyte's opinion that, well, that WHEN OTHERS is a bug. We can't proof an opinion, even an opinion from a well-respected source like Tom. All we can do is weigh in with our own opinions on the topic.
One of the most interesting things in (more...)
Extreme programming is old hat now, safe even. The world is ready for something new, something tougher, something that'll... break through. You know? . And here is what the world's been waiting for: Transgressive Programming.
The Transgressive Manifesto is quite short:
It's okay to use
The single underlying principle
is that we value willful controversy over mindless conformity.
I do have a serious point here. Even programmers who haven't read the original article (because they can't spell Dijkstra and so can't find it through Google) know that
GOTOs are "considered harmful". But as Marshall and Webber point out, (more...)
Tom Kyte on many new Database 12c features: http://t.co/z98lLxHH Via: (https://twitter.com/lucasjellema/status/257019546974683136) Filed under: DB Architecture, Oracle 12c, plsql Tagged: LinkedIn, New Features, OOW, Oracle 12c, Tom Kyte
We were recently looking for a way to easily anonymize some customer data, specifically bank account numbers.
Part of our testing mechanism is to verify that a bank account and sort-code combination is valid and we retrieve the bank details for that combination (using a commercial account lookup service). So we couldn't just replace the sort-code and/or account number with random numbers.
Conversely, replacing all account numbers with a handful of known, "safe" working values would lead to skewed data spreads, so that wouldn't work.
A better solution is to shuffle the existing data, moving the sort-code and account (more...)
One of things I don't like about Oracle SQL Developer is that if you are browsing and clicking on tables, it can automatically open the table on the right side of the IDE.There is a simple way to switch that off, just heard how to switch this feature off. Navigate to the Preferences settings:
Goto Database >> Objectviewer, and uncheck the tickbox labelled "Open Object on Single Click"
And that's it.
SQL vs NoSQL: Third International NoCOUG SQL & NoSQL Challenge sponsored by Pythian
As published in the 102nd issue of the NoCOUG Journal
THE WICKED WITCH OF THE WEST NEEDS HELP
BE IT KNOWN BY THESE PRESENTS that the Wicked Witch of the West needs your help to create
In my last blog post
I described using the DBMS_DATAPUMP and DBMS_JOB packages to automate database schema backups from the database itself, rather than using batch files to do so. I also mentioned that "since the files are written to the database server itself, you need some kind of process to move the files to another server or storage location, so the backups don't disappear along with the database if the database server itself is somehow lost or corrupted. [One] option is to upload the backup file from the database to some online ("cloud") storage service, such as Amazon S3. (more...)
Usually, database backups are the responsibility of DBAs, and not something that developers really care or think too much about. However, if you are a lone developer, or part of a small team that doesn't have the luxury of having a dedicated DBA, then you might have to deal with backups yourself.
There are several ways to make Oracle backups; what I will be concentrating on here is the "Data Pump Export" method. You may already be familiar with the command-line "expdp" command which allows you to create a dump (.dmp) file containing your database objects (schemas, tables, procedures, (more...)
Para quem usa o SQL Navigator 6.7 e tem notado que "às vezes" prende, tal parece ter origem na "feature History" que guarda os SQL (tec..) efetuados e permite uma pesquisa nos mesmos. É possivel efetuar uma "limpeza dos comandos efetuados" antes de uma data data ou toidos de modo a evitar que o ficheiro fique muito grande (parece que apartir de 600K é que o problema é encontrado) "Before you deleting your history file, you can have a backup if needed, the history file is in: xp: C:\Documents and Settings\UserName\Application Data\Quest Software\SQL Navigator 6.6.0\Unified (more...)