Skip to content

Instantly share code, notes, and snippets.

@garrettdieckmann
Created February 25, 2014 23:05
Show Gist options
  • Save garrettdieckmann/9219911 to your computer and use it in GitHub Desktop.
Save garrettdieckmann/9219911 to your computer and use it in GitHub Desktop.
PeopleSoft Audit Records
/* Title: GET_PS_OPRID */
/* Purpose: Retrieves the operator id (OPRID) */
/* from a VARCHAR2 comma separated field */
/* of the format 'OPRID,OS_USER,MACHINE' */
/* If no OPRID is found, it returns '!NoOPRID' */
/* Limitations: (any grants, privileges, etc) */
/* Who: PeopleSoft Inc. */
/* Date: 2000-04-07 */
Create or replace function SYSADM.GET_PS_OPRID (v_client_info VARCHAR2 )
return VARCHAR2 is
i integer;
offset integer;
sql_user VARCHAR2(30);
max_len constant integer := 30;
first_char constant integer := 1;
begin
if ( length(v_client_info) IS NULL ) then
/* Oracle delivered: Doesnt account for SQL updates */
/* modified to select SQL user into oprid field */
SELECT user INTO sql_user FROM dual;
if ( length(sql_user) IS NULL ) then
return('!NoOPRID');
else
return (substr (sql_user, first_char, max_len));
end if;
end if;
i := 1;
offset := 0;
/* Test if non-user client. If so, add offset to get program name */
if ( substr(v_client_info,first_char,1) = ',' ) then
offset := 4;
-- return('!NoOPRID');
end if;
/* loop until end of name, or end of field length */
while ( (substr(v_client_info,i + offset,1)) <> ',' and i < max_len) loop
i := i + 1;
end loop;
return (substr (v_client_info, first_char + offset, i));
end GET_PS_OPRID;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment