Oracle PLSQL Tutorial PL SQL Programming 168 codes

1 A complete example using RAISE_APPLICATION_ERROR

2 A PLSQL Block

3 A Safe INSERT Statement

4 A User-Defined Exception

5 A variable is null if a value hasnt been assigned to it yet

6 Add a row to the classes table, using the values of the variables

7 Adding User-Defined Exceptions

8 An example of an anonymous block

9 An example of using PRAGMA EXCEPTION_INIT

10 An example showing continuing program execution after handling exception

11 An example showing handling of pre-defined exceptions

12 Anonymous Block Structure

13 Anonymous blocks can be nested in the procedure and exception blocks in as many levels as you want

14 Assign custom exception a number

15 Assign SQL query results to PLSQL variables

16 Assigning a Name to Predefined Exception Code

17 Assigning values to variables

18 Avoiding exceptions raised in declaration part and exception handler

19 Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block

20 Call user defined function in DBMS_OUTPUT PUT_LINE

21 Catch cannot get lock exception

22 Catch custom exception

23 Catch divide by zero exception

24 Character and string literals in the Oracle world are enclosed by single quotes

25 Check OTHERS exception

26 Check SQL%NOTFOUND after delete statement

27 Check the result of count aggregation function and then raise exception

28 Code with Conditional Control to Avoid an Exception

29 Code with Explicit Handler for Predefined Exception

30 Code with No Exception Handler

31 Comments

32 Controlling scope with a variable declaration

33 Create your own no_data_found EXCEPTION

34 Data not found exception

35 DBMS_OUTPUT CHARARR

36 DBMS_OUTPUT GET_LINES

37 DBMS_RANDOM

38 DBMS_ROWID ROWID_BLOCK_NUMBER(ROWID)

39 Dbms_stats set_table_stats

40 Dbms_utility get_time

41 DBMS_WARNING package

42 Declare dbms_utility get_time as a variable default value

43 Declare variables in exception handler

44 Declaring a Variable by Reference

45 Declaring variables

46 Delete employee with stored procedure

47 Delete rows using PLSQL literals and variables

48 DELETE statement with variable

49 Delete table data in PLSQL

50 Delimiters

51 Demonstrate nested PLSQL blocks

52 Display error

53 DUP_VAL_ON_INDEX Exception

54 Each complete line of the PLSQL code must end with a semicolon (;)

55 Error message code and text

56 Examples of Integer and Real Literals

57 Exception Handling

58 Exceptions

59 Exceptions Raised in the Exception Handler

60 Get error information

61 Handling a custom exception

62 Handling an Unnamed Exception

63 Handling exceptions without halting the program

64 Handling user-defined exceptions with a WHEN clause

65 How DELETEs work with PLSQL

66 How DML works with PLSQL

67 How INSERTs work with PLSQL

68 In SQL statements, the names of database columns take precedence over the names of local variables and formal parameter

69 Including error messages in user-defined exceptions

70 Inline procedure

71 Inner function

72 Inner procedure in an anonymous function

73 Insert by Using RECORD Type Variable

74 Insert data in PLSQL block

75 Insert value to product and productcategory with stored procedure

76 Insert values using PLSQL literals and variables

77 INVALID_NUMBER Exception

78 Literals as variable values

79 Multi-line comments start with and end with

80 New quoting syntax is q[ ] where the [] is the user-defined delimiter

81 NO data found

82 Numeric literals cannot contain dollar signs or commas, but they can be written in scientific notation

83 ORA_CLIENT_IP_ADDRESS returns the client IP address as a VARCHAR2 datatype

84 ORA_DES_ENCRYPTED_PASSWORD returns the DES-encrypted password as a VARCHAR2 datatype

85 ORA_DICT_OBJ_NAME returns an object name as a VARCHAR2 datatype

86 ORA_DICT_OBJ_NAME_LIST

87 ORA_DICT_OBJ_OWNER returns an owner of the object acted upon by the event as a VARCHAR2 datatype

88 ORA_DICT_OBJ_OWNER_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype

89 ORA_DICT_OBJ_TYPE returns the datatype of the dictionary object changed by the event as a VARCHAR2 datatype

90 ORA_GRANTEE returns the number of elements in the list indexed by a PLS_INTEGER datatype

91 ORA_INSTANCE_NUM returns the current database instance number as a NUMBER datatype

92 ORA_IS_ALTER_COLUMN returns a true or false value as a BOOLEAN datatype

93 ORA_IS_CREATING_NESTED_TABLE returns a true or false value as a BOOLEAN datatype when you create a table with a nested

94 ORA_LOGIN_USER function returns the current schema name as a VARCHAR2 datatype

95 ORA_PRIVILEGE_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype

96 ORA_SERVER_ERROR returns an error number as a NUMBER datatype

97 ORA_SERVER_ERROR_MSG returns an error message text as a VARCHAR2 datatype

98 ORA_SYSEVENT function returns the system event that was responsible for firing the trigger as a VARCHAR2 datatype

99 ORA_WITH_GRANT_OPTION returns a true or false value as a BOOLEAN datatype

100 OTHERS Exception

101 Output NUMBER with dbms_output put_line Procedure

102 Output string with dbms_output put_line Procedure

103 PLSQL Block Showing the Use of the dbms_output put_line Procedure

104 Procedure for adjust salary

105 Propagation of Exceptions between Program Units

106 Propogating a Server-side Customized Error Number and Error Message to client program using PRAGMA EXCEPTION_INIT

107 Raise applocation error

108 Raise Exception in a function

109 Raising a custom exception

110 Raising an Exception in the Declaration Section

111 Raising an Exception Local PLSQL Block

112 Raising NO_DATA_FOUND

113 Scope Rules

114 Select into statement with exception catch statement

115 Select the first names for the Doe family from the Worker table

116 The forward slash on a line by itself says execute this procedure

117 The Lexical Set of Elements

118 The NULL Statement

119 The OTHERS Exception Handler

120 There are some restrictions on the declaration of variables

121 This is an anonymous procedure, so it has no name

122 TO_CHAR(d3,Day, Month dd, yyyy)

123 TO_CHAR(n1,999D99)

124 TO_CHAR(n2,$9G999D99)

125 TO_DATE(1102,mmddyy)

126 TO_DATE(1-1-1998,mm-dd-yyyy)

127 TO_DATE(Jan 1, 2000,mon dd, yyyy)

128 TO_NUMBER ($1,235 95,$9G999D99)

129 TO_NUMBER (123 99,999D99)

130 TOO_MANY_ROWS Exception

131 Types of more commonly used Exceptions

132 Understanding Different Exception Types

133 Update returning into

134 Update salary with stored procedure

135 UPDATE statement with variable

136 Update table and return if success

137 Update value and return affected row count

138 Use a nested block to catch exceptions from singleton SELECT

139 Use cursor variable in update statement

140 Use Declared variables in SQL statements

141 Use decode in PLSQL

142 Use LOOP to insert data to a table with loop counter variable

143 Use PLSQL to insert data to table

144 Use PLSQL variables with insert statement

145 Use RAISE_APPLICATION_ERROR to re throw exceptions

146 Use SQL%NOTFOUND after insert statement

147 Use the LOWER function to in DBMS_OUTPUT PUT_LINE

148 Use the pseudocolumn LEVEL

149 Use the ROUND function in PLSQL

150 Use TO_CHAR function in DBMS_OUTPUT PUT_LINE

151 Use TO_CHAR function in PLSQL

152 Use TO_DATE function in PLSQL

153 Use update statement in stored procedure

154 User-Defined Errors

155 Uses a PLSQL Nested Block

156 Using PRAGMA EXCEPTION_INIT

157 Using RAISE_APPLICATION_ERROR

158 Using SQLCODE and SQLERRM

159 Using SQLCODE for error code and SQLERRM for error message

160 Using TO_DATE Within PLSQL

161 Using two single-quotes print the string correctly

162 Using WHEN OTHERS clause

163 V_value_nr

164 Variables and Types

165 When the exception occurs, program control passes to the EXCEPTION block where the WHEN clause is examined for a matchi

166 Writing a simple program

167 Year 2000 problems Note the effect of using rr instead of yy

168 ZERO_DIVIDE Exception