Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these (more...)
|Text for bots: Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience.|
Consider this scenario of dynamic actions on change of P42_ITEM:
|Synchronous vs Asynchronous server calls|
One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".
For the examples I will use (more...)
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 …
1 2 3 4 5 6 7 8 9 10 11 (more...)
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 (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...)
This change can make a big impact in the execution time of PL/SQL as it's processed at compile time instead of interpreted at runtime.
Plug-ins can be wonderful black boxes and consumers may not care (more...)
There's more to PL/SQL programs than packages, but most of our code will live in packages. The PL/SQL Reference offers the following benefits of organising our code into packages:
Modularity - we encapsulate logically related components into an easy to understand structure.
Easier Application Design - we can start with the interface in the package specification and code the implementation later.
Hidden Implementation (more...)
SELECT SPLIT_PART('A;B;C;D', ';', 2);
- administration and governance
So we need to define at least one interface - packages - for each category in order that we can grant the appropriate access to different groups of users: read-only users, regular users, power users.
But there's more to Interface Segregation. This example is based on a procedure (more...)
The Principle Of Least AstonishmentAlso known as the Principle of Least Surprise, the rule is simple: programs should do what we expect them to (more...)
But developers are users too.
Another developer writing a program which calls a routine in my program is a user of my code (and, I must remember, six months after (more...)
Recently I got involved in a question on Temporal Validity Periods together with Chris Saxon, one of the askTom-answer team.
The question was along the lines of: "What if I have a single start date but two possible end dates. One of the end dates is filled automatically by a background proces (could be a job) while the other one is to signal that the end date is set manually by the user. Could you (more...)
IntroductionDesigning PL/SQL Programs
Principles and PatternsIntroducing the SOLID principles
Introducing the RCCASS principles
The Dependency Inversion Principle: a practical example
Tools and Techniques
Here is a simplified version of an ETL framework which uses SQL Types in a similar fashion to the approach described in my blog post here. The loading process is defined using an abstract non-instantiable Type like this:
create or replace type load_t force as object
( txn_date (more...)