userenv

Database Type and Version

• Source database type and version: all Oracle versions
• Target database type and version: all GaussDB versions

Function replacement syntax:

CREATE OR REPLACE FUNCTION dsc_ora_ext.dsc_fn_userenv
/* This function is used to retrieve information about the current session. 
   Currently the "sid" and "client_info" are supported.
*/
     ( i_session_attr   IN     VARCHAR(32) ) 
RETURN VARCHAR(64)
AS
    v_session_attr             VARCHAR(32);
    v_session_attr_value       VARCHAR(64);
BEGIN

    v_session_attr  := TRIM(i_session_attr);

    IF v_session_attr IS NULL
    THEN
        RAISE EXCEPTION 'Empty string/NULL is an invalid USERENV parameter';
    ELSE

        v_session_attr	:= UPPER(v_session_attr);
        IF v_session_attr = 'SID'
        -- if session id is required
        THEN
            v_session_attr_value := pg_catalog.pg_backend_pid();

        ELSIF v_session_attr = 'CLIENT_INFO'
        -- it returns the session's application_name, which is set using set application_name='<<CLIENT APPLICATION NAME>>'
        THEN
            SELECT s.application_name 
              INTO v_session_attr_value
              FROM pg_catalog.pg_stat_activity s
             WHERE s.pid 	= pg_catalog.pg_backend_pid();

        ELSIF v_session_attr = 'SESSION_USER'
        -- if session user name is required
        THEN
            v_session_attr_value := session_user;

		ELSIF v_session_attr IN ( 'INSTANCE', 'ENTRYID', 'ISDBA', 'LANG', 'LANGUAGE', 'SESSIONID', 'TERMINAL' )
        -- if other valid Oracle parameter is passed
        THEN
            RAISE EXCEPTION '% is an unsupported USERENV parameter', i_session_attr;

        ELSE
        -- if invalid parameter is passed
           RAISE EXCEPTION '% is an invalid USERENV parameter', i_session_attr;
        END IF;

    END IF;

    RETURN v_session_attr_value;
END;