SCHEMAS procedure and functions

set lines 400;
col host for a20;
col owner for a20;
col instance for a20;
col grantee for a20;
col grantor for a20;
col table_name for a40;
select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, object_type, count(*), to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM') "sysdate" from dba_objects where owner=upper('&owner') group by object_type,owner;

select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, count(*) schema_obj_count from dba_objects where owner=upper('&owner') group by owner'

select * from dba_tab_privs where grantee=upper('grantee') and table_name in ('DBMS_LOCK','DBMS_JOB');

-- List all tables in all schemas
SELECT OWNER
	,table_name
FROM dba_tables;

-- List all views in all schemas
SELECT OWNER
	,view_name
FROM dba_views;

-- List all indexes in all schemas
SELECT OWNER
	,index_name
	,table_name
FROM dba_indexes;

-- List all columns of a specific table in all schemas
SELECT OWNER
	,table_name
	,column_name
	,data_type
FROM dba_tab_columns
WHERE table_name = 'your_table_name';

-- List all procedures in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PROCEDURE';

-- List all triggers in all schemas
SELECT OWNER
	,trigger_name
	,table_name
FROM dba_triggers;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all synonyms in all schemas
SELECT OWNER
	,synonym_name
	,table_name
FROM dba_synonyms;

-- List all constraints in all schemas
SELECT OWNER
	,constraint_name
	,constraint_type
	,table_name
FROM dba_constraints;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all materialized views in all schemas
SELECT OWNER
	,mview_name
FROM dba_mviews;

-- List all materialized view logs in all schemas
SELECT OWNER
	,log_table
	,master
FROM dba_mview_logs;

-- List all user-defined types (UDTs) in all schemas
SELECT OWNER
	,type_name
FROM dba_types;

-- List all packages in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PACKAGE';

-- List all package procedures and functions in all schemas
SELECT OWNER
	,object_name
	,PROCEDURE_NAME
	,function_name
FROM dba_procedures
WHERE object_type = 'PACKAGE';

-- List all indexes on a specific table in all schemas
SELECT table_owner
	,index_name
	,column_name
FROM dba_ind_columns
WHERE table_name = 'your_table_name';

-- List all database links in all schemas
SELECT OWNER
	,db_link
FROM dba_db_links;

-----------------------------------------------------

Creating simple procedure:-

-- Create a simple procedure that prints a message
CREATE OR REPLACE PROCEDURE print_message
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, this is a simple procedure!');
END;
/

output:-

-- Execute the procedure

BEGIN

  print_message;

END;

/

--------------------------------------------------------------------------

Creating simple functions:-

-- Create a simple function that adds two numbers

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)

RETURN NUMBER

IS

  result NUMBER;

BEGIN

  result := a + b;

  RETURN result;

END;

/

output:-

-- Call the function

DECLARE

  sum_result NUMBER;

BEGIN

  sum_result := add_numbers(5, 7);

  DBMS_OUTPUT.PUT_LINE('Sum: ' || sum_result);

END;

/