{"id":1472,"date":"2017-11-12T17:26:59","date_gmt":"2017-11-12T10:26:59","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=1472"},"modified":"2025-06-09T00:02:44","modified_gmt":"2025-06-09T07:02:44","slug":"plsql-anonymous-block","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-anonymous-block\/","title":{"rendered":"PL\/SQL Anonymous Block"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn about the PL\/SQL anonymous block and how to execute it using SQL*Plus and SQL Developer tools.<\/p>\n\n\n\n

PL\/SQL anonymous block overview #<\/a><\/h2>\n\n\n\n

PL\/SQL is a block-structured language whose code is organized into blocks<\/strong>.<\/p>\n\n\n\n

A block consists of three sections:<\/p>\n\n\n\n

    \n
  1. Declaration Section<\/li>\n\n\n\n
  2. Executable Section<\/li>\n\n\n\n
  3. Exception-handling Section<\/li>\n<\/ol>\n\n\n\n

    The executable section is mandatory, while the declaration and exception-handling sections are optional.<\/p>\n\n\n\n

    A block may have a name. Functions<\/a> or Procedures<\/a> are examples of named blocks. Oracle Database stores named blocks in the server so you can reuse them later.<\/p>\n\n\n\n

    A block without a name is called an anonymous block<\/strong>. Since anonymous blocks are not saved in the Oracle Database server, they are only for one-time use. However, PL\/SQL anonymous blocks can be helpful for testing purposes.<\/p>\n\n\n\n

    The following picture illustrates the structure of a PL\/SQL block:<\/p>\n\n\n\n

    \"PL\/SQL<\/figure>\n\n\n\n

    1) Declaration section<\/strong><\/p>\n\n\n\n

    In the declaration section, you declare<\/a><\/span> variables<\/a>, allocate memory for cursors<\/a>, and define data types.<\/p>\n\n\n\n

    2) Executable section<\/strong><\/p>\n\n\n\n

    An executable section starts with the keyword BEGIN<\/code> and ends with the keyword END<\/code>. The executable section must have a least one executable statement, even if it is a NULL<\/code> statement<\/a> that does nothing.<\/p>\n\n\n\n

    3) Exception-handling section<\/strong><\/p>\n\n\n\n

    An exception-handling section that starts with the keyword EXCEPTION<\/a><\/code>. In the exception-handling section, you catch and handle exceptions raised by the code in the execution section.<\/p>\n\n\n\n

    Note a block itself is an executable statement, therefore you can nest a block within other blocks.<\/p>\n\n\n\n

    PL\/SQL anonymous block example #<\/a><\/h2>\n\n\n\n

    The following example shows a simple PL\/SQL anonymous block with one executable section.<\/p>\n\n\n

    BEGIN<\/span>\n   DBMS_OUTPUT.put_line ('Hello World!'<\/span>);\nEND<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The executable section calls the DMBS_OUTPUT.PUT_LINE<\/code> procedure to display the \"Hello World\"<\/code> message on the screen.<\/p>\n\n\n\n
    Execute a PL\/SQL anonymous block using SQL*Plus. #<\/a><\/h2>\n\n\n\n
    Once you have the code of an anonymous block, you can execute it using SQL*Plus, a command-line interface for executing SQL statements and PL\/SQL blocks provided by Oracle Database.<\/p>\n\n\n\n
    The following picture illustrates how to execute a PL\/SQL block using SQL*Plus:<\/p>\n\n\n\n
    \"PL\/SQL<\/figure>\n\n\n\n
    First, connect to the Oracle Database server using a username and password.<\/p>\n\n\n\n
    Second, turn on the server output using the SET SERVEROUTPUT ON<\/code> command so that the DBMS_OUTPUT.PUT_LINE<\/code> procedure will display text on the screen.<\/p>\n\n\n\n
    Third, type the code of the block and enter a forward slash ( \/<\/code>) to instruct SQL*Plus to execute the block. Once you type the forward slash (\/), SQL*Plus will execute the block and display the Hello World<\/code> message on the screen as shown in the illustrations.<\/p>\n\n\n\n
    Note that you must execute SET SERVEROUTPUT ON<\/code> command in every session that you connect to the Oracle Database to show the message using the DBMS_OUTPUT.PUT_LINE<\/code> procedure.<\/p>\n\n\n\n
    To execute the block that you have entered, you use \/<\/code> command instead of typing everything from scratch:<\/p>\n\n\n\n
    \"plsql<\/figure>\n\n\n\n
    If you want to edit the code block, use the edit<\/code> command. SQL*Plus will write the code block to a file and open it in a text editor as shown in the following picture:<\/p>\n\n\n\n
    \"plsql<\/figure>\n\n\n\n
    You can change the contents of the file like the following:<\/p>\n\n\n
    begin<\/span>\n\tdbms_output.put_line('Hello There'<\/span>);\nend<\/span>;\n\/<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Save and close the file. The contents of the file will be written to the buffer and recompiled.<\/p>\n\n\n\n
    After that, you can execute the code block again; it will use the new code:<\/p>\n\n\n\n
    \"plsql<\/figure>\n\n\n\n
    Execute a PL\/SQL anonymous block using SQL Developer #<\/a><\/h2>\n\n\n\n
    First, connect to the Oracle Database server using Oracle SQL Developer.<\/p>\n\n\n\n
    Second, create a new SQL file named anonymous-block.sql<\/code> in the C:\\plsql<\/code> directory.<\/p>\n\n\n\n
    \"PL\/SQL<\/figure>\n\n\n\n
    \"PL\/SQL<\/figure>\n\n\n\n
    Third, enter the PL\/SQL code and execute it by clicking the Execute<\/strong> button or pressing the Ctrl-Enter<\/strong> keyboard shortcut.<\/p>\n\n\n\n
    \"PL\/SQL<\/figure>\n\n\n\n
    More PL\/SQL anonymous block examples #<\/a><\/h2>\n\n\n\n
    In this example, we first declare a variable<\/a> l_message<\/code> that holds the greeting message. And then, in the execution section, we use the DBMS_OUTPUT.PUTLINE<\/code> procedure to show the content of this variable instead of using a literal string.<\/p>\n\n\n
    DECLARE<\/span>\n  l_message VARCHAR2( 255<\/span> ) := 'Hello World!'<\/span>;\nBEGIN<\/span>\n  DBMS_OUTPUT.PUT_LINE( l_message );\nEND<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the output:<\/p>\n\n\n\n
    Hello World!<\/pre>\n\n\n\n
    The following anonymous block example adds an exception-handling<\/a> section that catches ZERO_DIVIDE<\/code> exception raised in the executable section and displays an error message.<\/p>\n\n\n
    DECLARE<\/span>\n      v_result NUMBER;\nBEGIN<\/span>\n   v_result := 1<\/span> \/ 0<\/span>;\n   EXCEPTION<\/span>\n      WHEN<\/span> ZERO_DIVIDE THEN<\/span>\n         DBMS_OUTPUT.PUT_LINE( SQLERRM<\/span> );\nEND<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The error message is:<\/p>\n\n\n
    ORA-01476: divisor is equal to zero<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary #<\/a><\/h2>\n\n\n\n
      \n
    • PL\/SQL anonymous blocks are unnamed, temporary PL\/SQL code units.<\/li>\n\n\n\n
    • Use anonymous blocks for performing ad-hoc database operations.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t