PL/SQL Datatypes  «Prev 

Build a PL/SQL block using SQL*Plus

  1. To connect to the database, type a valid user name, password, and service. Type PETSTORE in the User Name box. Type GREATPETS in the Password box. Type MYDB in the Host String box. Click the OK button.
  2. To display the result of the PL/SQL block on the screen, set the server output on. Type the following command at the SQL > prompt:
    SET SERVEROUTPUT ON
    End your SET command by clicking ENTER key. This tells SQL*Plus to execute the command.
  3. Next, begin the main block with the DECLARE statement. Type DECLARE at the SQL > prompt. End your command by clicking Enter. This tells SQL*Plus to move to the next line.
  4. Next, declare a variable of type NUMBER and call it v_num. Type v_num NUMBER; at the 2 prompt. End your command by clicking Enter. This tells SQL*Plus to move to the next line.
  5. Start the executable section of the block. Type BEGIN at the 3 prompt. End your command by clicking Enter. This tells SQL*Plus to move to the next line.
  6. Assign the value of 42 to v_num. Type v_num := 42; at the 4 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  7. Next, add a comment to start the nested block. Type /* Start nested block */ at the 5 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  8. Next, declare the nested block. Type DECLARE at the 6 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  9. Declare v_char as a VARCHAR2(10) variable that you will use at a later stage to convert the number to a string. Type v_char VARCHAR2(10); at the 7 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  10. Start the executable section of the nested block. Type BEGIN at the 8 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  11. Convert the numeric value (v_num) to a character string using the TO_CHAR function and assign it to v_char. Type v_char := TO_CHAR(v_num); at the 9 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  12. Display the string value using the DBMS_OUTPUT.PUT_LINE package procedure. Type DBMS_OUTPUT.PUT_LINE (v_char); at the 10 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  13. End the nested block. Type END; at the 11 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  14. End the main block. Type END; at the 12 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
  15. Type / at the 13 prompt for SQL*Plus to compile your PL/SQL block. End your command by clicking ENTER.
  16. SQL*Plus now displays the result of the compilation of your block.