Database Tips and Tricks

Regular Expressions

Regular expressions (regex) are powerful tools for pattern matching in strings. In Oracle, there are several functions that utilize regular expressions:

• REGEX_SUBSTR(string, pattern, position, which_occurrence, instruction_of_match): Returns the character string matched.
• REGEX_INSTR(string, pattern, position, which_occurrence, optional, instruction_of_match): Returns the position of the match. Set optional = 1 to get the position after the match.
• REGEX_REPLACE(string, pattern, replace, position, optional, instruction_of_match): Replaces the matched pattern in the string.
• CHECK (REGEXP_LIKE(column_name, regex_expression)): Checks if a column value matches the regex pattern.
• Useful information:
  • Instructions for matching include: c for case-sensitive, i for case-insensitive, n to match newline, m for multiline, and x to ignore white spaces.
  • [ ] matches any one character, while [^ ] matches any character except the specified one.
  • [:graph:] is equivalent to [A-Za-z0-9punctuation].
  • Quantifiers: + for one or more, * for zero or more, ? for zero or one, and {n1}, {n1,}, {n1,n2} for specific repetitions.
  • (ab|bc|cd) uses | as an OR operator.
  • Use to escape special characters.
  • ^ denotes the start of a string, while $ denotes the end.
  • The first captured expression is referenced as 1.

Session Control Statements

In Oracle, session control statements are used to manage the current session:

• ALTER SESSION: Modifies the current session’s settings.
• SET ROLE: Assigns roles to the current session.

System Control Statements

System control statements in Oracle are used for system-wide changes:

• ALTER SYSTEM: Alters system parameters.

Embedded SQL Statements

Embedded SQL statements refer to any Data Definition Language (DDL), Data Manipulation Language (DML), or Transaction Control Language (TCL) integrated into a third-generation programming language (3GL).

Miscellaneous Tips

Here are some additional miscellaneous tips and commands:

• System Privileges vs. Object Privileges: System privileges are like having a license to drive any car, while object privileges are specific to driving a particular car.
• CREATE USER username IDENTIFIED BY password: Creates a new user with the specified username and password.
• ALTER USER username IDENTIFIED BY password: Modifies the password for an existing user.
• DROP USER username: Removes a user from the database.
• DROP USER username CASCADE: Drops a user and all objects owned by the user.
• CONNECT username/password: Connects to the database using the specified username and password.