Some Interesting SQL Queries Part2

Computing the Factorial of a number (n!)

CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
BEGIN
IF n = 1 THEN -- terminating condition
RETURN 1;
ELSE
RETURN n * fac(n - 1); -- recursive call
END IF;
END fac;
/

-- Test n!
SELECT fac(1), fac(2), fac(3), fac(4), fac(5) FROM dual;

sql to print string vertically

SELECT SUBSTR ('&&String', ROWNUM, 1)
FROM all_tables
WHERE ROWNUM <= LENGTH (TRIM ('&STRING'));

Procedure to reverse a string

create or replace procedure rev(x in varchar2) as
c char(1);
i number;
begin
for i in 1..length(x) loop
select substr(x,length(x)-i+1,1) into c from dual;
dbms_output.put(c);
end loop;
dbms_output.put_line(' ');
end;
/

SQL> set serverout on
SQL> exec rev('Java')
avaJ

Display the PL/SQL Dependency Tree

SELECT lvl, u.object_id, u.object_type, LPAD (' ', lvl) || object_name obj
FROM (SELECT LEVEL lvl, object_id
FROM SYS.public_dependency s
START WITH s.object_id =
(SELECT object_id
FROM user_objects
WHERE object_name = UPPER ('&OBJECT_NAME')
AND object_type = UPPER ('&OBJECT_TYPE'))
CONNECT BY s.object_id = PRIOR referenced_object_id
GROUP BY LEVEL, object_id) tree,
user_objects u
WHERE tree.object_id = u.object_id
ORDER BY lvl

When prompted, enter the OBJECT_NAME of the object whose dependencies you want to identify.

The OBJECT_NAME can be a PACKAGE, PACKAGE BODY, or PROCEDURE. The OBJECT_NAME is the name of the object at the root of the tree.

SQL script to lists all the profile settings (all levels)

SELECT pot.user_profile_option_name "Profile"
, DECODE( a.profile_option_value
, '1', '1 (may be "Yes")'
, '2', '2 (may be "No")'
, a.profile_option_value) "Value"
, DECODE( a.level_id
, 10001, 'Site'
, 10002, 'Appl'
, 10003, 'Resp'
, 10004, 'User'
, '????') "Levl"
, DECODE( a.level_id
, 10002, e.application_name
, 10003, c.responsibility_name
, 10004, d.user_name
, '-') "Location"
FROM applsys.fnd_application_tl e
, applsys.fnd_user d , applsys.fnd_responsibility_tl c
, applsys.fnd_profile_option_values a , applsys.fnd_profile_options b
, applsys.fnd_profile_options_tl pot
WHERE UPPER( pot.user_profile_option_name) LIKE UPPER( '%&&v_profile%')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id = a.application_id (+)
AND b.profile_option_id = a.profile_option_id (+)
AND a.level_value = c.responsibility_id (+)
AND a.level_value = d.user_id (+) AND a.level_value = e.application_id
(+)
AND( UPPER( e.application_name) LIKE UPPER( '%&&v_username%')
OR UPPER( c.responsibility_name) LIKE UPPER( '%&&v_username%')
OR UPPER( d.user_name) LIKE UPPER( '%&&v_username%'))
ORDER BY "Profile", "Levl", "Location", "Value"

<<<Previous