How to Select an Optional Value

PublishedJanuary 22, 2020

Comments

No comments yet. Be the first to comment.

More from this blog

SQLcl host Command

Oracle SQLcl is the command line tool to connect to an Oracle database. One neat feature it has is the ability to call host commands (i.e call terminal commands directly from SQLcl). You can use any of the following to trigger a host command: host ho...

Jul 5, 20251 min read

Tips for APEX Application Constants

How to use Text Messages for Constants

Jan 22, 20244 min read

SQL Developer for VS Code

Last week SQL Developer for VS Code was released (direct link to VS Code extension here). If you're not using VS Code (VSC) I highly suggest you download and install as it's the de facto editor for developers (regardless of programming language). I'v...

Jan 22, 20243 min read

How to Generate a sitemap.xml in SQL

I recently had to generate a sitemap.xml for an public facing APEX application. The official sitemap protocol has the following example: <?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> ...

Jan 17, 20242 min read

What Happens to APEX Page Items that are Never Rendered?

Every APEX page item has a Server Side condition which controls whether the item is rendered on the page. They're various server side condition options and for the purpose of this article we'll just set it to Never (can do things like queries that re...

Jan 15, 20242 min read

TalkApex

321 posts

The problem

Suppose you need to find an employee's ID named SAM. If he doesn't exist in the table then insert a new employee record.

Side note: Technically you can use a merge statement for this functionality but want to highlight that a row may not be found.

declare l_empno emp.empno%type; begin select e.empno into l_empno from emp e where e.ename = 'SAM'; if l_empno is null then -- ... insert new emp end if; -- ... end; / -- Errors ORA-01403: no data found ORA-06512: at line 4 1. 00000 - "no data found"

An error is raised if the employee (SAM in this case) isn't found. Here are some options to get around this.

Explicit exception block

This is the most correct way to know if the value exists or not. Some don't like to use this approach as it can clutter code (due to additional lines of code), on the positive side it is very explicit.

declare l_empno emp.empno%type; begin begin select e.empno into l_empno from emp e where e.ename = 'SAM'; exception when no_data_found then l_empno := null; end; end; /

Using max

Using an aggregate function will always return a value even if no rows are returned. For example:

select nvl(max(dummy), 'still returns value') val from dual where 1=2 -- Always false ; VAL ------------- still returns value

Using this logic with the base example we can do:

declare l_empno emp.empno%type; begin select max(e.empno) into l_empno from emp e where e.ename = 'SAM'; end; /

I've used this in the past, however it comes with a caveat. If used on a column that isn't unique it will still return one value. If multiple values are present (ex: all emps who's job is MANAGER) it can lead to false positives.

Using left join

Similar to the previous option, selecting from dual (always returns a row) we can left join to optionally find the value.

declare l_empno emp.empno%type; begin select e.empno into l_empno from dual left join emp e on 1=1 and e.ename = 'SAM'; end; /

I prefer this approach compared to the max approach since if multiple values are possible an error is returned (which in most cases we want):

declare l_empno emp.empno%type; begin select e.empno into l_empno from dual left join emp e on 1=1 and e.job = 'MANAGER' -- Will return multiple values ; end; / -- Error ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5

How to Select an Optional Value

Comments

More from this blog

SQLcl host Command

Tips for APEX Application Constants

SQL Developer for VS Code

How to Generate a sitemap.xml in SQL

What Happens to APEX Page Items that are Never Rendered?

The problem

Explicit exception block

Using `max`

Using `left join`

Command Palette

Comments

More from this blog

The problem

Explicit exception block

Using max

Using left join

Using `max`

Using `left join`