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 »
In Big/complex applications, it is essential to call Sub-programs (Procedures/Functions) from other sub-programs. In that case it will be difficult to trace out Errors/Exceptions from where it is raised i.e. which sub-program caused the exception. Normally we can use SQLCODE and SQLERRM to get the error/exception, but these may not work in the above case. …
We may get this error, when we try to assign a value to a variable which is bigger than the maximum size of the variable.Find the following code for example: DECLARE v_data VARCHAR2(1); BEGIN v_data := ‘YES’; END; / Error starting at line : 1 in command –DECLAREv_data varchar2(1);BEGINv_data := ‘YES’;END;Error …
Compile the following Function in Oracle Database by copying it and Pressing F5 or F9 create or replace function test_num (v_card varchar2) return varchar2 is v_num number; begin select v_card into v_num from dual; return 1; exception when others then return v_card; –DBMS_OUTPUT.PUT_LINE(v_card); –DBMS_OUTPUT.PUT_LINE(SQLCODE); –DBMS_OUTPUT.PUT_LINE(SQLERRM); end test_num; Now fire the query as follows to find …
Finding characters in a string (VARCHAR-VARCHAR2) column Read More »
CREATE OR REPLACE FUNCTION rev_str_f(str VARCHAR2) RETURN VARCHAR2ISv_str VARCHAR2(10); –you can specify your own sizev_str_rev VARCHAR2(10); –specify same size as v_strBEGINv_str := ‘&string’;DBMS_OUTPUT.PUT_LINE(‘Given String: ‘||v_str);FOR i IN 1..LENGTH(v_str) LOOPv_str_rev := v_str_rev||SUBSTR(v_str,-i,1);DBMS_OUTPUT.PUT_LINE(v_str_rev);END LOOP;DBMS_OUTPUT.PUT_LINE(‘The reverse String for given ‘||v_str||’ is: ‘||v_str_rev);RETURN v_str_rev;END rev_str_f;
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 »
