sql
Inserting into Record with Long Character Fields
Most of us here follow the good practice of executing our SQL statements directly against the database before using them within an SQLExec in PeopleCode, a view or an SQL actions in App Engines. Even after following this, we might get hit in some situa…
A simple pipelined version of print_table
Tom Kyte’s print_table procedure, available on http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958#14442395195806 seems to be very popular and there exist tricky variations on the theme, for example the following nice …
SQL Analytics 101 – Row_Number()
Here is a simple example for when SQL Analytical Functions are simple yet useful.I wanted a basic 1,2,3,4 count so I could alternate colours in a report.select ename, sal, rownum rn ,mod(rownum,4) mod_rn ,mod(row_number() over (order by sal),4) mod_rna…
What’s your take on RDBMS and NoSQL?
My take is that application developers have belatedly but correctly concluded that an RDBMS is not the best tool for every application. For example, relational algebra, relational calculus, and SQL are not the best tools for graph problems. As another …
Restoring a dropped table
Catastrophe! You’ve just accidentally dropped a table which contained really rather important data. What to do? One thing you can do to recover the situation quickly (if you’re running 10g or later, that is) is to run the following command:…
NoSQL and Oracle, Sex and Marriage
In a post entitled “NoSQL and Oracle, Sex and Marriage,” Cary Millsap asks why NoSQL technologies are suddenly so popular. My take is that application developers have belatedly but correctly concluded that an RDBMS is not the best tool for every ap…
SQL Injection Risks
While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection at…
ORs, IN lists and LNNVL
I’ve previously written about manually rewriting an OR condition into a UNION ALL using LNNVL. This is a description of a performance issue observed in the real world from the optimizer coming up with a CONCATENATION operation against many child …
ORs, IN lists and LNNVL
I’ve previously written about manually rewriting an OR condition into a UNION ALL using LNNVL. This is a description of a performance issue observed in the real world from the optimizer coming up with a CONCATENATION operation against many child …
