When Oracle executes PL/SQL program code it is interpreted. When compiled to native code, Oracle creates system dependent code. Native code is not interpreted by Oracle, but executed directly by the system. Read the rest of this entry »
You can use Database Link (dblink) to execute SQL against external database as if it was the database you are logged in to. For example, you might need to transfer sales information from a company branch located in Miami. Read the rest of this entry »
You can unlock Oracle user account that is locked by using the ALTER USER command. Read the rest of this entry »
Often we need SQL statements to use one value or another based on some condition that evaluated during the SQL statement execution. Oracle provides convenient functions like DECODE, NVL, NVL2, COALESCE that will return value based on some criteria. For complicated cases there is a CASE statement. With Oracle SQL CASE statement you can implement not only IF-THEN-ELSE logic, but also much more advanced logic. Read the rest of this entry »
To extract date parts in PL/SQL you can use the TO_CHAR function. For example, you can get the month number from a date value with the following expression TO_CHAR(date_value, 'mm');. There is better approach. You can use the EXTRACT function to get the date part you need. Read the rest of this entry »
You can use the ALTER TABLE command to rename tables in Oracle Database. Read the rest of this entry »
Temporary tables in Oracle database can be very useful tool. How to create temporary tables was described in another posting Using Temporary Tables in Oracle Database. Sometimes you need to remove existing temporary table. When you issue a DROP TABLE command you can get an error “ORA-14452: attempt to create, alter or drop an index on temporary table already in use”. Read the rest of this entry »
Temporary tables can be used to store intermediate results for complicated queries. This technique can result in stunning performance improvements. Here is how you can create temporary table in Oracle. Read the rest of this entry »
