There is nothing about the database itself that “makes” it an OLAP, or a DW, or an OLTP.That is all a matter of how it is used. Kind of like trying to tell if the Honda Accord parked in my driveway is a family sedan or a pizza delivery vehicle. In fact very few databases …
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. …
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), …
We will get this when we are trying to update a row in a table, for which there will be two or more corresponding records. Table 1: tc11 tcl2 t1 a t2 b t3 c t4 d Table 2: tc21 tc22 b1 a b2 b b3 c b4 d Table 3: tc31 tc32 t1 b1 …
ORA-30926: unable to get a stable set of rows in the source tables Read More »
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 »
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 »
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;
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 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 »
