Snippet Name: FUNCTIONS: without parameters Description: Not all functions take parameters; some produce output without evaluating anything passed in. This is a basic example of a function that does not use parameters. Also see:» FUNCTIONS: Deterministic » FUNCTIONS: Nested Functions » FUNCTIONS: IF statement » FUNCTIONS: date/time » FUNCTIONS: Sample functions » FUNCTIONS: drop » FUNCTIONS: Recompile » FUNCTIONS: DEBUG mode » FUNCTIONS: IN OUT parameter » FUNCTIONS: with output parameters » FUNCTIONS: with parameters » FUNCTIONS: Create function » FUNCTIONS: special restrictions » FUNCTIONS: System Privileges » IN Function » Built-In Functions: CASE » Built-In Functions: DECODE » SUBST and INSTR together » INSTR (InString) » SUBSTR (SubString) » String Functions: REVERSE » String Functions: LENGTH » String Functions: INSTR » String Functions: CONCAT » String Functions: CHAR » String Functions: INITCAP » String Functions: LOWER » String Functions: UPPER » Date Functions: NUMTOYMINTERVAL » Date Functions: NUMTODSINTERVAL Comment: (none) Language: Highlight Mode: PLSQL Last Modified: March 13th, 2009
Description: Not all functions take parameters; some produce output without evaluating anything passed in. This is a basic example of a function that does not use parameters.
Also see:» FUNCTIONS: Deterministic » FUNCTIONS: Nested Functions » FUNCTIONS: IF statement » FUNCTIONS: date/time » FUNCTIONS: Sample functions » FUNCTIONS: drop » FUNCTIONS: Recompile » FUNCTIONS: DEBUG mode » FUNCTIONS: IN OUT parameter » FUNCTIONS: with output parameters » FUNCTIONS: with parameters » FUNCTIONS: Create function » FUNCTIONS: special restrictions » FUNCTIONS: System Privileges » IN Function » Built-In Functions: CASE » Built-In Functions: DECODE » SUBST and INSTR together » INSTR (InString) » SUBSTR (SubString) » String Functions: REVERSE » String Functions: LENGTH » String Functions: INSTR » String Functions: CONCAT » String Functions: CHAR » String Functions: INITCAP » String Functions: LOWER » String Functions: UPPER » Date Functions: NUMTOYMINTERVAL » Date Functions: NUMTODSINTERVAL
CREATE OR REPLACE FUNCTION getuser RETURN user_users.username%TYPE IS -- explain use of %TYPE vOSUser user_users.username%TYPE; -- explain INTO and return BEGIN SELECT osuser INTO vUser FROM gv$session WHERE sid = ( SELECT sid FROM gv$mystat WHERE ROWNUM = 1); RETURN vuser; EXCEPTION WHEN OTHERS THEN RETURN 'UNK'; END getuser; / -- test getuser function SELECT getuser FROM dual; Simple FUNCTION Used IN An INSERT Statement CREATE TABLE my_table ( col_values VARCHAR2(10), insert_by VARCHAR2(30)); INSERT INTO my_stuff VALUES ('ABC', getosuser); INSERT INTO my_stuff VALUES ('ABCD', 'Harold Stassen'); INSERT INTO my_stuff VALUES ('ABCDE', getosuser); INSERT INTO my_stuff VALUES ('ABCDEF', 'Mary Lamb'); INSERT INTO my_stuff VALUES ('AB', getosuser); COMMIT; SELECT * FROM my_table;