Category Archives: PL/SQL

PL/SQL Script to Inactive and Delete Adhoc Role

DECLARE lc_role_name apps.wf_roles.name%TYPE := ‘XX_TEST_ROLE’; BEGIN wf_directory.setadhocroleexpiration(lc_role_name, sysdate -1 ); COMMIT; wf_directory.setadhocrolestatus( lc_role_name, ‘INACTIVE’); COMMIT; wf_directory.deleterole(lc_role_name , ‘WF_LOCAL_ROLES’ , 0); COMMIT; END; /DECLARE lc_role_name apps.wf_roles.name%type := ‘XX_TEST_ROLE’; BEGIN wf_directory.setadhocroleexpiration(lc_role_name, sysdate -1 ); COMMIT; wf_directory.setadhocrolestatus( lc_role_name, ‘INACTIVE’); COMMIT; wf_directory.deleterole(lc_role_name , ‘WF_LOCAL_ROLES’ , 0); COMMIT; END; / PL/SQL Script to Create Adhoc Role PL/SQL Script to Remove… Read More »

PL/SQL Script to Remove and Add Users to Adhoc Role

DECLARE ln_role_users_count NUMBER; lc_role_name apps.wf_roles.NAME%TYPE := ‘XX_TEST_ROLE’; BEGIN —————————— –Checking if Adhoc Role Exits —————————— SELECT COUNT (1) INTO ln_role_users_count FROM apps.wf_user_roles WHERE NAME = lc_role_name;   IF ln_role_users_count > 0 THEN ——————————– –Removing users from Adhoc Role ——————————– apps.wf_directory.removeusersfromadhocrole (role_name => lc_role_name);   dbms_output.put_line("Users removed from Role!"); END IF; ——————————– –Adding users to Adhoc… Read More »

PL/SQL Script to Create Adhoc Role

DECLARE ln_role_count NUMBER; lc_role_name apps.wf_roles.NAME%TYPE := ‘XX_TEST_ROLE’; lc_role_display_name apps.wf_roles.description%TYPE := ‘XX Test Role’; BEGIN —————————— –Checking if Adhoc Role Exits —————————— SELECT COUNT (1) INTO ln_role_count FROM apps.wf_roles WHERE NAME = lc_role_name;   IF ln_role_count = 0 THEN ——————— –Creating Adhoc Role ——————— apps.wf_directory.createadhocrole (role_name => lc_role_name, role_display_name => lc_role_display_name, notification_preference => ‘MAILHTM2’ );  … Read More »

Date Range in Oracle – Minimum Possible Date and Maximum Possible Date

Like any other data type in oracle, even DATE data type has a length restriction. A Date data type can hold a maximum data of 7 bytes. You can refer to this article from Oracle corporation on Internal Oracle Database Data Types and their limits which shows the limit restrictions of different data types. Below… Read More »

Usage of PL/SQL Collections

/********************************************************* *PURPOSE: Sample Usage of PL/SQL Collections * *AUTHOR: Shailender Thallam * **********************************************************/ SET serveroutput ON; DECLARE ——————– –Record type object ——————– TYPE emp_type IS record ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), deptno NUMBER(2)); ——————- –Table type object ——————- TYPE emp_table IS TABLE OF emp_type; ——————— –Table type variable ——————— emp_var emp_table := emp_table();… Read More »

What is BULK COLLECT? How and Why do we need to use it?

Before understanding about BULK COLLECT, lets see how a PL/SQL code is executed. Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL engine. When ever there is a need to process an SQL statement, a context switch happens between… Read More »