How to search a particular query in oracle ?

How to search a particular query in oracle ?

select table_name from dict where table_name like '%LINK%';

select table_name from dict where table_name like '%DIRECTORY%';

select table_name from dict where table_name like '%AUDIT%';

PL/SQL (PROCEDURE,PACKAGE,TRIGGER & FUNCTION)

PL/SQL (PROCEDURE,PACKAGE,TRIGGER & FUNCTION)

To check all the procedures, packages, triggers and functions in the database.


SQL> select count(*),object_type from dba_objects where object_type in ('PROCEDURE','FUNCTION','PACKAGE','TRIGGER') group by object_type;

COUNT(*) OBJECT_TYPE
---------- -------------------
161 PROCEDURE
1320 PACKAGE
627 TRIGGER
305 FUNCTION

Procedure:

procedures are used to perform specific actions
pass values in and out by using an argument list
can be called from within other programs using the CALL command.

Example:

create or replace procedure test_procedure(dept number, name varchar2, loc varchar2)
as
begin
insert into scott.dept values(dept,name,loc);
end;
/

SQL> select * from scott.dept;

DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
50 FINANCE        INDIA

How to execute a procedure ?

exec test_procedure(50,'FINANCE','INDIA');

Query to see all procedures:

select object_name from dba_objects where object_type='PROCEDURE';


Packages:

packages are collections of functions and procedures
each package should consist of two objects:
*package specification
*package body

Built-in packages:

Oracle database comes with several built-in PL/SQL packages that provide:

-administration and maintain utilities
-extended functionality
-use to DESCRIBE command to view subprograms
-DBMS_STAT: gathering, viewing, and modifying optimizer statistics
-DBMS_OUTPUT:generating output from PL/SQL
-DBMS_SESSION:accessing the ALTER SESSION and SET ROLE statements
-DBMS_SHARED_POOL:managing the shared pool (for example flusing it)
-DBMS_UTILITY:getting time, CPU time and version information
-DBMS_SCHEDULER:scheduling functions and procedures that are callable from PL/SQL
-DBMS_REDEFINITION:redefining objects online
-UTL_FILE:reading and writing to operating system files from PL/SQL.

SQL> set serveroutput on --------To check the description or any error message
SQL> exec dbms_output.PUT_LINE('This is mqm message');
This is mqm message

How to execute to packages ?

SQL> set serveroutput on 
SQL> exec dbms_output.PUT_LINE('This is mqm message');

Query to see all packages?

select object_name from dba_objects where object_type='PACKAGES';


Triggers:

triggers are PL/SQL code objects that are stored in the database and that automatically run or "fire" when something happens. The oracle database allows many actions to serve as triggering events including an insert into a table, a user logging in to the database and someone trying to drop a table or change audit settings.


Function:

create or replace function compute_tax (salary number)
return number
as begin
if salary<5000 then
return salary*.15;
else
return salary*.33;
end if;
end;
/

How to execute a function ?

select sysdate from dual;
select compute_tax(4000) from dual;

Query to see to functions?

select object_name from dba_objects where object_type='FUNCTION';

Database triggers for different notifications

Database triggers for different notifications

--shutdown trigger
--startup trigger
--logon trigger
--logoff trigger
--server error trigger


Create a table to store the trigger information:
create table trigger_table (database_name varchar2(30), event_name varchar2(20), event_time date, triggered_by_user varchar2(30));

Set the date format a session level:
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'

--shutdown

create or replace trigger log_shutdown
before shutdown on database
begin
insert into trigger_table (database_name, event_name, event_time, triggered_by_user)
values ('QADER', 'SHUTDOWN INITIATED', sysdate, user);
commit;
end;
/

--startup

create or replace trigger log_startup
after startup on database
begin
insert into trigger_table (database_name, event_name, event_time, triggered_by_user)
values ('QADER','STARTUP INITIATED',sysdate,user);
commit;
end;
/

--logon

create table LOGON_table (login_date date, user_name varchar2(10), status varchar2(10));

create or replace trigger logon_trigger
after logon on database
begin
insert into logon_table
values
(SYSDATE, USER, 'logged_in');
commit;
end logon_trigger;
/

--logout

create or replace trigger log_out
before logoff on database
begin
insert into logon_table
values
(SYSDATE, USER, 'logged_out');
commit;
end logon_trigger;
/

--logon_failure

create or replace trigger logon_failures
after servererror on database
begin
if (IS_SERVERERROR(1017)) THEN
INSERT INTO logon_table
(login_date, user_name, status)
values
(sysdate, sys_context('USERENV','AUTHENTICATED_IDENTITY'),'ORA-01017');
END IF;
COMMIT;
END logon_failures;
/