Creating Constraints in Oracle

Constraints define conditions about a database that must remain true. They are used to ensure data integrity. Commonly used constraints are primary key constraints, foreign key constraints, and unique constraints. Constraints are also used to enforce non-null column values.

A constraint is typically defined as part of the table definition as is created using something similar to the following syntax:

CREATE TABLE ORDERS (
ORDER_ID NUMBER(7) NOT NULL,
PRODUCT_ID NUMBER(7) NOT NULL,
PRODUCT_NAME VARCHAR2(100) NOT NULL,
CONSTRAINT ORDER_ID_PK
PRIMARY KEY(ORDER_ID),
CONSTRAINT PRODUCTS_PRODUCT_ID_FK
FOREIGN KEY(PRODUCT_ID)
REFERENCES PRODUCTS(PRODUCT_ID)
)
The preceding table definition creates two constraints: one for the primary key on the ORDER_ID column; and one for the PRODUCT_ID column foreign key.

Constraints can also be created on a table using the ALTER TABLE syntax, as follows:

ALTER TABLE ORDERS
ADD CONSTRAINT PRODUCTS_PRODUCT_ID_FK
FOREIGN KEY(PRODUCT_ID)
REFERENCES PRODUCTS(PRODUCT_ID)

ColdFusion: variables, scopes and their ordering

In ColdFusion, it is generally a good idea to prefix your variables.

Why?  Performance and Ambiguity.

Performance

If you set a variable and then tried to output it:

<cfset myvariable = “my value”>
<cfoutput>#myvariable#</cfoutput>

ColdFusion will look into each scope until it finds the variable.

ColdFusion searches for variables without a scope the following order:

1. Query result (in a <cfoutput> loop)
2. Arguments (within a function or CFC)
3. variables (local)
4. CGI
5. URL
6. FORM
7. COOKIE
8. CLIENT

Ambiguity

If you set a variable the following way and output:

<cfset myvariable = “local variables scope”>
<cfcookie name=”myvariable” value=”cookie scope”>
<cfoutput>#myvariable#</cfoutput>

The result would be: “local variables scope”

This is because ColdFusion searched the scopes in order until it reached the local variables scope.

Both variables still exist and are accessible by using the scope prefix.

<cfoutput>
#variables.myvariable#<br />
#cookie.myvariable#
</cfoutput>

The result would be:

local variables scope
cookie scope

PHP: How To Concatenate Strings

In PHP, the operator to concatenate or combine strings together is: . (dot)

Here are some examples of usage when concatenating strings:

<?php

$string1 = ‘tech’;
$string2 = ‘saints’;
$string3 = ‘.com’;

$full = $string1.$string2.$string3;

echo $full;

?>

Result: techsaints.com

<?php

$string = ‘techsai’;
$string .= ‘nts’;
$string .= ‘.com’;

echo $str;

?>

Result: techsaints.com

<?php

$num = 123;

$string = $num.’ is a number’;

echo $string;

?>

Result: 123 is a number

When concatenating a number and a string, the string is automatically cast as a string.

Oracle varchar2 Data Type

The Oracle varchar2 data type allows for the storage of up to 4000 bytes of character data. It can be defined for a column as follows:

MyColumn varchar2(100) DEFAULT ‘MyString’ NOT NULL;

Note that the varchar data type is now deprecated in Oracle as it’s usage has become a synonym for varchar2, although this may change in future versions.

Oracle SET DEFINE OFF

The SET DEFINE OFF command in Oracle prevents Oracle from doing any variable substitution.

Use the SET DEFINE OFF command in Oracle to prevent Oracle from interpreting the ampersand for variable substitution.

The SET DEFINE ON command turns variable substitution back on.

Oracle DUAL Table

The Oracle DUAL table is a table created by Oracle and is used for selects statements that do not have a table name to go along with them.

For example, the DUAL table can be used to select pseudo columns, such as getting the currval or nextval from an Oracle sequence.

SELECT myseq.nextval FROM DUAL;

The DUAL table has one column, named “DUMMY,” which is a VARCHAR2 datatype of length 1. The value of the record is “X.”

All Oracle users have access to the DUAL table, although it is owned by “SYS.”

The DUAL table should not be updated, altered, or deleted.

Creating Oracle Tablespaces

The following syntax demonstrates how to create an Oracle tablespace:

CREATE TABLESPACE myTablespace
DATAFILE ‘/oracle/ts_mytablespace.dbf’
SIZE 100m
AUTOEXTEND on
NEXT 10m
MAXSIZE 500m;

The size of this tablespace is defined as 100 megabytes. By having the AUTOEXTEND attribute set to “on,” the statement is specifying that if the tablespace size exceeds 100 megabytes, more space will be allocated for the tablespace. The amount of space by which to extend the tablespace is specified by the “NEXT” attribute, which in this example is 10 megabytes. The tablespace will continue to grow until it reaches the maximum allowable size, which is determined by the “MAXSIZE” attribute – 500 megabytes.

Note: In order to create an Oracle tablespace, you must have the CREATE TABLESPACE privilege.

Oracle Tablespaces

A tablespace is the Oracle way of referring to a database file. A tablespace is a file set up to contain tables and is the primary logical storage structure for Oracle databases. Logical storage means the tablespace itself cannot be seen in the file system of the database server. The tablespace relates database objects to particular data files. Data files are physically located in the file system of the server.

An Oracle tablespace keeps information about various aspects of tables, including:

• Data types of columns
• Maximum length of data that is permissible in columns

Various types of tablespaces can exist in Oracle. A quite limited description of a few of these are:

Permanent tablespaces, such as the SYSTEM tablespace

• This is where Oracle’s data dictionary resides. If this table space gets deleted, your database will be gone.
• The SYSTEM tablespace is created by Oracle at the time the database is created.
• The SYSTEM tablespace stores the various units that comprise Oracle programs, such as packages, procedures, functions, and triggers.

Temporary tablespaces

• These are used primary for sort operations.
• Cannot contain permanent objects.

Undo tablespaces

• Stores records of transaction data before it is committed to the database.
• By creating an Undo tablespace, a DBA can control how long to keep rollback data.

The Oracle CLOB data type

When a large amount of alphanumeric data needs to be stored in one record, the CLOB data type should be used.

CLOB stands for “Character Large Object.” The CLOB data type allows for the storage of up to 4GB of data. It’s basically the same as the VARCHAR2 data type, with the exception that it allows for the storage of much longer strings of data.

Note that the CLOB data type stores only alphanumeric data. If it is necessary to store other types of data, such as sound or video, the BLOB data type should be used.

List All Stored Procedures in MySQL

To list all stored procedures in MySQL, simply execute the following command:

SHOW PROCEDURE STATUS;