Procedure

get tables and their number of rows in Oracle Database Schema

Simplest way is to use the data dictionaries provided by Oracle. And you can depend on the results if the schema is up to date with the statistics. Use the following query to get the table list and their number of rows (records). SELECT table_name, num_rows FROM user_tables; OR SELECT table_name, num_rows FROM all_tables WHERE …

get tables and their number of rows in Oracle Database Schema Read More »

Procedure to display the departments, which are having total salary greater than?

Procedure without Parameters: CREATE OR REPLACE PROCEDURE dept_sal_pISCURSOR C1 ISselect d.department_name,e.tot_sal from(SELECT department_id,sum(salary) tot_salFROM employeesGROUP BY department_idHAVING SUM(salary) > 50000) e,departments dwhere e.department_id=d.department_id;v_id departments.department_name%TYPE;v_sal employees.salary%type;BEGINOPEN C1;DBMS_OUTPUT.PUT_LINE(‘Departments having salary greater than 50K are :’);LOOPFETCH C1 INTO v_id,v_sal;exit when C1%notfound;DBMS_OUTPUT.PUT_LINE(v_id||’ ‘||v_sal);END LOOP;if c1%rowcount=0 then DBMS_OUTPUT.PUT_LINE(‘No Departments found’);end if;CLOSE C1;END dept_sal_p; Procedure with Parameter as total salary limit: …

Procedure to display the departments, which are having total salary greater than? Read More »

Scroll to Top