In Oracle Apex, you are trying to create a PL/SQL function body to return an SQL query, but you are getting the error ORA-20999: PL/SQL function body did not return a value.
This is because you are definitely creating and returning an SQL query within an IF condition.
To resolve this error, you must create and return the SQL query in the ELSE part of the IF condition. Below is a correct example:
Declare
v_sql varchar2(500);
Begin
If :p2_status = 'A' then
v_sql := 'Select col1 d, col2 r from your_table where status = ''A''';
Else
v_sql := 'Select null d, null r from dual';
End If;
Return v_sql;
End;
Now, this method will work and will not give an error because we have written the SQL query for the ELSE part too.
Related Tutorial:
- Resolve “Session State Protection Violation” Error in Oracle Apex
An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.org and orclqa.com a question and answer forum for developers. You can connect with me on Facebook and Twitter.
It would appear that the glorious error ORA-20999 appears quite frequently in application code, as evidenced by the history of searches on the Internet for this error number. Unfortunately for the person searching endlessly for this error it’s not a standard Oracle offering. Instead, it’s a user-defined exception/error number to catch and report any of a plethora of unnamed Oracle exceptions, which makes finding a definitive answer for what this error represents practically impossible, as it means what the application programmer intended, which can, and does, vary between application programmers and applications. Let’s look at the valid range of user-definable error numbers/exceptions and try to clear the clouded air a bit.
Oracle offers a range of error numbers which are not assigned any standard Oracle error text and are not associated with any fixed Oracle exceptions; this range starts at 20000 and ends at 20999. Looking at a basic PL/SQL block to define and use some of these available error numbers it can be seen that these can either be quite useful or quite frustrating:
SQL> --
SQL> -- User defined errors are numbered
SQL> -- from 20000 to 20999 inclusive
SQL> --
SQL> --
SQL> -- Any time you see an error number
SQL> -- in that range it's an exception/error
SQL> -- defined by the user
SQL> --
SQL>
SQL> declare
2 ex20000 exception;
3 ex20459 exception;
4 ex20773 exception;
5 ex20999 exception; -- a very popular error number
6
7 pragma exception_init(ex20000, -20000);
8 pragma exception_init(ex20459, -20459);
9 pragma exception_init(ex20773, -20773);
10 pragma exception_init(ex20999, -20999);
11
12 begin
13 begin
14 begin
15 begin
16
17 --
18 -- Raising our first defined exception
19 --
20 raise ex20000;
21
22 exception
23 when ex20000 then
24
25 --
26 -- Return the first error code
27 -- and where we generated it
28 --
29 dbms_output.put(dbms_utility.format_error_stack);
30 dbms_output.put_line(' First error');
31 dbms_output.put_line(dbms_utility.format_error_backtrace);
32
33 end;
34
35 --
36 -- Raise the second defined error
37 --
38 raise ex20459;
39
40 exception
41 when ex20459 then
42
43 --
44 -- Return the error code
45 -- and where we generated it
46 --
47 dbms_output.put(dbms_utility.format_error_stack);
48 dbms_output.put_line(' Second error');
49 dbms_output.put_line(dbms_utility.format_error_backtrace);
50
51 end;
52
53 --
54 -- Raise third defined error
55 --
56 raise ex20773;
57
58 exception
59 when ex20773 then
60
61 --
62 -- Return the error code
63 -- and where we generated it
64 --
65 dbms_output.put(dbms_utility.format_error_stack);
66 dbms_output.put_line(' Third error');
67 dbms_output.put_line(dbms_utility.format_error_backtrace);
68
69 end;
70
71 --
72 -- Raise last defined error
73 --
74 raise ex20999;
75
76 exception
77 when ex20999 then
78
79 --
80 -- Return the error code
81 -- and where we generated it
82 --
83 dbms_output.put(dbms_utility.format_error_stack);
84 dbms_output.put_line(' Fourth error');
85 dbms_output.put_line(dbms_utility.format_error_backtrace);
86
87 end;
88 /
ORA-20000:
First error
ORA-06512: at line 20
ORA-20459:
Second error
ORA-06512: at line 38
ORA-20773:
Third error
ORA-06512: at line 56
ORA-20999:
Fourth error
ORA-06512: at line 74
PL/SQL procedure successfully completed.
SQL>
Not much useful information was presented here, so it’s uncertain what error or errors could have occurred to generate this progression of error messages. [The ORA-06512 error is an informative message as Oracle ‘unwinds’ the error stack and reports what it believes to be as the source of the actual error.] Such user-defined error numbers can be assigned to known Oracle errors, however:
SQL> --
SQL> -- Define error messages
SQL> -- which could be more descriptive
SQL> -- and exceptions which are
SQL> -- easier to handle
SQL> --
SQL>
SQL> declare
2 ex20206 exception;
3
4 pragma exception_init(ex20206, -2060); -- select for update error
5
6 begin
7
8 raise ex20206;
9
10 exception
11 when ex20206 then
12 raise_application_error(-20206, 'Attempt to lock distributed tables', true);
13
14 end;
15 /
declare
*
ERROR at line 1:
ORA-20206: Attempt to lock distributed tables
ORA-06512: at line 12
ORA-02060: select for update specified a join of distributed tables
SQL> declare
2
3 nolock exception;
4 pragma exception_init(nolock, -69);
5
6 begin
7 execute immediate 'alter table emp add myothercol number';
8 exception
9 when nolock then
10 raise_application_error(-20909, 'Thet ain''t allowed!!', true);
11 end;
12 /
declare
*
ERROR at line 1:
ORA-20909: Thet ain't allowed!!
ORA-06512: at line 10
ORA-00069: cannot acquire lock -- table locks disabled for EMP
SQL>
These examples are much clearer in what generated the exceptions and in the nature of the offending operations. [The ORA-00069 error mystically appears after someone has done this to a table:
SQL> alter table emp disable table lock;
Table altered.
SQL>
and someone else tries to lock that table with DDL or a call to ‘LOCK TABLE …’. The solution to that ‘problem’ is to do this:
SQL> alter table emp enable table lock;
Table altered.
SQL>
and then find out why someone else thought it necessary to disable locking on the affected table.]
Oracle does enforce the available error number range, as illustrated below, so existing, defined Oracle errors won’t be ‘stepped on’ inadvertently:
SQL> --
SQL> -- Attempt to raise
SQL> -- an exception using an error number
SQL> -- outside of the acceptable range
SQL> --
SQL>
SQL> begin
2 raise_application_error(-1400, 'Something strange occurred ...');
3
4 end;
5 /
begin
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -1400 is out of
range
ORA-06512: at line 2
SQL>
[An ORA-01400 error is generated when attempting to insert a NULL value into a column declared as NOT NULL.]
Apparently application programmers read their error messages and understand perfectly what has transpired, and that’s great for them:
“‘ORA-20618: Excessive flarpage’?!?!? What does THAT mean?!?!?”
“Oh, that means ‘don’t press the F8 key more than once on alternate Tuesdays’.”
“I never would have guessed …”
It isn’t good for the user community in general, however, as they are the ones seeing these ‘artificial’ error messages generated by the application code and, in many cases, have no idea what problems to report to Customer Service when they arise:
SQL>
SQL> --
SQL> -- This could possibly be a bit clearer ...
SQL> --
SQL>
SQL> declare
2 ex20773 exception;
3
4 pragma exception_init(ex20773, -1002); -- fetch out of sequence error
5
6 begin
7
8 raise ex20773;
9
10 exception
11 when ex20773 then
12 raise_application_error(-20773, 'Yew cain''t dew that!!!');
13
14 end;
15 /
declare
*
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12
SQL>
In cases where the users have no access to the developers (and the development team hasn’t obscured the package or procedure code with the wrap utility) it may be necessary to look at that code and see exactly what did generate the error. Of course this may ‘backfire’ as the actual error condition may be buried so deep in the code as to be nearly impossible to search for and the error message was generated by the ubiquitous catch-all ‘when others then …’ exception handler:
SQL>
SQL> --
SQL> -- This couldn't possibly be less informative
SQL> --
SQL>
SQL> declare
2 ex20773 exception;
3
4 pragma exception_init(ex20773, -1002); -- fetch out of sequence error
5
6 begin
7
8 raise ex20773;
9
10 exception
11 when others then
12 raise_application_error(-20773, 'Yew cain''t dew that!!!');
13
14 end;
15 /
declare
*
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12
SQL>
And, gee whiz, sometimes the developers decide to pass in the SQLCODE and SQLERRM values to RAISE_APPLICATION_ERROR, with disastrous results:
SQL>
SQL> --
SQL> -- Let's try this and see if it flies
SQL> --
SQL> -- we'll declare an exception then pass in the
SQL> -- generated SQLCODE to the
SQL> -- raise_application_error handler
SQL> --
SQL>
SQL> declare
2 ex21000 exception;
3
4 pragma exception_init(ex21000, -19);
5
6 begin
7 raise ex21000;
8
9 exception
10 when ex21000 then
11 raise_application_error(SQLCODE, SQLERRM);
12
13 end;
14 /
declare
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -19 is out of
range
ORA-06512: at line 11
SQL>
As mentioned earlier RAISE_APPLICATION_ERROR polices the error code values passed to it, and will unceremoniously complain when that value is out of range. [For those who are curious an ORA-00019 (which would generate the SQLCODE of -19) is a ‘maximum number of session licenses exceeded’ error.]
Possibly a ‘user-centered’ mindset on the part of the application programmers might better serve the end users, and maybe some testing should be done by people outside of the development community to verify that the error messages generated are truly useful to all parties involved.
I’ve blogged here about coding confusing text as error messages, so I won’t mention that topic again in this post. But maybe, just maybe, application programmers should read both posts and change their errant ways so the end users have something meaningful and useful as an error message and, as a result, their calls to the Help Desk aren’t exercises in futility.
Hope springs eternal.
FORECAST Job fails with error message «ORA-20999: General Failure «schedule start from date is null»»
calendar_today
Updated On:
Products
CA Automic Applications Manager (AM)
Issue/Introduction
Error Message :
ORA-20999: General Failure «schedule start from date is null»
After upgrading from version 8.x.x to version 9.x.x, FORECAST Job fails with an error similar to the following:
old 7: start_date := to_date(‘&start_date’,’yyyymmddhh24miss’);
new 7: start_date := to_date(‘20170927140319′,’yyyymmddhh24miss’);
old 8: end_date := to_date(‘&end_date’,’yyyymmddhh24miss’);
new 8: end_date := to_date(‘20171007235959′,’yyyymmddhh24miss’);
old 9: max_depth := ‘&max_depth’;
new 9: max_depth := ’20’;
old 10: min_units := ‘&min_units’;
new 10: min_units := ‘DAYS’;
Start=27 14:03:19 End=07 23:59:59
declare
*
ERROR at line 1:
ORA-20999: General Failure «schedule start from date is null»
ORA-06512: at line 11
Environment
OS Version: N/A
Cause
Root Cause: Since version 8.0.x, the ‘Reschedule From” date value for Weekly and Monthly Schedules is a required field. This error indicates that there are these types of schedules without the ‘Reschedule From” date value
Resolution
Use the below sql to find Schedules without a ‘Reschedule from date» value. For each result, edit the Schedule and add a ‘Reschedule from date’ value.
select so_module, aw_sch_name, aw_sch_start, aw_sch_frm_date, aw_sch_units from aw_module_sched, so_job_table
where aw_job_seq=so_job_seq and aw_sch_units in (-1, -2) and aw_sch_frm_date is null;
Fix Status: No Fix
Fix Version(s):
N/A
Additional Information
Workaround :
N/A
Feedback
thumb_up
Yes
thumb_down
No
So I’m in Code editor — SQL Query in Oracle Apex and when I write down the command
"select product_name from product;"
Oracle is answering with error : «ORA-20999: Wrong number of columns selected in the SQL query. See help of attribute for details». Does anybody know what is the problem here?
Littlefoot
133k15 gold badges36 silver badges58 bronze badges
asked Feb 7, 2019 at 12:44
8
It looks like a list of values.
If that’s so, it requires EXACTLY two columns to be returned: a display value, and a return value. Therefore, it might look like this:
select product_name d, --> display value
product_id r --> return value
from product
answered Feb 7, 2019 at 13:58
LittlefootLittlefoot
133k15 gold badges36 silver badges58 bronze badges
Select List Requires at least 2 Columns
- Return Column
- Display Column
If you want only one column you can do as follow.
select product_name,product_name "Product Name" from product;
Suraj Rao
29.4k11 gold badges94 silver badges103 bronze badges
answered Jan 1, 2022 at 13:11
It’s all started when during one night failure we were discussing options to restore/recover one pluggable database (PDB) and taking into account all ours limitations the best option was the restoration it on the test environment cluster and then clone it to the target production CDB.
Even if the story ended without restore/recover, I’ve decided to prepare the duplicate command that I will be able to use in stress condition to avoid unnecessary thinking. Here I’ve got to say, that we are using ZDLRA Oracle solution, that is forcing us to use RMAN recovery catalog.
To avoid any doubts of the restoration place to be performed the approach should be targetless, so we created an instance with the following names:
*.db_name='FAUX'
*.db_unique_name='F_AUX'They both differs from the target (db_name=FPROD; db_unique_name=fprod_s), so we are on the safe side here.
The duplicate command and RMAN commands look like:
hostname01> rman
connect auxiliary /
connect catalog /@ZDLRA
set dbid 888888888;
set DATABASE 'FPROD';
run {
allocate auxiliary channel ch1 device type SBT PARMS "SBT_LIBRARY=XXXXXXXXXXXXXX/libra.so,ENV=(XXXXXXXXXXXXXX)";
...
allocate auxiliary channel chd1 device type disk;
...
DUPLICATE target database to FAUX pluggable database PDBXXXXXXXXXXXXXX;
}From this point I didn’t expect any issue, however the RMAN script finished almost immediately with the following issue:
RMAN-06004: Oracle error from recovery catalog database: ORA-20999: Error: expected db_id = 999999999, but input database has db_id = 888888888Googling at that time didn’t give me any meaningful reasons for this error (one bug for the old version that appears in rare cases, so I won’t mention it), but the error text got me thinking if there is something in the RMAN recovery catalog with DBID 999999999. I guess RMAN recovery catalog is not the most popular technology and we were somehow forced to use it, so it also took some time to find the way to query it properly (selecting views from the DB itself isn’t the best approach IMHO), so there are two useful options:
hostname01> rman
RMAN> connect catalog /@ZDLRA
RMAN> list DB_UNIQUE_NAME all;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
5004444 O1 44444444 PRIMARY O_M
...and the other one:
hostname01> rman
RMAN> connect catalog /@ZDLRA
RMAN> set dbid 999999999
RMAN> LIST DB_UNIQUE_NAME OF DATABASE;
List of Databases
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
14155555 FB1 999999999 PRIMARY FB_S
14155555 FB1 999999999 STANDBY FB_P
14155555 FB1 999999999 STANDBY F_AUXHere it is. F_AUX wasn’t the normal db_unique_name for our DataGuard (DG) configuration, so we didn’t expect it to be present anywhere in the catalog, however it seems that previously someone from the team used it and was connected to such DB as a target, that finally registered it in the RMAN recovery catalog (it’s kind of a standard approach for many DBAs, but this is just a simple example why you should not restore database for cloning purpose using target connection).
So the final solution is to unregister DB_UNIQUE_NAME from the catalog:
RMAN> UNREGISTER DB_UNIQUE_NAME F_AUX;
database db_unique_name is "F_AUX", db_name is "FB1" and DBID is 999999999
Want to unregister the database with target db_unique_name (enter YES or NO)? yes
database with db_unique_name F_AUX unregistered from the recovery catalogRestoration using target pointing to FPROD seemed to be a workaround here and YES, the catalog worked properly, but we got another RMAN duplicate command error:
RMAN-05553: SYS objects in skipped tablespaces prevent duplicationSome SYS objects indeed we keep out of SYSTEM/SYSAUX, so if you don’t won’t list all needed tablespaces for all dozens of PDBS you have you can use targetless duplication where such check is not conducted.