My use case is integrating Oracle Apex with the public Mendeley REST API for Mendeley Catalog Search.
The idea was to build an (more...)
Read and monitor Oracle related blogs and news sources, all in one place.
My students asked if you could embed an
OFFSET x ROWS FETCH NEXT y ROWS ONLY clause in a SQL Server T-SQL user-defined function. The answer is no, it isn’t Oracle (yes, you can do that in Oracle Database 12c with an NDS statement). There’s an example in Chapter 2 of my Oracle Database 12c PL/SQL Programming book if you’re interested. I also demonstrate a different approach to SQL Server T-SQL table functions in (more...)
A question came up about how to verify dates from a string without throwing a casting error because of a non-conforming date. You can throw a number of exceptions, and I wrote a function to filter bad string formats like the
The first one is for a day between 1 and the last day of month, which is:
ORA-01847: day of month must be between 1 and last day of month
It seems impossible to raise an exception when handling no_data_needed.
create or replace function demo return sys.odciNumberList pipelined as begin pipe row(1); pipe row(1/0); exception when others then dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode); raise program_error; end; / firstname.lastname@example.org > select * from table(demo()); ERROR: ORA-06501: PL/SQL: program error ORA-06512: at "SOKRATES.DEMO", line 8 ORA-01476: divisor is equal to zero no rows selected in exception handler, sqlcode: -1476
Disclaimer: I’m not posting to make me look better, we’ve all written code that we’re later ashamed of, and I’m no different!
This is some code from a system I was maintaining some time ago. I’ve kept it since then because it illustrates a number of things NOT to do:
FUNCTION password_is_valid (in_password IN VARCHAR2) -- do NOT copy this code!!! ... RETURN VARCHAR2 IS l_valid VARCHAR2(1); l_sql VARCHAR2(32000); CURSOR cur_rules IS SELECT REPLACE(sql_expression ,'#PASSWORD#' ,'''' (more...)
My session on "Oracle 12c for Developers" is done.Afterwards someone asks the question:
What happens when you use DBMS_REDACT with a complete row update?My guess was that it would place the redacted data in the column, but I haven't tried it, so here goes:
create table emp
,to_char (abs (dbms_random.random)) credit_card
(object_schema => 'A'
,object_name => 'EMP'
,policy_name => 'Hide Creditcard'
The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. (more...)
While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.
When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that (more...)
A recent addition to my Oracle PL/SQL library is the book Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.
I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.
In the foreword of the book, Bryn Llewellyn writes:
The UTL_FILE database package is used to read from and write to operating system directories and files. By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter [...] Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.
I was aware that up to Oracle 11g, a PL/SQL program wasn’t allowed use an associative array in a SQL statement. This is what happens when I try to do it.
SQL> drop table test_array purge; Table dropped. SQL> create table test_array as 2 select level num_col from dual 3 connect by level <= 10; Table created. SQL> select * from test_array; NUM_COL ---------- 1 2 3 4 5 6 7 8 9 10 10 (more...)