Oracle – How to get Time Zone Region from SQLPLUS

oracleoracle11gsqlplus

I am using Oracle Developer and Oracle 11g (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production)

select SESSIONTIMEZONE from DUAL;

Result in SQLPLUS/system_config_tbl_load.sql: -4:00
Result in SQL Developer: America/New_York

gives different results in SQL Developer and SQLPLUS.
I want to see the region in SQLPLUS.

Additionally I have to do this for multiple locations and do not have the option of changing the Time Zone using "ALTER SESSION SET time_zone = 'America/New_York'"

Best Answer

I assume SQL Developer makes an implict ALTER SESSION SET time_zone=... when you open a new connection.

I see several solutions:

Define environment variable TZ on client host
Create a database trigger and set timezone accordingly
Change the timezone of the database - works unless any user sets his personal timezone

Database Trigger:

CREATE OR REPLACE TRIGGER LOG_T_LOGON 
   AFTER LOGON ON DATABASE
DECLARE

BEGIN
   IF ora_login_user IS NULL THEN 
      RETURN;
   END IF;
   IF ora_login_user IN (...) THEN -- Prevent to be executed for EACH user
      execute immediate 'ALTER SESSION SET time_zone = ''America/New_York''';
   END IF;
END;

END;

Related Solutions

Sql – ORA-00932: inconsistent datatypes: expected TIMESTAMP got TIMESTAMP

You need an object constructor for the view. The table doesn't contain two columns in each row. It contains an object that has two values. So:

CREATE OR REPLACE VIEW MT_TIMESTAMP_ERROR_VIEW
OF MT_TIMESTAMP_ERROR_TYPE
WITH OBJECT IDENTIFIER(ID)
AS
(
  select MT_TIMESTAMP_ERROR_TYPE(ecid, CAST(startdate as TIMESTAMP))
  from MT_TIMESTAMP_ERROR_TABLE
);

Here is a SQL Fiddle.

Alternatively, if you want to extract the two fields from the object, then don't declare the view to return an object:

CREATE OR REPLACE VIEW MT_TIMESTAMP_ERROR_VIEW
AS
(
  select t.ecid, t.startdate
  from MT_TIMESTAMP_ERROR_TABLE t
);

PS. I have no idea why Oracle is giving such a confusing error message. It really should be more like "inconsistent datatypes: expected MT_TIMESTAMP_ERROR_TYPE got TIMESTAMP". Presumably, the error handling system is getting confused on the different levels of types.

Customize FacesServlet to get rid of .xhtml extension

If your sole reason is to get rid of the .xhtml extension, then there are various ways depending on the JSF version you're using.

JSF 2.3+

JSF 2.3 offers a new API to collect all views: the ViewHandler#getViews(). Combine this with ServletRegistration#addMapping() in a ServletContextListener as below.

@FacesConfig
@WebListener
public class ApplicationConfig implements ServletContextListener {

    @Override
    public void contextInitialized(ServletContextEvent event) {
        addExtensionLessMappings(event.getServletContext(), FacesContext.getCurrentInstance());
    }

    private void addExtensionLessMappings(ServletContext servletContext, FacesContext facesContext) {
        servletContext
            .getServletRegistrations().values().stream()
            .filter(servlet -> servlet.getClassName().equals(FacesServlet.class.getName()))
            .findAny()
            .ifPresent(facesServlet -> facesContext
                .getApplication()
                .getViewHandler()
                .getViews(facesContext, "/", ViewVisitOption.RETURN_AS_MINIMAL_IMPLICIT_OUTCOME)
                .forEach(view -> facesServlet.addMapping(view))
        );
    }
}

Effectively, this is an oneliner. Source: Arjan Tijms' Blog and The Definitive Guide to JSF.

If you're using MyFaces as JSF 2.3 implementation, then this can be transparently activated by solely the following web.xml context parameter:

<context-param>
    <param-name>org.apache.myfaces.AUTOMATIC_EXTENSIONLESS_MAPPING</param-name>
    <param-value>true</param-value>
</context-param>

Mojarra does not have an equivalent yet.

JSF 2.2-

Use OmniFaces FacesViews. It offers a zero-configuration way to achieve that by placing the view files in /WEB-INF/faces-views/ folder. Otherwise, if you intend to not modify your project structure and want to keep your view files at the usual place and still benefit of extensionless URLs, then it's a matter of adding the following context parameter:

<context-param>
    <param-name>org.omnifaces.FACES_VIEWS_SCAN_PATHS</param-name>
    <param-value>/*.xhtml</param-value>
</context-param>

In case you don't want to use OmniFaces, but rather want to homegrow your own, just look at source code of OmniFaces. It's open source under Apache 2.0 License. It's only not an oneliner.