Using %TYPE is recommended, because if the type of the column in the table changes, then it is not necessary to change the application code. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated. This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab table. For example, the Get_emp_names procedure specification in "Parameters for Procedures and Functions" could be written as the following: PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE) Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. They are usually used to call stored procedures or to open cursor variables. IF Emp_number > 9999 OR Emp_number < 1000 THENĭBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number ||Īnonymous blocks are usually used interactively from a tool, such as SQL*Plus, or in a precompiler, OCI, or SQL*Module application. You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. WHERE Empno = Emp_number - no such numberĭBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name) ĭBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number) The following anonymous block handles the predefined Oracle Database exception NO_DATA_FOUND (which would result in an ORA-01403 error if not handled): DECLARE This allows your application to prevent the server from issuing an error that could cause the client application to end. Also, end the example with a slash (/) to activate it.Įxceptions let you handle Oracle Database error conditions within PL/SQL program logic. If you test this block using SQL*Plus, then enter the statement SET SERVEROUTPUT ON so that output using the DBMS_OUTPUT procedures (for example, PUT_LINE) is activated.