Author: Yuli Vasiliev

Oracle Pro Members Standard Members

External Table as a Data Loading Tool

External Table as a Data Loading Tool By Yuli Vasiliev The external tables feature can be used as a data loading tool – a good alternative to SQL Loader. You can create the external table with the SQL CREATE TABLE…ORGANIZATION EXTERNAL statement. Before you can do that, though, you have to set up a directory where the external table data…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Using OWB to Handle Heterogeneous Data Sources

Using OWB to Handle Heterogeneous Data Sources By Yuli Vasiliev Being an Oracle database user, you may need, at some point, to access data stored in a remote non-Oracle database system. This is where Oracle Warehouse Builder (OWB) – a powerful data integration tool pre-installed with Oracle Database – will prove useful. Using Oracle Warehouse Builder, you can perform extract,…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Implementing Row-Level Security with VPD

Implementing Row-Level Security with VPD By Yuli Vasiliev Virtual Private Database is a useful Oracle Database 11g Enterprise Edition feature that allows you to implement row-level security, centralizing access security mechanisms within your Oracle database. This article provides a simple example of how you might apply row-level VPD to the employees table from the HR/HR demonstration schema. To follow the…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

NULLs and Average

NULLs and Average Aggregate functions, such as MAX, MIN, AGR, and SUM ignore NULLs. That may be a problem if you want to aggregate all the rows, not just those with non-NULL values for the column of interest. To solve this problem, you can use the NVL function. Read on to learn how. For the article sample, you’ll need to…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Column-Level Security with Views

Column-Level Security with Views Perhaps using views is the best option when it comes to restricting access to a certain column or columns within a table. First, you create a view that selects all the columns from the underlying table except those ones you want to make inaccessible. Then, you grant the SELECT privilege on that view to your users,…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Column-Level Security with DECODE

Column-Level Security with DECODE To restrict access to a certain column or columns within a table, you might create a view upon that table hiding unnecessary columns as needed. Sometimes, however, making a certain column completely inaccessible is not exactly what you’d like to get. Instead, you might mask the column values so that the user can access only those…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Joining Relational Data and XML Data In a SQL Query

Joining Relational Data and XML Data In a SQL Query In this article, you’ll look at how you might merge external XML data into an Oracle database and then join that XML data with relational data stored inside the database within a single SQL query. To follow the article sample, you’ll first need to create an XML document to be…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Combining Multiple Queries In a Single One

Combining Multiple Queries In a Single One This is often the case when you need to query more than one table in a single query, or even issue more than one subquery to a single table, combining those subqueries in a single query. There are several types of queries that come within the above description, including joins, hierarchical queries, and…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Getting Started With Hierarchical Queries

Getting Started With Hierarchical Queries Hierarchical queries return rows in the order that forms a hierarchical tree where each row is located at a certain level in a hierarchy, having a parent, children, or both. The best way to understand this concept is to build some hierarchical queries against database objects that permit it. For example, you might issue such…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register
Oracle Pro Members Standard Members

Dealing With Sparse Date Data

Dealing With Sparse Date Data Generating a row per day within a specified date range is often necessary when you need to create a report showing daily results for each day in the range, regardless of whether there was an activity on a particular day or not. Suppose you need to generate a row for each day within the following…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale and Standard Member Monthly - Fall Sale members only.
Log In Register