Blog - Our findings and learning

You no need to try to learn something. With the experience of trying, we learn what works and how it works. We share the same with you to learn from our experience.

ORA-06502: PL/SQL: numeric or value error

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

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

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

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;

Read More »

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;

Read More »