In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction cannot simply call the procedure directly.
If you call a procedure interactively, you will have to explicitly commit or rollback the transaction because Oracle has no idea if you intend the procedure call to be a logical transaction or if you intend to compose a larger transaction involving multiple procedure calls. If you use dbms_scheduler
, dbms_scheduler
assumes that a job is a logical transaction and commits at the end of the job assuming it was successful (dbms_job
does the same thing).
Functions should not manipulate data in the first place. A function that manipulates data cannot be called from a SQL statement (barring the corner case where the function itself is declared to use an autonomous transaction which is almost never appropriate). The whole point of having both functions and procedures is that functions can be embedded in SQL statements and can be more freely granted to users because they do not change any data.
Strictly, yes, the FROM
clause of a SELECT
statement is not optional. The syntax for SQL-99 details the basic SELECT
statment, and the FROM
clause doesn't have any square brackets around it. That indicates the standard considers it non-optional:
SELECT [ DISTINCT | ALL ]
{Column expression [ AS name ]} [ ,... ] | *
FROM <Table reference> [ {,<Table reference>} ... ]
[ WHERE search condition ]
[ GROUP BY Columns [ HAVING condition ] ]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options |
INTO DUMPFILE 'file_name' |
INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]
In actual use, programmers and DBAs often find it useful to do things other than manipulate data in tables or manipulate tables and data structures. This type of thing is largely beyond the scope of the SQL standard, which is concerned with the data features more than the nuts and bolts of specific implementations. Whether we want to run SELECT getdate()
or SELECT 1
or SELECT DB_NAME()
(or whatever your dialect prefers), we don't actually want data from a table.
Oracle chooses to solve the standard and implementation discrepancy using a dummy table with the following effective definition:
CREATE TABLE DUAL (
DUMMY CHAR(1)
)
INSERT INTO DUAL (DUMMY) VALUES ('X')
Other RDBMSes essentially assume that a dummy table is used if no FROM
is specified.
The history of the DUAL table is on Wikipedia:
The DUAL table was created by Charles Weiss of Oracle corporation to
provide a table for joining in internal views:
I created the DUAL table as an underlying object in the Oracle Data
Dictionary. It was never meant to be seen itself, but instead used
inside a view that was expected to be queried. The idea was that you
could do a JOIN to the DUAL table and create two rows in the result
for every one row in your table. Then, by using GROUP BY, the
resulting join could be summarized to show the amount of storage for
the DATA extent and for the INDEX extent(s). The name, DUAL, seemed
apt for the process of creating a pair of rows from just one.
The original DUAL table had two rows in it (hence its name), but
subsequently it only had one row.
Best Answer
Your options are:
Assign the result to a dummy variable:
Select the result into a dummy variable:
Create an implicit cursor in a loop:
If the function does not need to be called from SQL you could change it into a procedure with an OUT parameter, though this still requires a "junk" variable it might look very slightly cleaner?
If you don't want to use this function though, why not just remove it from your code?
The only time I can think of there being a valid use-case for this sort of structure is when you're running identical code in two different schemas/on two different databases but this function is useless in one of them. If that's the case the function is still useful in the other and so you will want to do something with the result anyway.