Oracle IN : Maximum Number of Values

July 10, 2010 By Leave a Comment

In case you wonder what it the maximum number of values you can put inside the IN condition, it is 1000 as of Oracle 9i. It is documented in the link below.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96536/ch44.htm#288033.

Example:

SELECT * FROM emp
WHERE emp_no IN(1,2,3,4,5......1000);

I needed this because I was given a long list of values (around 500 values) and I need to fetch records in a table using that data. It would be easy if I can insert it first into a temp table and then join. Unfortunately, I only have read rights on the database so I need to use the IN condition. But I got concerned because in Oracle 7 and 8i releases, the maximum number of values you can put inside IN is a maximum of 255 (one byte).

Note:

Take note that the IN(SUBQUERY) does not have limitations because that’s essentially a join.

Example:
SELECT * FROM emp
WHERE emp_no IN(SELECT emp_no FROM dept_emp);

However as part of my best practices, I make sure that the results of the IN subquery is either one or none for optimal performance (not applicable in some instances).

Incoming search terms:

  • oracle maximum
  • oracle max
  • oracle in maximum
  • oracle number max value
  • maximum in oracle
  • max value in oracle number
  • oracle in values maximum number
  • oracle where in maximum
  • oracle where maximum
  • oracle maximum of in
Filed Under: Oracle

Enable/Disable Bind Variable in SQL*Plus

January 18, 2010 By Leave a Comment

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

Incoming search terms:

  • oracle disable bind variables
  • turn off bind variables sqldeveloper &
  • sqlplus disable bind variables
  • sqlplus bind variable assignment
  • sqlplus bind off
  • sql variable disable
  • sql plus set bind variable
  • sql disable variable
  • sql developer colon bind
  • sql * plus ask for input
Filed Under: Oracle, PL/SQL

Oracle WITH Clause (Subquery Refactoring)

December 17, 2009 By Leave a Comment

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.

Incoming search terms:

  • oracle with clause
  • with clause in oracle
  • oracle with clause syntax
  • oracle sql subquery refactor
  • subquery refactoring oracle
  • subquery optimization pl-sql
  • how to create the subquery in oracle using with clause
  • subquery factoring clause + procedure
  • random pl sql de un subquery
  • subquery sp oracle
Filed Under: Oracle, PL/SQL Tagged With: , , ,

Oracle CASE WHEN Statement

December 8, 2009 By Leave a Comment

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!

Incoming search terms:

  • oracle case when
  • oracle case sta
  • sql optimize case statement
  • performance select case statement in oracle
  • oracle case when syntax
  • oracle case when statement
  • oracle case when as
  • case statement in oracle
  • oracle case decode optimization
  • optimize the case statement in orac;e
Filed Under: Oracle, PL/SQL Tagged With: , , , , , ,

Could not locate OCI dll Error

September 23, 2009 By 13 Comments

If you are using 10g client with PL/SQL developer, you may encounter the message “Initialization error: Could not locate OCI dll” when launching PL/SQL Developer. It does not happen with the 8i client so I already suspected that it must be with the 10g client that I recently installed. Luckily, I found this thread in Oracle OTN forum and the solution seems to be easy.

All I have to do is copy a whole bunch of files from the ORAHOME folder to ORAHOME\bin. It seems like PL/SQL Developer is hardwired to find the files under the bin folder. It did the trick and my sql editor is now working. I hope it fixes yours too.

Here’s the screenshot of the files I copied. Take note that I copied them instead of just moving so as not to break anything.

oracle_files_copy_to_bin

Incoming search terms:

  • oci dll
  • Could not locate OCI dll
  • initialization error could not locate oci dll
  • toad could not locate oci dll
  • pl sql developer could not locate oci dll
  • plsql developer could not locate oci dll
  • oci dll was not found
  • oracle oci dll file
  • toad cannot find oci dll: oci dll
  • cannot find OCI DLL: oci dll
Filed Under: Oracle Tagged With: , ,

How to Get Oracle DB Version

September 17, 2009 By Leave a Comment

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.

Incoming search terms:

  • get oracle version
  • oracle select db version
  • select dbversion
Filed Under: Oracle, PL/SQL

Rename Table in Oracle

September 2, 2009 By Leave a Comment

SYNTAX

alter table
table_name
rename to
new_table_name;

Example

alter table
employee
rename to
employee_bak;

Incoming search terms:

  • rename oracle table
Filed Under: Oracle Tagged With: , , ,

Long to Varchar2

July 30, 2009 By Leave a Comment

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.

Incoming search terms:

  • oracle long to varchar
  • oracle long to varchar2
  • LONG to VARCHAR2
  • long to varchar
  • oracle long to char
  • ORACLE long varchar2
  • varchar2 long
  • LONG_TO_CHAR( in_rowid
  • sql long to varchar2
  • pl sql long to varchar2
Filed Under: Oracle, PL/SQL Tagged With: , , , ,

Explain Plan in Oracle SQL*Plus

March 23, 2009 By Leave a Comment

In case you only have SQL*Plus as your editor, you can use the following codes to show the execution plan of your query.

First Step : create plan
EXPLAIN PLAN FOR
SELECT field1, field2
FROM table;

Second Step: show results

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

You will need the necessary privileges to execute this. You’ll also need to have the PLAN table. If not, you’ll have to create it as that’s the placeholder where execution plan is written.

This technique is important especially when you don’t have a GUI-based SQL editor like PL/SQL Developer or TOAD.

Incoming search terms:

  • oracle explain plan sqlplus
  • explain in sql*plus
  • explain plan pl sql developer
  • how to generate the explain plan from sqlplus
  • oracle explain plan using SQL*PLUS
  • oracle plsql explain plan privileges
  • oracle sql developer execution plan
  • sqlplus explain plan
Filed Under: Oracle, PL/SQL Tagged With: , ,

Monitor Oracle DB Server Processes

March 20, 2009 By Leave a Comment

To see DB processes running on your Oracle DB, the code below can be used. Specially handy if you want to check which part of your package or stored procedure is taking time to execute. Indispensable for performance tuning.

SELECT sess.sid, sess.serial#, sess.sql_child_number, sess.sql_exec_id,
sess.process, sess.status, sess.username, sess.osuser, sess.program,
sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.type = 'USER'
--AND sess.osuser = 'yourwindowslogin';

Equivalent in SQL Server/Sybase

The equivalent command in Sybase or SQL Server is SP_WHO. However, the Oracle version provides more information.

For now, just this. I’ll provide additional details later. For additional fields, try to check the schema of v$session.

Incoming search terms:

  • sp_who in oracle
  • oracle sp_who
  • what is equivalent of sp_who2 in oracle
  • sp_who2 on siebel
  • sp_who pour oracle
  • sp_who oracle equivalent
  • sp_who oracle
  • equivalent of sp_who in oracle
  • oracle sp_who equivalent
  • oracle pl/sql sp_who
Filed Under: Blog, Oracle Tagged With: , ,
« Older Posts
Newer Posts »