Enable/Disable Bind Variable in SQL*Plus
As state in the title, this post is about turning off the bind variable in SQL*Plus. Actually, this is also applicable in PL/SQL Developer and Toad but I'll be discussing more on SQL*Plus. You see, I have a problem wherein whenever I load my backup data (saved as insert statements) and there are ampersand (&) or colon(:) somewhere in the script, SQL*Plus will ask me for an input. Of course, I don't need that because as a backup, I want to load the data as it is. And when migrating 60MB or more, then it isn't funny to update the query one by one. The good thing is, I don't actually need to update the query because there's an answer to my problem. And it's pretty damn simple.
Syntax
set define off
Oh yes, that's just one line that will solve my misery. Well, it's all about my ignorance. To turn back on, then do the opposite as below.
Syntax
set define on
Oracle WITH Clause (Subquery Factoring)
WITH clause was introduced in Oracle 9i to provide powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries.
WITH Clause Syntax
WITH alias_name -- alias for the aggregate_query AS (aggregate_query_here) SELECT... -- Beginning of the query main body
It should be noted that multiple aggregate queries can be defined in the WITH clause as below.
WITH alias_name1 AS (agg_query1) , alias_name2 AS (agg_query2) SELECT...
When using subquery factoring, think of the aggregate_query as an in-line view. Actually, it is a view but a temporary one. Instead of creating a permanent view accessible in the database, you are creating a temporary one exclusively used by your main query.
Usage
I primarily use this in creating adhoc queries, reports and extractions. Of course, you can just create a view to accomplish this task. However, there are times wherein you are not allowed to create additional objects in the database for one reason or another. In that case, this subquery optimization comes very handy.
Caveat
You can't use this inside Oracle Forms. It also doesn't work inside stored procedures or anything with PL/SQL in it. At least I'm talking about until 11g.
Oracle CASE WHEN Statement
Another oracle sql statement that you may need in order to simplify and optimize your code is the use of CASE statement. Here's how it is being used.
Syntax #1
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
Example
SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;
Syntax #2
CASE
WHEN [ condition_1 ] THEN result_1
WHEN [ condition_2 ] THEN result_2
...
WHEN [ condition_n ] THEN result_n
ELSE result
END
Example
SELECT m.paper_type ,
m.paper_brand ,
m.paper_grammage,
m.paper_width ,
m.paper_length ,
m.paper_stock_no,
(
CASE
WHEN NVL(smc.qty_reserved_onhand,0) != NVL(st.sum_qty_r_oh,0) THEN 'QROH NOT TALLY'
WHEN NVL(smc.qty_reserved_incom,0) != NVL(st.sum_qty_r_incom,0) THEN 'QRINC NOT TALLY'
WHEN NVL(smc.ton_reserved_onhand,0) != NVL(st.sum_ton_r_oh,0) THEN 'TROH NOT TALLY'
WHEN NVL(smc.ton_reserved_incom,0) != NVL(st.sum_ton_r_incom,0) THEN 'TRINC NOT TALLY'
END
) ERROR_MESSAGE
from paper_stock_master m
Personally, I use Syntax #2 most of the time. I don't know why but maybe it just happened that I find it more suitable to use in the situations that I needed the CASE statement.
Difference Between DECODE and CASE WHEN
The most fundamental difference between the two oracle statements is that in DECODE, you are comparing a field or value in a given set of values or fields. It's actually a shorthand for IF..ELSIF..ELSE statement with the condition all set to equals (=). CASE WHEN is almost the same but you can use any conditional operator (i.e, =,!=, >, >= , <, <=, etc) thereby giving you more freedom and flexibility in writing your code.
In may instances CASE WHEN helped me reduce the number of lines of my codes as well as optimize the performance. On top of that, I no longer need a full pl/sql program with multiple queries and even cursors to be able to provide the result that a simple CASE statement can provide. However CASE, like any other sql commands, is only applicable for certain scenarios. It's not a magic keyword that will solve/apply to everything. So you have to weigh your options if you need it or not.
Ciao!
How to Get Oracle DB Version
I only know two ways of getting the Oracle DB version you are currently connected to. They are as follows:
SELECT *
FROM product_component_version
SELECT *
FROM v$version
This is quite handy if your program has SQL commands that are not applicable for earlier versions of Oracle Database.
Long to Varchar2
I got tangled with a problem on the datatype LONG in Oracle which is still being used in a number of applications. Siebel, is actually one of them. When I was tasked to extract some data from one of the tables in EIM using C++, I run into this problem.
I googled for answer and here's what I got.
CREATE OR REPLACE FUNCTION LONG_TO_CHAR( in_rowid rowid,in_owner
varchar,in_table_name varchar,in_column varchar2)
RETURN varchar AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--
begin
sql_cur := 'select '||in_column||' from
'||in_owner||'.'||in_table_name||' where rowid =
'||chr(39)||in_rowid||chr(39);
dbms_output.put_line (sql_cur);
execute immediate sql_cur into text_c1;
text_c1 := substr(text_c1, 1, 5000);
RETURN TEXT_C1;
END;
It's pretty simple but unfortunately, we can't do this without using function.
Example use:
SELECT MI_F_LONG_TO_CHAR(ROWID, 'SIEBEL','EIM_ORDER','ORD_X_COMMENTS')
FROM SIEBEL.EIM_ORDER
credit to Oracle Mag for this entry.
