oracle-databaseoracle-apexdual-table

In Oracle Apex, Dual table is not working


In SQL Commands section of Oracle's Apex app, when I run the following command, it just returns the sysdate function name, instead of the current date. Why?

SELECT sysdate from dual;
OR
SELECT sysdate from sys.dual;

Output: sysdate

Ref: https://stackoverflow.com/a/73772/1232087


Solution

  • Try:

    1. Using an alias

    Oracle APEX might be displaying the function name instead of executing it.

    SELECT sysdate AS current_date FROM dual;

    2. Making sure you’re in the right schema

    Oracle APEX's SQL commands section runs the queries in the schema that is assigned to your workspace. If DUAL is missing or is inaccessible, try:

    SELECT sysdate FROM SYS.DUAL;

    If this still won't work, make sure that your schema has access to DUAL.

    3. Using the TO_CHAR function

    If APEX is formatting the output incorrectly then explicitly converting it to a string format may work:

    SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') AS current_date FROM dual;

    In response to comments:
    Run the following queries:

    SELECT * FROM dual;

    or

    SELECT * FROM sys.dual;

    If you get an error like "table or view does not exist" then you don’t have access to DUAL.

    You can also check your user privileges

    Run this:

    SELECT * FROM all_tables WHERE table_name = 'DUAL';

    If no rows are returned then you may not have the required access.

    Check whether DUAL exists in your schema or not

    Run this:

    SELECT owner, table_name FROM all_tables WHERE table_name = 'DUAL';

    This will show which schema owns the DUAL table