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...)
Following up from the results on when you started learning APEX, here is question 2 from my 2015 survey.
Note that questions such as this offered the respondent to choose multiple selections, hence a count much higher than 192.
Q2: Which versions have you experienced?
I think it's fair to say that most of the 2.x respondents might also be part of the third that said they started prior to 2007.
I started in (more...)
I was lucky enough to attend a 'Let's Talk Oracle' session from Canberran Richard Foote
today diving into AWR.
After an early dad joke and before we got into his AWR collection, we gave this brain teaser.
This hurt many heads, including mine, and I also picked the wrong answer. Richard reports our group did well (more...)
Today while perusing posts in the #orclapex
hashtag, I came across this from Christina Moore.
She hasn't been blogging long but I love her style and format. I found this one on classic report templates
a great resource, though I wish the Oracle team would produce content like this to accompany the documentation, or as an extension to (more...)
Folks, OTNYathra, the OTN tour organized by AIOUG got over last week. Thanks to the Team AIOUG for putting in loads of efforts to make this event a grand success. A round of applause to Sai, Hari, and Veera for coordinating it so well. This was my second OTNYathra and I could easily sense the penetration AIOUG has … Continue reading
Do your self, your DW architecture, and your brain a favor and take time to attend the 3rd Annual World Wide Data Vault Consortium
Early last year I put the call out
to #orclapex developers, asking them to fill out some questions in survey (using a packaged APEX application, of course).
The results helped contribute to a presentation
I did Kscope15. Thank you to all those who responded. I thought I'd finally post some results, see if it can elicit further discussion, even of a casual nature.
Some of you asked me if I could post these results, so (more...)