Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Wednesday, February 20, 2013

Oracle SQL Articles

Credit goes to this website: http://www.oracle-base.com/articles/plsql/articles-plsql.php
Place your mouse cursor on each topic and click on link.

Oracle 9i

ANYDATA Type - This article presents an overview of the ANYDATA type.
ANSI ISO SQL Support In Oracle9i - Check out the new SQL enhancements to bring Oracle in line with the ANSI/ISO SQL: 1999 standards.
CASE Expressions And Statements - Learn how to use CASE expressions in both SQL and PL/SQL. In addition, learn how to use the CASE statement in PL/SQL.
DBMS_XPLAN - Easily format the output of an explain plan with this replacement for the utlxpls.sql script.
Flashback Query - Get a consistent view of the your data at a previous point in time.
MERGE Statement - Use the MERGE statement to quickly upsert data into tables.
Metadata API (DBMS_METADATA) - Extract DDL or XML definitions of all database objects using this simple API.
SQL New Features In Oracle9i - Check out the new SQL features with emphasis on those relevant for the Oracle9i Database: New Features For Administrators OCP exam.
SQL*Plus Web Reports - Generate HTML reports directly from SQL*Plus.
SQL/XML (SQLX) : Generating XML using SQL - Oracle9i Release 2 includes functionality to support the emerging SQL/XML standard to simplify XML generation from SQL queries.
XMLSEQUENCE - Use this operator to split multi-value results from XMLTYPE queries into multiple rows.
XMLType Datatype - Store XML documents in tables and query them using SQL.

Oracle 10g

Automatic SQL Tuning in Oracle Database 10g - This article the discusses the new features which automate the tuning of SQL statements in Oracle 10g.
Commit Enhancements in Oracle 10g Database Release 2 - Use the WRITE clause of the COMMIT command or the COMMIT_WRITE parameter to influence the way redo information is processed by commit operations.
DML Error Logging in Oracle 10g Database Release 2 - Use this new feature to capture errors and allow DML operations to complete successfully.
MERGE Statement Enhancements in Oracle Database 10g - Learn about the enhancements to the merge statement in Oracle Database 10g.
Flashback New Features and Enhancements in Oracle Database 10g - Use the new and enhanced features of flashback technology to view old data, track data changes and recover from accidental data loss without reverting to LogMiner or point in time recoveries.
SQL Access Advisor in Oracle Database 10g - Use this advisor to get suggestions for indexes and materialized views that might help system performance.
SQL*Plus Enhancements in Oracle Database 10g - Check out enhancements to SQL*Plus in Oracle Database 10g.
SQL trace, 10046, trcsess and tkprof in Oracle - An article that combines all previous SQL Trace, event 10046 and tkprof information, along with information on trcsess and DBMS_MONITOR from Oracle 10g Onward.

Oracle 11g

Automatic SQL Tuning in Oracle Database 11g Release 1 - Take advantage of the Automatic SQL Tuning features of Oracle 11g Release 1.
DDL With the WAIT Option (DDL_LOCK_TIMEOUT) - Avoid unnecessary "ORA-00054: resource busy" errors in 11g.
Enhanced Finer Grained Dependency Management in Oracle Database 11g Release 1 - See how finer grained dependency management in 11g reduces the extent of invalidations associated with schema changes.
Invisible Indexes in Oracle Database 11g Release 1 - Take control of the indexes available to the optimizer by using invisible indexes in 11g.
Miscellaneous New Features in Oracle Database 11g Release 1 - A collection of new features and enhancements listed in the miscellaneous section of
PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1 - Learn how to use the new PIVOT and UNPIVOT operators in Oracle Database 11g Release 1.
Query Result Cache in Oracle Database 11g Release 1 - Improve the performance of SQL across the whole database instance by caching query results.
SQL Access Advisor in Oracle Database 11g Release 1 - Use this advisor to get suggestions for indexes, materialized views and partitioning schemes to improve system performance.
SQL Performance Analyzer in Oracle Database 11g Release 1 - Compare the performance of the statements in an SQL tuning set before and after database changes.
SQL Plan Management in Oracle Database 11g Release 1 - Maintain consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.
UTL_MATCH : String Matching by Testing Levels of Similarity/Difference - Use the UTL_MATCH package to determine the similarity between two strings.

Miscellaneous

ALL, ANY and SOME Comparison Conditions in SQL - A quick look at some comparison conditions you may not be used to seeing in SQL against an Oracle database.
Analytic Functions - An introduction to analytic functions in Oracle.
Deadlocks - This article shows the steps necessary to identify the offending application code when a deadlock is detected.
DML RETURNING INTO Clause - This article describes the use of the RETURNING INTO clause in DML.
Dynamic IN-Lists - This article presents a number of methods for parameterizing the IN-list of a query.
Efficient Function Calls From SQL - This article presents a number of solutions for making calls to PL/SQL functions from SQL more efficient.
Efficient SQL Statements - A brief non-version specific guide to writing efficient SQL statements.
FIRST_VALUE and LAST_VALUE Analytic Functions - Simple examples of how to use these analytic functions.
Identifying Host Names and IP Addresses - This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.
LAG and LEAD Analytic Functions - Simple examples of how to use these analytic functions.
Literals, Substitution Variables and Bind Variables - The article compares the affect of using literals, substitution variables and bind variables on memory and CPU usage.
Naming Conventions - Some of the typical naming conventions used for objects in Designer and the database.
NULL-Related Functions (NVL, DECODE, NVL2, COALESCE, NULLIF, LNNVL, NANVL, SYS_OP_MAP_NONNULL) - A summary of the functions available for handling NULL values.
Overlapping Date Ranges - This article presents simple methods to test for overlapping date ranges.
RANK, DENSE_RANK, FIRST and LAST Analytic Functions - Simple examples of how to use these analytic functions.
Recompiling Invalid Schema Objects - This article presents several methods for recompiling invalid schema objects.
ROLLUP, CUBE, GROUPING Functions and GROUPING SETS - An overview of some functionality available for aggregation in data warehouses.
Schema Owners and Application Users - Protect your schema objects by defining application users with the necessary privileges on those objects, rather than allowing direct connections to the schema owner.
String Aggregation Techniques - Several methods of combining multiple rows of data into a single row using aggregate functions.
Top-N Queries - Top-N queries provide a method for limiting the number of rows returned from ordered sets of data.
Using Ref Cursors To Return Recordsets - Return recordsets from Oracle stored procedures.
Virtual Indexes - Use virtual indexes to test alternate indexing strategies with no impact on the rest of your system.
WITH Clause - Use the WITH clause to reduce repetition and simplify complex SQL statements.

Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)