Oracle Database 10g Pl_Sql Programming

Thank you for purchasing Oracle 10g PL/SQL Programming! The examples provided here are divided by chapter, and match those found in the book. Each chapter contains a script to create the schema with all necessary permissions. The permissions granted for the schemas change between chapters, so be sure to run the schema creation script for each chapter and not rely on the one created in another chapter. To avoid permissions problems it is highly recommended that the schema creation script we provide be used, but you may also create your own schema as long as all required privileges are granted. The examples for each chapter are created to run independent of objects created in other chapters. In other words - no cross-chapter dependencies. This means you can jump from chapter 10 to 17, and have no problem running chapter 17's examples. When creating your database, be sure to install Oracle Text as it is used for examples in multiple chapters. If not installed, some examples will not work and you will receive errors on schema creation. Oracle Text is included in both Standard and Enterprise editions, so either release can be used. All examples were tested on Windows and Linux ports of Oracle 10g Release 1. Should you have ANY problem with the examples, feel free to e-mail FEEDBACK@PLSQLBOOK.COM, and we will respond to the issue promptly. EXAMPLES Chapter 1: Introduction to PL/SQL ------------------------------------ CreateUser.sql This script creates the plsql user for chapter 1 examples. You must run this script as SYS or SYSTEM as SYSDBA. The script can be rerun. PlsqlBlock.sql This script contains an example of a PL/SQL block of code. Chapter 2: Using SQL*Plus and JDeveloper ------------------------------------------ CreateUser.sql This script creates the plsql user for chapter 2 examples. You must run this script as SYS or SYSTEM as SYSDBA. The script can be rerun. DbmsOutput.sql This script demonstrates the DBMS_OUTPUT package. Debug.sql This script demonstrates debugging PL/SQL with JDeveloper. Chapter 3: PL/SQL Basics -------------------------- CreateUser.sql This script creates the plsql user for chapter 3 examples. You must run this script as SYS or SYSTEM as SYSDBA. The script can be rerun. AnonymousBlock.sql This script demonstrates the structure of an anonymous block. BindVariables.sql This script demonstrates the use of bind variables. BlockStructure.sql This script demonstrates the structure of a block. Boolean.sql This script demonstrates how to assign a value to a variable of type boolean. BooleanLiteral.sql This script demonstrates Boolean literals. Case.sql This script demonstrates CASE. CompileError.sql This script demonstrates the compile time warnings generated by Oracle. CompileWarning.sql This script demonstrates the DBMS_WARNING package. DateTimeLiteral.sql This script demonstrates Date/Time literals. Goto.sql This script demonstrates the GOTO command. If.sql This script demonstrates IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. Interval.sql This script demonstrates the use of the INTERVAL types. Loop.sql This script demonstrates LOOPs. MultiLineComment.sql This script demonstrates the use of multi-line comment blocks. NamedBlock.sql This script demonstrates the structure of a named block. NestedBlock.sql This script demonstrates a nested block. Number.sql This script demonstrates the NUMBER datatype. PLSQL_Warnings.sql This script demonstrates the PLSQL_WARNINGS parameter. Ref_Cursor.sql This script demonstrates the use of REF CURSOR. Reserved.sql This script prints a list of reserved words. SingleLineComment.sql This script demonstrates the use of single line comments. StringLiteral.sql This script demonstrates the different ways in which 10g handles apostrophes. Timestamp.sql This script demonstrates the TIMESTAMP datatypes. Trigger.sql This script demonstrates the use of a trigger. Variables.sql This script declares a variable as a constant then attempts to change the assigned value. Visibility.sql This script demonstrates variable visibility. WhiteSpace.sql This script demonstrates shows poor design when white space is not used. WrapAfter.plb This file shows what a wrapped file looks like. WrapBefore.sql This file is used in the example showing how to wrap PL/SQL. WrapSeed.sql This script demonstrates the Wrap utility. Chapter 4: Using SQL with PL/SQL ---------------------------------- CreateUser.sql This script creates the plsql user for chapter 4 examples. You must run this script as SYS or SYSTEM as SYSDBA. The script can be rerun. Autonomous.sql This script demonstrates how autonomous transactions work. BasicSelect.sql This script demonstrates how to use a basic select statement in a PL/SQL block. ContextArea1.sql This script shows how cursors work with the context area, and remain consistent after opened. ContextArea2.sql This script demonstrates how changes to the database impact processing of records after a cursor is opened. Conversion.sql This script demonstrates the TO_CHAR and TO_DATE conversion functions. CursorForLoop.sql This script demonstrates the use of the Cursor For Loop. CursorSubquery.sql This script demonstrates the use of the cursor subquery. CursorVariable1.sql This script demonstrates the use of REF CURSOR. CursorVariable2.sql This script demonstrates the use of SYS_REFCURSOR. DateTime.sql This script demonstrates a few date functions. DDL.sql This script demonstrates how DDL doesn't work with PL/SQL. Delete.sql This script demonstrates how DELETEs work with PL/SQL. Error.sql This script demonstrates error functions SQLERRM and SQLCODE. ExplicitAttribute.sql This script demonstrates the use of cursor attributes. GreatestLeast.sql This script demonstrates the Greatest and Least functions. ImplicitAttribute.sql This script demonstrates the use of cursor attributes. Insert.sql This script demonstrates how INSERTs work with PL/SQL. Level.sql This script shows the pseudocolumn LEVEL and an example of using the levels. LevelUpdate.sql This script shows the pseudocolumn LEVEL and an example of using the levels with an update. Like.sql This script demonstrates the use of the LIKE operator. LockSession1.sql This script demonstrates how transactions use locks, commits, and rollbacks. This script is for the first session as noted in the book. LockSession2.sql This script demonstrates how transactions use locks, commits, and rollbacks. This script is for the second session as noted in the book. Lower.sql This script demonstrates the LOWER function. NDS.sql This script demonstrates how NDS works with PL/SQL. OpenCursor.sql This script demonstrates what happens when the number of cursors opened exceeds the open_cursors value. For this example, set the value of open_cursors to 20 in the init.ora file. RegexpLike.sql This script demonstrates the use of the REGEXP_LIKE function. Round.sql This script demonstrates the ROUND function. RowID.sql This script demonstrates the use of the rowid pseudo column. Savepoint.sql This script demonstrates how transactions use savepoints. SimpleLoop.sql This script shows how simple loops are written. TextIndex.sql This script demonstrates indexing using Oracle Text. Update.sql This script demonstrates how UPDATEs work with PL/SQL. UpdateDual.sql This script demonstrates how DML works with PL/SQL. WhereCurrentOf.sql This script demonstrates how UPDATEs work with PL/SQL and the WHERE CURRENT OF clause. WhileLoop.sql This script demonstrates the use of the WHILE loop. Chapter 5: Records -------------------- Chapter 6: Collections ------------------------- Chapter 7: Error Handling ---------------------------