Compare string from long column

Why it does not work?

Direct compare of long as varchar is not allowed in oracle 10 and previous.

What is that you see?

So if you do this

SELECT *
FROM all_triggers
WHERE trigger_name = 'Trigger_Name' AND trigger_body LIKE '%content%';

Here the long column is trigger_body

You will see a error like this
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

How do you solve this?

To solve this you need to do this.
Create a function like this. What this does is create a PL SQL function that does the compare for you, if you pass in the trigger name and the content that you are looking to find in there.

CREATE OR REPLACE FUNCTION t_instr (
p_trigname IN VARCHAR2,
p_lookfor IN VARCHAR2
)
RETURN NUMBER
AS
l_str LONG;
BEGIN
SELECT trigger_body
INTO l_str
FROM all_triggers
WHERE trigger_name = p_trigname;
RETURN INSTR (l_str, p_lookfor);
END;
/

And then do this to find if the value is present or not

SELECT decode(t_instr ('Trigger_name', 'Content that you are looking for'),0,'NOT Present','Present') status
FROM DUAL;

Finally do this to kick the function out.

DROP FUNCTION t_instr;

What is the useful link!

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:55212348054

oracle 10g drop primary key cascade does not drop index

Bug or feature?

First the theory.
If you have a table, that has a primary key, in the background there is a index created for the primary key.
If you are from the oracle 9 and previous realm then you would be thinking, if i drop the primary key and say cascade, then the index too would be dropped. And this is true as well.

Not so for Oracle 10g.
And this is a feature of 10G not a bug!! ;)

So what is the behaviour!

With 10g the index does not get dropped by default syntax.
Shut uo and show me the code! ;(

With Oracle 9 and previous
ALTER TABLE my_table DROP PRIMARY KEY CASCADE;

This will drop the primary key along with the index.

What is the new syntax?

If you use the syntax above the index does not get dropped.

To drop the index you need to do this.
ALTER TABLE my_table DROP PRIMARY KEY CASCADE DROP INDEX;


Whatz the other side?

The otherside is

ALTER TABLE my_table DROP PRIMARY KEY CASCADE KEEP INDEX;

Where did I find helpful link!

Here, http://www.orafaq.com/forum/t/59807/0/

SQL Injection

Ever wondered what is SQL injection??? What can we do with SQL injection...

Here you go http://en.wikipedia.org/wiki/SQL_injection

So how to avoid this attack.

Use PreparedStatement instead of Statement.
Reason: The PreparedStatement parses the parameters as Strings and hence these attacks cannot be possible.

SQL query tuning

The following link is a good starting point for SQL query optimization.

http://www.dba-oracle.com/art_sql_tune.htm

what is the use of rank() and dense_rank()

in SQL queries.

Welcome Note

Welcome to another in depth series!!