Useful real time sql snippets

www.sqlsnippets.com

write a function to return the count of rows in a table by passing table name as parameter......

create or replace function tab_count(p_table varchar2) return pls_integer
as
v_cnt pls_integer;
begin

execute immediate 'select count(1) from '||p_table into v_cnt;

return v_cnt;

end tab_count;
/

Swap YES,NO values in a table column with single update statement.....

update a set status=(case when status='YES' then 'NO'
  when status='NO' then 'YES'
end)

We can write select query in this way also....

select
(select count(*) from a),
(select count(*) from b),
(select count(*) from a,b)
from dual

How to get counts of first table ,second table and both tables in a join in single query?

select
(select count(*) from a),
(select count(*) from b),
(select count(*) from a,b)
from dual

How to trim spaces without using trim function in oracle?

Here is the code for the same
=============================

declare

v_str varchar2(20):=' ABCD ';
v_final varchar2(20);
pos pls_integer:=1;
begin

while pos <=length(v_str)
LOOP
if (substr(v_str,pos,1) != chr(32))
then
v_final := concat(v_final , substr(v_str,pos,1));
end if;
pos :=pos+1;
END LOOP;

dbms_output.put_line(v_final);

end;

What is the difference between case and decode in oracle?

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot – which we’ll see in this article.


1. CASE can work with logical operators other than ‘=’

2. CASE can work with predicates and searchable subqueries

3. CASE can work as a PL/SQL construct

4. Careful! CASE handles NULL differently

5. CASE expects datatype consistency, DECODE does not

what is the syntax of crontab file?

crontab file syntax as follows.


* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)

Give me the situation where truncate is not allowed on a table in oracle?

You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.

What is the difference between procedure and package?

1)Package name should be unique but procedures with same names across packages can exist.
2) support for encapsulation.
3)Break the dependency chain.
4)Package : Package is group of procedure, fuctions and data types ete. The package is faster as compared to procedure or function. The package will be loaded on the memory when first execution and will be available for subsequent calls, this way package is faster compared to procedure and functions.

can a cursor be overloaded?

cursors cannot be overloaded. cursor with 2 same names cann't exist.

How to delete duplicate rows in a table in oracle?

Here is the query for the same.

DELETE FROM
table_name A
WHERE
a.rowid >
(
SELECT
min(B.rowid)
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);

what is the difference between nested query and correlated query in oracle?

In nested query, sub query is executed first and then result will be pushed to the main query where clause.
whereas in correlated query is quite opposite. For each row returned by the main query, sub query will be executed.

How to do incremental data load from one table to another table in oracle?

Please use the outer join to do the incremental load from one table to another table.

insert into dup_emp
select a.* from emp a,dup_emp b
where a.EMPNO=b.empno(+)
and b.empno is null

How to do string aggregation in oracle?

Please follow the below link for the same.

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

How to find nth highest salary in oracle?

The following are the ways to find nth highest salary from emp table.

using correlated query

================

select * from emp a where 2=(select count(distinct sal) from emp b where a.sal <= b.sal
< div="">

using order by

===========

select * from

(

select sal,rownum rnum

from (

select sal

from emp a

where sal is not null

order by sal desc)

) x

where x.rnum=2

using connect by prior hierarchical query

=============================

select level,max(sal)

from emp

where level=2

connect by prior sal > sal

group by level

using analytic function

================

select * from ((select sal,dense_rank() over (order by sal desc) rank from emp)) where

rank=2

How to find week number for a given date in oracle?

The query as follows.

===================================

select trunc((sysdate- trunc(sysdate,'YYYY'))/7) from dual

What is the difference between datapump and old import/exportilities in oracle?

Datapump is a server side utility where as export/import is a user process. Datapump improves the performance dramatically over old export/import utilities, because the datapump processes running on the server have direct access to the datafiles and the SGA. They dont have to go via session. Also it is possible to launch a datapump job and then detach from it, leaving it running in the background. you can reconnect to the job to monitor its progress at any time.

How to add two dates in oracle?

We cann't add two dates in oracle.