VijayaTech Labs

Admin at VijayaTech Labs.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in PLSQL

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. …

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in PLSQL Read More »

Histograms in Oracle for Table Columns

Histogram:A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. Purpose of Histograms:By default the optimizer assumes a uniform distribution of rows across the distinct values in a column. For columns that contain data skew (a nonuniform distribution of data within the column), …

Histograms in Oracle for Table Columns Read More »

Finding characters in a string (VARCHAR-VARCHAR2) column

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 »

Toad and SQL Developer shortcuts for Database Developers

S.No. Shortcut Functionality Toad SQL Developer 1 Ctrl + Enter Ctrl + Enter Executing Query or selected script (multiple queries execution is not available in Toad) 2 F9 F9 Executing Query or selected script (multiple queries execution is not available in Toad) 3 F4 Shift + F4 Viewing Object properties i.e. Code of Functions/Packages/procedures and …

Toad and SQL Developer shortcuts for Database Developers Read More »

Function to display reverse string of the given string in PL/SQL

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;

Difference between REPLACE and TRANSLATE in SQL?

Replace will replace the character or string with the given character/string for every occurrence of the character/string.Translate will replace the character with another character given not by the string/multiple characters Case 1: SELECT REPLACE(‘Jack and Jue’,’J’,’Bl’) replace_str,TRANSLATE(‘Jack and Jue’,’J’,’Bl’) translate_strFROM DUAL; Case 2: SELECT REPLACE(‘Jack and Jue’,’J’,”) replace_str,TRANSLATE(‘Jack and Jue’,’J’,”) translate_strFROM DUAL;

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