Extract characters between specific strings in Oracle

To Extract specific characters from text we usecombination of  SUBSTR or INST functions .







SUBSTR Function :



It cuts a part of the Character from a a position we  define.







For example :



SELECT   SUBSTR ('HeIsGood', 3) FROM DUAL



The result is :



IsGood







INSTR Function :



It calculates the position of specific text we define inside Character.



For example :



SELECT   INSTR ('cocacola', 'co', 2) FROM DUAL



The result is :



5





We can use combination of INSTR and SUBSTR to extract characters between specific text as following:





SELECT SUBSTR('1/9/1438', 1 ,INSTR('1/9/1438', '/', 1, 1)-1) "DAY", SUBSTR('1/9/1438', INSTR('1/9/1438','/', 1, 1)+1,



INSTR('1/9/1438','/',1,2)-INSTR('1/9/1438','/',1,1)-1) "MONTH", SUBSTR('1/9/1438', INSTR('1/9/1438','/', 1, 2)+1) "YEAR"



FROM dual;



The result is :

1  9  1438

Thanks to PSOUG.org

Router Unable to load some websites and download apps from Google Play

After buying new ZAIN router (Saudi mobile company)  , I found that some sites does not open like portal.elm.sa/



So I found trouble was belongs to Router Firewall .



Disabling Firewall fixes it.

ORA-02298 Parent Keys Not Found

After you export DB then import it you may get this error:

That means a value found in child table but not found in parent table.



You can solve it by the following script to get the values not found :



select <COLUMN_NAME> from <CHILD_TABLE> a where not exists (select <COLUMN_NAME> from



<PARENT_TABLE> where primary_key = a.<KEY_VALUE>);





To looks like :

Then you will get different values and insert them into parent table  or delete them from child table.

Thanks to Atlassian Documentation

Fixing Internet Explorer Crash on Launching Oracle Forms

here is simple video shows how to fix  Internet Explorer crash when running Forms.

replace jvm.dll file in the following path :

C:\Program Files (x86)\Oracle\JInitiator 1.3.1.22\bin\hotspot

you can download jvm file from here.

Using Aliases in same query

Alias is very helpful to shorten long query in SQL.



To use Alias in the same query you can use the following method:







SELECT  A,B,C*50 from (select A,B,(A+B) as C from table).



note that you can choose  any column from inner-query to display it in outer-query.



but you cannot display a column in outer-query which is not exist in inner-query.

You can try this example using SCOTT schema:



select  SAL,COMM,COMM_RATE *100 from (

select SAL,COMM,(COMM/SAL) as COMM_RATE from emp)

Remove the 'Do you want to save changes?' Messages in Forms

here is it ...

1. Modify the CLEAR_ALL_MASTER_DETAILS procedure body. Change the existing CLEAR_BLOCK(ASK_COMMIT) to CLEAR_BLOCK(DO_COMMIT).

2. Specifying NO_COMMIT or NO_VALIDATE with the CLEAR_BLOCK built-in causes

Oracle Forms to flush the current block without performing a commit or prompting the operator.

ex:CLEAR_BLOCK(NO_COMMIT,NO_VALIDATE);

Thanks to Dipankar...

Avoid FRM-40401 message

Use Standard.commit  instead of commit to prevent Oracle message :

Source :OraFAQ