Q&A: Oracle Basic

How to make a procedure private?
Don't declare it in package specification.

What is ref cursor?
It is cursor variable like a pointer in C. can point to any query. Used to pass data set between program units.
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php

When a ref cursor open, who use and close this ref cursor?
The program unit who receive the data set will use and close the cursor, it may be other packages or front end code or Reporting tools.

What is an autonomous transaction?
Autonomous transaction is the transaction initiated by another transaction. The key point is it allows the separate commit/rollback transactions.

What are two exceptions to be careful of when using a select INTO statement?
1. When records not return, it would generate NOT_DATA_FOUND Found error message.
2. TOO_NAMY_ROWS can be another problem.

What is %type? What are the advantages of using this over datatypes?
%type means matching the date type of original data source. It can make sure there would be no problem with date type matching issue. On other hand, if declare an own datatype, when source data by chance is different from this declaration, there would be a data type consistence issue.

In PL/SQL how can you test that an update statement updated no rows?
SQL%ROWCOUNT can be used to test for this purpose.

Give examples of collections in oracle and their differences?
# Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
# Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
# Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Have you used Bulk Collection? If yes then:"Why is bulk collection more efficient"?
Without bulk collection, every single row would request PL/SQL server to call SQL server one by one. By using bulk collection, say 5000 in a patch, the PL/SQL server will call SQL server for every 5000 rows. This can be very efficient.

What is the difference between a function and a procedure?
1. Functions are normally used for computations where as procedures are normally used for executing business logic.
2. Function is mainly used in the case where it must return 1 value, which can be scalar value or table or table values. Where as a procedure may not return a value (except through out parameter). If procedure mistakenly called, is returns always integer zero.
3. Procedure can call in another project but function work in same project.
4. We can't have any DDL, DML and TLC command inside a function, if that function is called from a SQL query as a UDF(user defined function). But if the function is not called from SQL query then we can have all transactional statement(DDL, DML and TLC) inside a function. Procedure can not be called from the SQL statements.

When a procedure within a package (containting multiple procedures) is called, what will get loaded into memory, the whole package or just the procedure?
Whole package will be loaded into memory.

How many triggers exist?
Trigger Types
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points:
•Before the triggering statement executes
•After the triggering statement executes
•Before each row that the triggering statement affects
•After each row that the triggering statement affects
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.

How to know if index is being used?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: SELECT table_name, index_name, monitoring, used FROM v$object_usage;

Optional Parameters In SQL Server Reporting Services

Optional parameters In SSRS means if parameters have been selected, it would be passed back to SQL query as condition in WHERE clause, and when no parameter(s) have been selected, the WHERE condition should be not in use. Dynamic SQL is not the best solution. Following solution is much better:

SELECT Col_A
, Col_B
FROM Tab
WHERE (Col_A=@para_A OR @para_A IS NULL)
AND (Col_B=@para_B OR @para_B IS NULL)

What make life harder is when parameters are selected, but due to lack of records, the result is nil? These would be happened often when multiple parameters get involved. Depends on situations, you may wish to abandon that condition completely. So, the real issue is not if you have option to select a particular parameter, it is if you have selected that parameter but it turns out nil result and you need to abandon it after this discovery.

There is no programmatic solution so far. However, following approach may help:

1, Limit the number of parameters, because you will need to combine them in matrix. Too many parameters will increase the task load exponentially.

2, Classify parameters into hierarchy.

3, Write queries for each combination case of the parameters. Hierarchical structure of parameters will help a lot.

4, From low position in hierarchy to pick up the best available result amongst the results generated from above queries. Following is an example to do this procedure:

; SET @Stopper = 0
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_1' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_2' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
...

What you need to do next is in SSRS to enable the optional parameters. While you have made it possible to accept the NULL value in your back-end queries, by default the report interface of SSRS cannot be flagged out the parameters. The users would have no choice but to select parameters. In this case, include NULL value record for your parameter data set:

SELECT NULL AS Id
, 'Unknown' AS Name
UNION
SELECT DISTINCT Id
, Name
FROM Tab

If you have problem on how to position this NULL value record, please refer to following article:

Special Function of ORDER BY Clause in T-SQL

Next, in Report Parameters window of SSRS, make sure the "Allow null value" option has been selected for this particular parameter.

Done.


http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx

Special Function of ORDER BY Clause in T-SQL

Compare to MS Access Jet Engine SQL, T-SQL's ORDER BY clause is flexible, which can point either the column name or alias of the column. Which special function can be used for special purpose. Here is any example. Let us say, you need a data set in order:

SELECT DISTINCT Name
FROM Tab
ORDER BY Name

Then you need to add a record called "Unknown" at the top:

SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab

There is problem, you won't able to add ORDER BY clause in T-SQL because you used UNION. Following is next step:

SELECT Name
FROM
( SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

You won't be able to get the right result, because "Unknown" is not on the top. Solution is:

SELECT CASE WHEN Name = '0000' THEN 'Unknown' ELSE Name END AS New_Name
FROM
( SELECT '0000' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

Why use '0000'? Because it would be on the top after sort. In this case, if you use

ORDER BY New_Name

it won't work.


http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html

IF ELSE Statement

The orthodox IF ELSE Statement is IF Condition_1 THEN Function_1 ELSE Function_2. Indeed, this is a simplified statement of: IF Condition_1 THEN Function_1 ELSE IF Condition_2 THEN Function_2. By saying that, the perfect relationship between Condition_1 (C1) and Condition_2 (C2) consists of:

1. The union of Condition_1 and Condition_2 (C1 ∪ C2) covers every possible situations, while
2. they do not have intersection (A ∩ B = 0).

For instances:

Example 1: if(0<C) {F1} else {F2}
Example 2: if(0<C) {F1} else if(0>C) {F2}
Example 3: if(0<=C) {F1} else if(0=>C) {F2}

Example 1 is perfect okay because the implicit Condition_2 covers everything else other than Condition_1. Example 2, however is not such perfect because it does not cover every situations, thus leave a possible logic bug. Example 3 contains logic error because there is intersection between two conditions. It is therefore suggested when use IF ELSE Statement, it shall cover all situations but not overlapped unless you are fully aware what was left behind.

However, when two sets of conditions apply, it because much complicated. For instance:

if(0<C) {F1}
else if(0==C || 100==C2) {F2}
else if(0>C) {F3}
else {F4};

You would never get clear logic set here. Actually, you can, after conditions cover full combinations/metrics of two sets. What about three sets conditions? You should try to avoid this kind of situation. However, if you can't, there is a suggestion:

1. Employ a middle layer, say Action, between Conditions and Functions. Unlike Functions, Actions are just flags of Functions and some of them can override others.
2. Use several IF Statements instead of a single IF ELSE Statement to cover every situations selected by you.
3. Because some Actions may override others, so, careful arranging the orders of IF Statements is necessary.

Here is the example:

if(0==C || 100==C2) {A1};
if(0<C) {A2};
if(0>C || 1!=C3) {A3};
if(0>C) {A4};
if (A1) {F3}
else if (A2) {F1}
else if (A3) {F4}
else {F3};

Done. Just be very careful all necessary situations need to be covered and action overriding order need to be correctly arranged. Else, you just prepare to deal with the logic bugs.

Percentage Format in T-SQL

There is no function to present the percentage format in T-SQL, something like 17.25%. There are various ways to do this. However, for reporting purpose, the easiest way might be:

CONVERT(VARCHAR, Result_From_Calc)+'%'

Following is an example how to add Total row at bottom of the selected dataset as will as add percentage column for each of rows.

The original dataset is:

SELECT Leg_Id
, Statute_Miles
FROM dw_Legs
GO

The final script is as follows:

WITH Totaling
AS
(
SELECT SUM(Statute_Miles) AS Milage
FROM dw_Legs
)
SELECT Leg
, Milage
, CONVERT(VARCHAR, Milage*100/(SELECT Milage FROM Totaling))+'%' AS Percentage
FROM
(
SELECT CONVERT(VARCHAR, Leg_Id) AS Leg
, Statute_Miles AS Milage
FROM dw_Legs
UNION
SELECT 'Total' AS Leg
, Milage
FROM Totaling
) AS Dataset
GO

Explaination:

1. Total milage will be used more than once, so it has been in put into WITH clause.
2. The reason convert Leg_Id into VARCHAR is because word ‘Total’ is a VARHCAR. Else the UNION won’t work.
3. Make sure there is only one row being selected into WITH clause. That is where people usually create a bug.

Following is the result: