When you are inserting data into Oracle tables in SQL*Plus using hard-coded string, some of the character fields may contain the ampersand character (&). The following is an example: insert into dept (deptno, deptname) values(20,’R&D’); However, within SQL*Plus, “&” is treated as a substitution character by default. Thus, the above
Although an index is not mandatory on your foreign keys, if they are not indexed you will cause additional locking when certain Data Manipulation Language statements are executed against the parent table. Without the index, checking for existing child records when attempting a delete of a master record (cascade or
Many programmers create stored procedures out of large bodies of database trigger codes. This has primarily been because while stored procedures are stored in compiled form, database triggers are compiled each time they get fired. But you no longer have to split your database trigger code into multiple procedures because
In a PL/SQL block, if you are querying a database table, you need to declare data variables to hold column values returned from the query. The variables can be declared of the same explicit datatype as the column in the table. Let’s say we create a table: create table dept
The DBMS_UTILITY.COMPILE_SCHEMA procedure compiles all procedures, functions and packages in the specified schema. DBAs now don’t have to generate the dynamic scripts to recompile the invalid objects in the schema and then recompile them. The built-in package can be called from a SQL*Plus as shown below: exec DBMS_UTILITY.COMPILE_SCHEMA(‘USER’) The ‘USER’
PL/SQL supports three parameter-passing modes: IN
At some point, you must have encountered a “Snapshot too old” error when running a huge batch transaction. Then you find that you may have to restart the process all over again when 80 percent of the transaction was complete when the error occurred. Ask your DBA to create a
If you are ever trying to write SQL to access SQL server’s system tables, it is often useful to look at SQL Server’s own system stored procedures. You can do this by simply running EXEC sp_helptext system_proc_name (where system_proc_name is the name of the stored procedure you want see the