Here in this article I will be writing how to read a CLOB file using DBMS_LOB which is more than 32kb size. The below example is related to dunning letters where a seeded concurrent program IEX: Bulk XML Delivery Manager generates dunning letters data in XML form and stored it in table IEX_XML_REQUEST_HISTORIES in CLOB… Read More »
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 »
Before reading this article, read about Roles in Workflow if you are not aware of what a role is about. A role can be created from application from “User Management” responsibility. Adhoc role is something which can be created dynamically using PL/SQL on run time based on requirement. Not only Adhoc role can be created… Read More »
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 »
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 »
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 »
/********************************************************* *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 »
It would be tiresome for most of us to debug issues especially in extensions when we have no clue of flow of process execution and also the values of variables in intermittent stages. Debugging becomes more complex when most of custom logic is in huge pl/sql scripts. Today in this article I will show how… Read More »
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 »
Click on the question to see the answer Basic PL/SQL Interview Questions Difference between Syntax and Runtime errors in PL/SQL?? Explain what is COMMIT, ROLLBACK and SAVEPOINT?? How many types of Triggers are there in PL/SQL and what are they? How to generate Debugging Information in PL/SQL? In what order should a open/fetch/loop set of… Read More »