Category: plsql

PL/SQL – Triggers ”; Previous Next In this chapter, we will discuss Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events − A database manipulation (DML) statement (DELETE, INSERT, or UPDATE) A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). Triggers can be defined on the table, view, schema, or database with which the event is associated. Benefits of Triggers Triggers can be written for the following purposes − Generating some derived column values automatically Enforcing referential integrity Event logging and storing information on table access Auditing Synchronous replication of tables Imposing security authorizations Preventing invalid transactions Creating Triggers The syntax for creating a trigger is − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; Where, CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name. {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view. {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation. [OF col_name] − This specifies the column name that will be updated. [ON table_name] − This specifies the name of the table associated with the trigger. [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE. [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger. WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers. Example To start with, we will be using the CUSTOMERS table we had created and used in the previous chapters − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ The following program creates a row-level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values − CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW WHEN (NEW.ID > 0) DECLARE sal_diff number; BEGIN sal_diff := :NEW.salary – :OLD.salary; dbms_output.put_line(”Old salary: ” || :OLD.salary); dbms_output.put_line(”New salary: ” || :NEW.salary); dbms_output.put_line(”Salary difference: ” || sal_diff); END; / When the above code is executed at the SQL prompt, it produces the following result − Trigger created. The following points need to be considered here − OLD and NEW references are not available for table-level triggers, rather you can use them for record-level triggers. If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state. The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using the DELETE operation on the table. Triggering a Trigger Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT statement, which will create a new record in the table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, ”Kriti”, 22, ”HP”, 7500.00 ); When a record is created in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result − Old salary: New salary: 7500 Salary difference: Because this is a new record, old salary is not available and the above result comes as null. Let us now perform one more DML operation on the CUSTOMERS table. The UPDATE statement will update an existing record in the table − UPDATE customers SET salary = salary + 500 WHERE id = 2; When a record is updated in the CUSTOMERS table, the above create trigger, display_salary_changes will be fired and it will display the following result − Old salary: 1500 New salary: 2000 Salary difference: 500 Print Page Previous Next Advertisements ”;

PL/SQL – Cursors ”; Previous Next In this chapter, we will discuss the cursors in PL/SQL. Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc. A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors − Implicit cursors Explicit cursors Implicit Cursors Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes − S.No Attribute & Description 1 %FOUND Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE. 2 %NOTFOUND The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE. 3 %ISOPEN Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement. 4 %ROWCOUNT Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the example. Example We will be using the CUSTOMERS table we had created and used in the previous chapters. Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ The following program will update the table and increase the salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected − DECLARE total_rows number(2); BEGIN UPDATE customers SET salary = salary + 500; IF sql%notfound THEN dbms_output.put_line(”no customers selected”); ELSIF sql%found THEN total_rows := sql%rowcount; dbms_output.put_line( total_rows || ” customers selected ”); END IF; END; / When the above code is executed at the SQL prompt, it produces the following result − 6 customers selected PL/SQL procedure successfully completed. If you check the records in customers table, you will find that the rows have been updated − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2500.00 | | 2 | Khilan | 25 | Delhi | 2000.00 | | 3 | kaushik | 23 | Kota | 2500.00 | | 4 | Chaitali | 25 | Mumbai | 7000.00 | | 5 | Hardik | 27 | Bhopal | 9000.00 | | 6 | Komal | 22 | MP | 5000.00 | +—-+———-+—–+———–+———-+ Explicit Cursors Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. The syntax for creating an explicit cursor is − CURSOR cursor_name IS select_statement; Working with an explicit cursor includes the following steps − Declaring the cursor for initializing the memory Opening the cursor for allocating the memory Fetching the cursor for retrieving the data Closing the cursor to release the allocated memory Declaring the Cursor Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example − CURSOR c_customers IS SELECT id, name, address FROM customers; Opening the Cursor Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows − OPEN c_customers; Fetching the Cursor Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows − FETCH c_customers INTO c_id, c_name, c_addr; Closing the Cursor Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows − CLOSE c_customers; Example Following is a complete example to illustrate the concepts of explicit cursors &minua; DECLARE c_id customers.id%type; c_name customers.name%type; c_addr customers.address%type; CURSOR c_customers is SELECT id, name, address FROM customers; BEGIN OPEN c_customers; LOOP FETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ” ” || c_name || ” ” || c_addr); END LOOP; CLOSE c_customers; END; / When the above code is executed at the SQL prompt, it produces the following result − 1 Ramesh Ahmedabad 2 Khilan Delhi 3 kaushik Kota 4 Chaitali Mumbai 5 Hardik Bhopal 6 Komal MP

PL/SQL – Transactions ”; Previous Next In this chapter, we will discuss the transactions in PL/SQL. A database transaction is an atomic unit of work that may consist of one or more related SQL statements. It is called atomic because the database modifications brought about by the SQL statements that constitute a transaction can collectively be either committed, i.e., made permanent to the database or rolled back (undone) from the database. A successfully executed SQL statement and a committed transaction are not same. Even if an SQL statement is executed successfully, unless the transaction containing the statement is committed, it can be rolled back and all changes made by the statement(s) can be undone. Starting and Ending a Transaction A transaction has a beginning and an end. A transaction starts when one of the following events take place − The first SQL statement is performed after connecting to the database. At each new SQL statement issued after a transaction is completed. A transaction ends when one of the following events take place − A COMMIT or a ROLLBACK statement is issued. A DDL statement, such as CREATE TABLE statement, is issued; because in that case a COMMIT is automatically performed. A DCL statement, such as a GRANT statement, is issued; because in that case a COMMIT is automatically performed. User disconnects from the database. User exits from SQL*PLUS by issuing the EXIT command, a COMMIT is automatically performed. SQL*Plus terminates abnormally, a ROLLBACK is automatically performed. A DML statement fails; in that case a ROLLBACK is automatically performed for undoing that DML statement. Committing a Transaction A transaction is made permanent by issuing the SQL command COMMIT. The general syntax for the COMMIT command is − COMMIT; For example, INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”kaushik”, 23, ”Kota”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, ”Komal”, 22, ”MP”, 4500.00 ); COMMIT; Rolling Back Transactions Changes made to the database without COMMIT could be undone using the ROLLBACK command. The general syntax for the ROLLBACK command is − ROLLBACK [TO SAVEPOINT < savepoint_name>]; When a transaction is aborted due to some unprecedented situation, like system failure, the entire transaction since a commit is automatically rolled back. If you are not using savepoint, then simply use the following statement to rollback all the changes − ROLLBACK; Savepoints Savepoints are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints. By setting savepoints within a long transaction, you can roll back to a checkpoint if required. This is done by issuing the SAVEPOINT command. The general syntax for the SAVEPOINT command is − SAVEPOINT < savepoint_name >; For example INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, ”Rajnish”, 27, ”HP”, 9500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (8, ”Riddhi”, 21, ”WB”, 4500.00 ); SAVEPOINT sav1; UPDATE CUSTOMERS SET SALARY = SALARY + 1000; ROLLBACK TO sav1; UPDATE CUSTOMERS SET SALARY = SALARY + 1000 WHERE ID = 7; UPDATE CUSTOMERS SET SALARY = SALARY + 1000 WHERE ID = 8; COMMIT; ROLLBACK TO sav1 − This statement rolls back all the changes up to the point, where you had marked savepoint sav1. After that, the new changes that you make will start. Automatic Transaction Control To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment variable as − SET AUTOCOMMIT ON; You can turn-off the auto commit mode using the following command − SET AUTOCOMMIT OFF; Print Page Previous Next Advertisements ”;

PL/SQL – Functions ”; Previous Next In this chapter, we will discuss the functions in PL/SQL. A function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous chapter are true for functions too. Creating a Function A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows − CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [, …])] RETURN return_datatype {IS | AS} BEGIN < function_body > END [function_name]; Where, function-name specifies the name of the function. [OR REPLACE] option allows the modification of an existing function. The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure. The function must contain a return statement. The RETURN clause specifies the data type you are going to return from the function. function-body contains the executable part. The AS keyword is used instead of the IS keyword for creating a standalone function. Example The following example illustrates how to create and call a standalone function. This function returns the total number of CUSTOMERS in the customers table. We will use the CUSTOMERS table, which we had created in the PL/SQL Variables chapter − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END; / When the above code is executed using the SQL prompt, it will produce the following result − Function created. Calling a Function While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function. A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program. To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block − DECLARE c number(2); BEGIN c := totalCustomers(); dbms_output.put_line(”Total no. of Customers: ” || c); END; / When the above code is executed at the SQL prompt, it produces the following result − Total no. of Customers: 6 PL/SQL procedure successfully completed. Example The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values. DECLARE a number; b number; c number; FUNCTION findMax(x IN number, y IN number) RETURN number IS z number; BEGIN IF x > y THEN z:= x; ELSE Z:= y; END IF; RETURN z; END; BEGIN a:= 23; b:= 45; c := findMax(a, b); dbms_output.put_line(” Maximum of (23,45): ” || c); END; / When the above code is executed at the SQL prompt, it produces the following result − Maximum of (23,45): 45 PL/SQL procedure successfully completed. PL/SQL Recursive Functions We have seen that a program or subprogram may call another subprogram. When a subprogram calls itself, it is referred to as a recursive call and the process is known as recursion. To illustrate the concept, let us calculate the factorial of a number. Factorial of a number n is defined as − n! = n*(n-1)! = n*(n-1)*(n-2)! … = n*(n-1)*(n-2)*(n-3)… 1 The following program calculates the factorial of a given number by calling itself recursively − DECLARE num number; factorial number; FUNCTION fact(x number) RETURN number IS f number; BEGIN IF x=0 THEN f := 1; ELSE f := x * fact(x-1); END IF; RETURN f; END; BEGIN num:= 6; factorial := fact(num); dbms_output.put_line(” Factorial ”|| num || ” is ” || factorial); END; / When the above code is executed at the SQL prompt, it produces the following result − Factorial 6 is 720 PL/SQL procedure successfully completed. Print Page Previous Next Advertisements ”;

PL/SQL – Variables ”; Previous Next In this chapter, we will discuss Variables in Pl/SQL. A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in PL/SQL has a specific data type, which determines the size and the layout of the variable”s memory; the range of values that can be stored within that memory and the set of operations that can be applied to the variable. The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, variable names are not case-sensitive. You cannot use a reserved PL/SQL keyword as a variable name. PL/SQL programming language allows to define various types of variables, such as date time data types, records, collections, etc. which we will cover in subsequent chapters. For this chapter, let us study only basic variable types. Variable Declaration in PL/SQL PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable”s value and the storage location is identified by the variable name. The syntax for declaring a variable is − variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] Where, variable_name is a valid identifier in PL/SQL, datatype must be a valid PL/SQL data type or any user defined data type which we already have discussed in the last chapter. Some valid variable declarations along with their definition are shown below − sales number(10, 2); pi CONSTANT double precision := 3.1415; name varchar2(25); address varchar2(100); When you provide a size, scale or precision limit with the data type, it is called a constrained declaration. Constrained declarations require less memory than unconstrained declarations. For example − sales number(10, 2); name varchar2(25); address varchar2(100); Initializing Variables in PL/SQL Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following − The DEFAULT keyword The assignment operator For example − counter binary_integer := 0; greetings varchar2(20) DEFAULT ”Have a Good Day”; You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable. It is a good programming practice to initialize variables properly otherwise, sometimes programs would produce unexpected results. Try the following example which makes use of various types of variables − DECLARE a integer := 10; b integer := 20; c integer; f real; BEGIN c := a + b; dbms_output.put_line(”Value of c: ” || c); f := 70.0/3.0; dbms_output.put_line(”Value of f: ” || f); END; / When the above code is executed, it produces the following result − Value of c: 30 Value of f: 23.333333333333333333 PL/SQL procedure successfully completed. Variable Scope in PL/SQL PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer block, it is also accessible to all nested inner blocks. There are two types of variable scope − Local variables − Variables declared in an inner block and not accessible to outer blocks. Global variables − Variables declared in the outermost block or a package. Following example shows the usage of Local and Global variables in its simple form − DECLARE — Global variables num1 number := 95; num2 number := 85; BEGIN dbms_output.put_line(”Outer Variable num1: ” || num1); dbms_output.put_line(”Outer Variable num2: ” || num2); DECLARE — Local variables num1 number := 195; num2 number := 185; BEGIN dbms_output.put_line(”Inner Variable num1: ” || num1); dbms_output.put_line(”Inner Variable num2: ” || num2); END; END; / When the above code is executed, it produces the following result − Outer Variable num1: 95 Outer Variable num2: 85 Inner Variable num1: 195 Inner Variable num2: 185 PL/SQL procedure successfully completed. Assigning SQL Query Results to PL/SQL Variables You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list. The following example illustrates the concept. Let us create a table named CUSTOMERS − (For SQL statements, please refer to the SQL tutorial) CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Table Created Let us now insert some values in the table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”kaushik”, 23, ”Kota”, 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, ”Komal”, 22, ”MP”, 4500.00 ); The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL − DECLARE c_id customers.id%type := 1; c_name customers.name%type; c_addr customers.address%type; c_sal customers.salary%type; BEGIN SELECT name, address, salary INTO c_name, c_addr, c_sal FROM customers WHERE id = c_id; dbms_output.put_line (”Customer ” ||c_name || ” from ” || c_addr || ” earns ” || c_sal); END; / When the above code is executed, it produces the following result − Customer Ramesh from Ahmedabad earns 2000 PL/SQL procedure completed successfully Print Page Previous Next Advertisements ”;

PL/SQL – Collections ”; Previous Next In this chapter, we will discuss the Collections in PL/SQL. A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection. PL/SQL provides three collection types − Index-by tables or Associative array Nested table Variable-size array or Varray Oracle documentation provides the following characteristics for each type of collections − Collection Type Number of Elements Subscript Type Dense or Sparse Where Created Can Be Object Type Attribute Associative array (or index-by table) Unbounded String or integer Either Only in PL/SQL block No Nested table Unbounded Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes Variablesize array (Varray) Bounded Integer Always dense Either in PL/SQL block or at schema level Yes We have already discussed varray in the chapter ”PL/SQL arrays”. In this chapter, we will discuss the PL/SQL tables. Both types of PL/SQL tables, i.e., the index-by tables and the nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot. Index-By Table An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string. An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name, the keys of which will be of the subscript_type and associated values will be of the element_type TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name; Example Following example shows how to create a table to store integer values along with names and later it prints the same list of names. DECLARE TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); salary_list salary; name VARCHAR2(20); BEGIN — adding elements to the table salary_list(”Rajnish”) := 62000; salary_list(”Minakshi”) := 75000; salary_list(”Martin”) := 100000; salary_list(”James”) := 78000; — printing the table name := salary_list.FIRST; WHILE name IS NOT null LOOP dbms_output.put_line (”Salary of ” || name || ” is ” || TO_CHAR(salary_list(name))); name := salary_list.NEXT(name); END LOOP; END; / When the above code is executed at the SQL prompt, it produces the following result − Salary of James is 78000 Salary of Martin is 100000 Salary of Minakshi is 75000 Salary of Rajnish is 62000 PL/SQL procedure successfully completed. Example Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | +—-+———-+—–+———–+———-+ DECLARE CURSOR c_customers is select name from customers; TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; name_list c_list; counter integer :=0; BEGIN FOR n IN c_customers LOOP counter := counter +1; name_list(counter) := n.name; dbms_output.put_line(”Customer(”||counter||”):”||name_lis t(counter)); END LOOP; END; / When the above code is executed at the SQL prompt, it produces the following result − Customer(1): Ramesh Customer(2): Khilan Customer(3): kaushik Customer(4): Chaitali Customer(5): Hardik Customer(6): Komal PL/SQL procedure successfully completed Nested Tables A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects − An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically. An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it. A nested table is created using the following syntax − TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name type_name; This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause. A nested table can be stored in a database column. It can further be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database. Example The following examples illustrate the use of nested table − DECLARE TYPE names_table IS TABLE OF VARCHAR2(10); TYPE grades IS TABLE OF INTEGER; names names_table; marks grades; total integer; BEGIN names := names_table(”Kavita”, ”Pritam”, ”Ayan”, ”Rishav”, ”Aziz”); marks:= grades(98, 97, 78, 87, 92); total := names.count; dbms_output.put_line(”Total ”|| total || ” Students”); FOR i IN 1 .. total LOOP dbms_output.put_line(”Student:”||names(i)||”, Marks:” || marks(i)); end loop; END; / When the above code is executed at the SQL prompt, it produces the following result − Total 5 Students Student:Kavita, Marks:98 Student:Pritam, Marks:97 Student:Ayan, Marks:78 Student:Rishav, Marks:87 Student:Aziz, Marks:92 PL/SQL procedure successfully completed. Example Elements of a nested table can also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as − Select * from customers; +—-+———-+—–+———–+———-+ | ID | NAME | AGE | ADDRESS | SALARY | +—-+———-+—–+———–+———-+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal |

PL/SQL – Strings ”; Previous Next The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. PL/SQL offers three kinds of strings − Fixed-length strings − In such strings, programmers specify the length while declaring the string. The string is right-padded with spaces to the length so specified. Variable-length strings − In such strings, a maximum length up to 32,767, for the string is specified and no padding takes place. Character large objects (CLOBs) − These are variable-length strings that can be up to 128 terabytes. PL/SQL strings could be either variables or literals. A string literal is enclosed within quotation marks. For example, ”This is a string literal.” Or ”hello world” To include a single quote inside a string literal, you need to type two single quotes next to one another. For example, ”this isn””t what it looks like” Declaring String Variables Oracle database provides numerous string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes prefixed with an ”N” are ”national character set” datatypes, that store Unicode character data. If you need to declare a variable-length string, you must provide the maximum length of that string. For example, the VARCHAR2 data type. The following example illustrates declaring and using some string variables − DECLARE name varchar2(20); company varchar2(30); introduction clob; choice char(1); BEGIN name := ”John Smith”; company := ”Infotech”; introduction := ” Hello! I””m John Smith from Infotech.”; choice := ”y”; IF choice = ”y” THEN dbms_output.put_line(name); dbms_output.put_line(company); dbms_output.put_line(introduction); END IF; END; / When the above code is executed at the SQL prompt, it produces the following result − John Smith Infotech Hello! I”m John Smith from Infotech. PL/SQL procedure successfully completed To declare a fixed-length string, use the CHAR datatype. Here you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length required. The following two declarations are identical − red_flag CHAR(1) := ”Y”; red_flag CHAR := ”Y”; PL/SQL String Functions and Operators PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL − S.No Function & Purpose 1 ASCII(x); Returns the ASCII value of the character x. 2 CHR(x); Returns the character with the ASCII value of x. 3 CONCAT(x, y); Concatenates the strings x and y and returns the appended string. 4 INITCAP(x); Converts the initial letter of each word in x to uppercase and returns that string. 5 INSTR(x, find_string [, start] [, occurrence]); Searches for find_string in x and returns the position at which it occurs. 6 INSTRB(x); Returns the location of a string within another string, but returns the value in bytes. 7 LENGTH(x); Returns the number of characters in x. 8 LENGTHB(x); Returns the length of a character string in bytes for single byte character set. 9 LOWER(x); Converts the letters in x to lowercase and returns that string. 10 LPAD(x, width [, pad_string]) ; Pads x with spaces to the left, to bring the total length of the string up to width characters. 11 LTRIM(x [, trim_string]); Trims characters from the left of x. 12 NANVL(x, value); Returns value if x matches the NaN special value (not a number), otherwise x is returned. 13 NLS_INITCAP(x); Same as the INITCAP function except that it can use a different sort method as specified by NLSSORT. 14 NLS_LOWER(x) ; Same as the LOWER function except that it can use a different sort method as specified by NLSSORT. 15 NLS_UPPER(x); Same as the UPPER function except that it can use a different sort method as specified by NLSSORT. 16 NLSSORT(x); Changes the method of sorting the characters. Must be specified before any NLS function; otherwise, the default sort will be used. 17 NVL(x, value); Returns value if x is null; otherwise, x is returned. 18 NVL2(x, value1, value2); Returns value1 if x is not null; if x is null, value2 is returned. 19 REPLACE(x, search_string, replace_string); Searches x for search_string and replaces it with replace_string. 20 RPAD(x, width [, pad_string]); Pads x to the right. 21 RTRIM(x [, trim_string]); Trims x from the right. 22 SOUNDEX(x) ; Returns a string containing the phonetic representation of x. 23 SUBSTR(x, start [, length]); Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied. 24 SUBSTRB(x); Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single-byte character systems. 25 TRIM([trim_char FROM) x); Trims characters from the left and right of x. 26 UPPER(x); Converts the letters in x to uppercase and returns that string. Let us now work out on a few examples to understand the concept − Example 1 DECLARE greetings varchar2(11) := ”hello world”; BEGIN dbms_output.put_line(UPPER(greetings)); dbms_output.put_line(LOWER(greetings)); dbms_output.put_line(INITCAP(greetings)); /* retrieve the first character in the string */ dbms_output.put_line ( SUBSTR (greetings, 1, 1)); /* retrieve the last character in the string */ dbms_output.put_line ( SUBSTR (greetings, -1, 1)); /* retrieve five characters, starting from the seventh position. */ dbms_output.put_line ( SUBSTR (greetings, 7, 5)); /* retrieve the remainder of the string, starting from the second position. */ dbms_output.put_line ( SUBSTR (greetings, 2)); /* find the location of the first “e” */ dbms_output.put_line ( INSTR (greetings, ”e”)); END; / When the above code is executed at the SQL prompt, it produces the following result − HELLO WORLD hello world Hello World h d World ello World 2 PL/SQL procedure successfully completed. Example 2 DECLARE greetings varchar2(30) := ”……Hello World…..”; BEGIN dbms_output.put_line(RTRIM(greetings,”.”)); dbms_output.put_line(LTRIM(greetings, ”.”)); dbms_output.put_line(TRIM( ”.” from greetings)); END; / When the above code is executed at the SQL prompt, it produces the following result − ……Hello World Hello World….. Hello World PL/SQL procedure successfully completed. Print Page Previous Next

PL/SQL – Exceptions ”; Previous Next In this chapter, we will discuss Exceptions in PL/SQL. An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions − System-defined exceptions User-defined exceptions Syntax for Exception Handling The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN − DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling goes here > WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements …….. WHEN others THEN exception3-handling-statements END; Example Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we had created and used in the previous chapters − DECLARE c_id customers.id%type := 8; c_name customerS.Name%type; c_addr customers.address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE (”Name: ”|| c_name); DBMS_OUTPUT.PUT_LINE (”Address: ” || c_addr); EXCEPTION WHEN no_data_found THEN dbms_output.put_line(”No such customer!”); WHEN others THEN dbms_output.put_line(”Error!”); END; / When the above code is executed at the SQL prompt, it produces the following result − No such customer! PL/SQL procedure successfully completed. The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block. Raising Exceptions Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception − DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END; You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way. User-defined Exceptions PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. The syntax for declaring an exception is − DECLARE my-exception EXCEPTION; Example The following example illustrates the concept. This program asks for a customer ID, when the user enters an invalid ID, the exception invalid_id is raised. DECLARE c_id customers.id%type := &cc_id; c_name customerS.Name%type; c_addr customers.address%type; — user defined exception ex_invalid_id EXCEPTION; BEGIN IF c_id <= 0 THEN RAISE ex_invalid_id; ELSE SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; DBMS_OUTPUT.PUT_LINE (”Name: ”|| c_name); DBMS_OUTPUT.PUT_LINE (”Address: ” || c_addr); END IF; EXCEPTION WHEN ex_invalid_id THEN dbms_output.put_line(”ID must be greater than zero!”); WHEN no_data_found THEN dbms_output.put_line(”No such customer!”); WHEN others THEN dbms_output.put_line(”Error!”); END; / When the above code is executed at the SQL prompt, it produces the following result − Enter value for cc_id: -6 (let”s enter a value -6) old 2: c_id customers.id%type := &cc_id; new 2: c_id customers.id%type := -6; ID must be greater than zero! PL/SQL procedure successfully completed. Pre-defined Exceptions PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions − Exception Oracle Error SQLCODE Description ACCESS_INTO_NULL 06530 -6530 It is raised when a null object is automatically assigned a value. CASE_NOT_FOUND 06592 -6592 It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL 06531 -6531 It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are attempted to be stored in a column with unique index. INVALID_CURSOR 01001 -1001 It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor. INVALID_NUMBER 01722 -1722 It is raised when the conversion of a character string into a number fails because the string does not represent a valid number. LOGIN_DENIED 01017 -1017 It is raised when a program attempts to log on to the database with an invalid username or password. NO_DATA_FOUND 01403 +100 It is raised when a SELECT INTO statement returns no rows. NOT_LOGGED_ON 01012 -1012 It is raised when a database call is issued without being connected to the database. PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL has an internal problem. ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable having incompatible data type. SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but the instance of the object type was not initialized. STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or memory was corrupted. TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT INTO statement returns more than one row. VALUE_ERROR 06502 -6502 It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs. ZERO_DIVIDE 01476 1476 It is raised when an attempt is made to divide a number by zero. Print Page Previous Next Advertisements ”;

PL/SQL Tutorial PDF Version Quick Guide Resources Job Search Discussion PL/SQL Tutorial PL/SQL, which stands for Procedural Language extensions to the Structured Query Language (SQL). It is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90”s to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java. In this tutorial, we”ll give you a great understanding of PL/SQL to proceed with the Oracle database and other advanced RDBMS concepts. Purpose of PL/SQL The purpose of PL/SQL is to merge database commands with procedural programming language. It offers more complete programming solutions for building critical applications that operate on the Oracle database. Features of PL/SQL PL/SQL has the following features − PL/SQL is tightly integrated with SQL. It offers extensive extensive error checking mechanisms. It supports numerous data types for flexible data handling. Includes a variety of programming structures, such as loops and conditionals. Includes a variety of programming structures, such as loops and conditionals. It supports structured programming through functions and procedures. It supports object-oriented programming, enabling more complex data handling and manipulation. It supports the web application development and server pages. Why to learn PL/SQL? Learning PL/SQL is an essential skill for persons who are interested in databases and other advanced RDBMS technologies. PL/SQL offers various benefits, making it an essential skill for database developers − Ease of Use: PL/SQL is straightforward to write and read, featuring block-structured syntax which simplifies programming and debugging. Portability: Programs written in PL/SQL are fully portable across different Oracle databases, ensuring consistency and ease of migration. Tight SQL Integration: PL/SQL is tightly integrated with SQL, allowing for efficient querying, transforming, and updating of data within a database. High Performance: It reduces network traffic by sending entire blocks of statements to the database at once, thus improving performance. Security: It includes robust security features to protect database integrity. Object-Oriented Support: It supports object-oriented programming, and allows you to define object types that can be used in object-oriented designs. PL/SQL Block Structured PL/SQL follows a block-structured approach, dividing programs into logical blocks of code. Each block consists of three main sections − Declarations: This section, starting with the keyword DECLARE, is optional and used for defining variables, cursors, subprograms, and other elements required within the block. Executable Commands: Enclosed between the keywords BEGIN and END, this mandatory section contains executable PL/SQL statements. It must include at least one executable line of code, even if it”s just a NULL command indicating no action. Exception Handling: This starts with the keyword EXCEPTION, this optional section deals with handling errors in the program through defined exceptions. PL/SQL statements are terminated with a semicolon(;). Additionally, blocks can be nested within each other using BEGIN and END keywords. Applications of PL/SQL PL/SQL is widely used in various applications, including − Database Security: It implements robust security measures within the database. XML Management: Generating and managing XML documents within the database. Linking Databases to Web Pages: Integrates databases with web applications. Automation: Automating database administration tasks for efficient management. Who Should Learn PL/SQL? This tutorial is designed for Software Professionals, who are willing to learn PL/SQL Programming Language in simple and easy steps. This tutorial will give you a great understanding of PL/SQL Programming concepts, and after completing this tutorial, you will be at an intermediate level of expertise from where you can take yourself to a higher level of expertise. Prerequisites to learn PL/SQL Before proceeding with this tutorial, you should have a basic understanding of software concepts like what is database, source code, text editor, and execution of programs, etc. If you already have an understanding of SQL and other computer programming languages, then it will be an added advantage to proceed. Let”s get started! PL/SQL Jobs and Opportunities Proficiency in PL/SQL opens up various career opportunities, such as − Oracle PL/SQL Programmer PL/SQL Developer Database Developer Data Analyst Database Testers Data Scientist ETL Developer Database Migration Expert Cloud Database Expert etc By mastering PL/SQL, you can increase your career opportunities in database management and development, as well as in creating secure and scalable applications. Frequently Asked Questions about PL/SQL There are some very Frequently Asked Questions(FAQ) about PL/SQL, this section tries to answer them briefly. What are PL/SQL Records? PL/SQL records are data structures designed to hold multiple data items of different types. They consist of various fields, much like a row in a database table. Comparison of SQL and PL/SQL SQL (Structured Query Language) is a standard language used for creating, manipulating, and retrieving data from relational databases. SQL is mainly used to write queries, as well as create and execute DDL (Data Definition Language) and DML (Data Manipulation Language) statements. Whereas, PL/SQL (Procedural Language/SQL) is an extension of SQL. And it adds procedural capabilities to SQL, enabling the creation of more complex and powerful database applications. PL/SQL supports variables, data types, and control structures such as loops and conditionals, which SQL does not. This makes PL/SQL more efficient for writing program blocks, functions, procedures, triggers, and packages. How do you handle exceptions in PL/SQL? When an exception is raised in PL/SQL, the current PL/SQL block stops its regular execution and transfers control to the exception section. The exception is then handled by an exception handler within the current PL/SQL block or passed to the enclosing block if not handled locally. To manage an exception after it is raised, you need to write an exception handler for it. This handler should be placed after all executable statements in your PL/SQL block but before the END statement. The EXCEPTION keyword indicates the start of the exception-handling section. Which is the best place to learn PL/SQL? The best place to learn PL/SQL is through our comprehensive and user-friendly tutorial. Our PL/SQL tutorial provides an excellent starting point for understanding database programming with PL/SQL. You can explore our simple

PL/SQL – Constants and Literals ”; Previous Next In this chapter, we will discuss constants and literals in PL/SQL. A constant holds a value that once declared, does not change in the program. A constant declaration specifies its name, data type, and value, and allocates storage for it. The declaration can also impose the NOT NULL constraint. Declaring a Constant A constant is declared using the CONSTANT keyword. It requires an initial value and does not allow that value to be changed. For example − PI CONSTANT NUMBER := 3.141592654; DECLARE — constant declaration pi constant number := 3.141592654; — other declarations radius number(5,2); dia number(5,2); circumference number(7, 2); area number (10, 2); BEGIN — processing radius := 9.5; dia := radius * 2; circumference := 2.0 * pi * radius; area := pi * radius * radius; — output dbms_output.put_line(”Radius: ” || radius); dbms_output.put_line(”Diameter: ” || dia); dbms_output.put_line(”Circumference: ” || circumference); dbms_output.put_line(”Area: ” || area); END; / When the above code is executed at the SQL prompt, it produces the following result − Radius: 9.5 Diameter: 19 Circumference: 59.69 Area: 283.53 Pl/SQL procedure successfully completed. The PL/SQL Literals A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For example, TRUE, 786, NULL, ”tutorialspoint” are all literals of type Boolean, number, or string. PL/SQL, literals are case-sensitive. PL/SQL supports the following kinds of literals − Numeric Literals Character Literals String Literals BOOLEAN Literals Date and Time Literals The following table provides examples from all these categories of literal values. S.No Literal Type & Example 1 Numeric Literals 050 78 -14 0 +32767 6.6667 0.0 -12.0 3.14159 +7800.00 6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3 2 Character Literals ”A” ”%” ”9” ” ” ”z” ”(” 3 String Literals ”Hello, world!” ”Tutorials Point” ”19-NOV-12” 4 BOOLEAN Literals TRUE, FALSE, and NULL. 5 Date and Time Literals DATE ”1978-12-25”; TIMESTAMP ”2012-10-29 12:01:01”; To embed single quotes within a string literal, place two single quotes next to each other as shown in the following program − DECLARE message varchar2(30):= ”That””s tutorialspoint.com!”; BEGIN dbms_output.put_line(message); END; / When the above code is executed at the SQL prompt, it produces the following result − That”s tutorialspoint.com! PL/SQL procedure successfully completed. Print Page Previous Next Advertisements ”;