May 4, 2000

Altering the Substitution Character in SQL*Plus

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

Indexing Foreign Key Columns in Child Tables

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

Pre-Compiled Database Triggers

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

Declaring Data Variables in Oracle PL/SQL Blocks

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

One-Step Compilation of Oracle Packages, Functions and Procedures

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’

Processing of Large Batch Transactions in Oracle

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

View SQL Behind System Stored Procedures

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