Why you might want to think twice about using INSERT ALL.
One of those things I knew and then forgot.
So, let’s say you’ve got three tables or a partitioned table or something like that.
Let’s use regional tables for simplicity.
drop table t1_r1;
drop table t1_r2;
drop table t1_r3;
create table t1_r1
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R1')));
create table t1_r2
(col1 varchar2(2) not null
,col2 number not (more...)
In a recent release that I’ve been working on, we had to drop and recreate some of the unique/primary key constraints (due to columns being added into the constraint) on some of our tables.
Prior to working in this team, I had only ever come across explicitly creating the indexes for a table, similar to:
create table test_explicit_index (col1 number,
create unique index col1_exp_pk on test_explicit_index (col1);
We can see the (more...)
Need help choosing sessions for #KScope16? Here are my Top 5 for the #BigData track
Occasionally, my students loose their network connection when copying their virtual machines. This article shows you how to rebuild your Internet connection.
The first step requires you to identify the port number on your host operating system, which is typically Windows OS or Mac OS X. You can find that by running the following search from a Mac OS X Terminal session or Windows OS Command session.
If you’re on the Mac OS X, you (more...)
The next question in my 2015 survey
was a high level look at what resources developers use to get through your day.
On a slightly side note, there was an interesting discussion on the science of preferred vs effective learning styles in this podcast
. It reminded me of my scuba diving course where we learnt the content using 5 different methods, which was a great way to ensure everyone understood how to survive in a (more...)
Last month I wrote a post about CSS performance
, including some performance test results.
I recently encountered this brilliant post on Medium
that describes some best practices for CSS.
While APEX does a lot of this for you, I think it's worth a read by all developers. Even applying basic naming conventions can make code easier to read and understand.
There was also a section on performance that described an issue that my previous (more...)
Ever had this problem where your button is offset to page items?
|Item / Button misalignment|
While the Universal Theme is awesome, it hasn't quite got everything right. I look forward to trying the updated version
of the theme in our existing applications.
APEX forum legend fac586 (a.k.a. Paul MacMillan) provided a simple solution here
to the Column CSS Classes
property for the button.
Little tips like this can be acquired (more...)
In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)
What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any
dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.
Here’s a sample web page that fails to run successfully …
In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.
create type integer_ntt as table of integer
select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list
While SQL Developer installs with a dbms_output view, some organizations close it before they distribute images or virtual machine (VM) instances. This post shows you how to re-enable the
Dbms Output view for SQL Developer.
- You need to open SQL Developer, which may look like this when the
DBMS_OUTPUT view isn’t visible.
- You need to click on the View menu option in SQL Developer and choose the Dbms Output dropdown menu (more...)
Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.
To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.
create table (more...)
If your Apex application import log shows something like this:
...PAGE 73: Transaction Lines Report
ERROR at line 1:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK)
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 16271
ORA-06512: at line 6
(this is on an Apex 4.2.4 instance)
This is due to a Saved Report on an Interactive Report that was included in the export, which conflicts with a different Saved Report in the target instance. The (more...)
My vacation from my blog is officially over. The question that I’m answering today is: How can you pass a set of non-sequential ID values to a function and return a result set?
You need to create three object types for this example. They are:
- a list of numbers
- a record structure, declared as an object type without methods
- a list of the record structure
These are the SQL commands to create the required data (more...)
In today’s world, where everything is at the tip of your fingers, why are your enterprise systems glued to your office desk? Imagine if you could access and operate your Oracle Forms/EBS systems from any location. What if your Oracle Forms system was so agile, so easy to adapt, that any business function you do in Oracle Forms could be included in a mobile application? What would you include in your mobile (more...)
I came across a tweet from a non-Oracle person I follow that should amuse many web developers:
One of the replies referred to "little bobby tables", eluding to a classic xkcd comic
about SQL injection.
Of course I had to make the correction that this was in fact Cross Site Scripting (XSS), not SQL injection. This post summarises
syntactical considerations (more...)
Learn how the Snowflake Elastic Data Warehouse improves performance of mixed workloads
Tim Hall of ORACLE-BASE spoke for a lot of people when he (yet again) wrote about Oracle Documentation links changing all the time
. There exists a method for a more permanent link to Oracle Documentation, but it can be a hazzle to create such a permalink involving manually searching HTML code of the doc source (see my comment
to Tim's blog post.)
Then I thought that this manual creation of permalink probably could be (more...)