I will show how to round up a number by protecting 2 decimal. When I use round or ceil function, I can not have correct result. I want to round a number upwards with calculate 2 decimal. I want result like below examples. 1.2445 > 1.25 1.21 > 1.21 1.521 > 1.53 1.1 > 1.1…
Tag: Oracle Administration
Update Another Column On The Same Table With Trigger While Updating Table in Oracle
If you want to do update another column when execute an update on table, you can use before update trigger. If you use after update trigger, you can get some errors. if you use pragma autonomous_transaction parameter as declare in trigger you can get errors like that are “ORA-00060: deadlock detected while waiting for resource”, “ORA-06512:…
Obtain Surname, Last Name and Mid Name From Full Name in Oracle
If you have a column with full name data and you want to split this data, you can use below function to split operation. Split Name ve Surname Table : test Data1 : Emin Fatih Acar Data2 : Fatih Acar SQL> select fullname, substr(fullname,0,instr(fullname,’ ‘,Instr(fullname,’ ‘,-1))-1) name, substr(fullname,Instr(fullname,’ ‘,-1)+1) surname from test; Result: Data1 name…
Keep Table in Buffer Pool Oracle 11g
Oracle provides a feature to keep objects in the Buffer pool. This feature allows you to keep the frequently accessed table in memory itself which will provide the faster access of the table. The object will be kept under the KEEP pool and the purpose of the keep pool is to accommodate / cache the…
Create Dblink From Oracle to PostgreSQL Database
Since fetching data from PostgreSQL database to Oracle database, you can create database link between two database systems. For that you can apply below operations. Test Systems PostgreSQL DB IP : 192.168.10.10 OS : Centos 7 x64 Service : postgresql.service Oracle DB IP : 192.168.10.20 OS : OEL 6 x64 PostgreSQL Side psql# create user…
Oracle 12c : RMAN New Features and Enhancements
1. Fine Grained Recovery With Oracle Database 12c, you can use a simple RECOVER TABLE command to perform a point-in-time recovery of a table/partition without having to go through a manual point-in-time recovery process. This command automatically performs the following steps: creation of the auxiliary instance, table recovery, exporting of the object, and importing it…
Oracle 11g Error Codes and Solution Suggestions from ORA-22800 to ORA-23000
Oracle 11g Error Codes and Solution Suggestions from ORA-22800 to ORA-23000 ORA-22800: invalid user-defined type Cause: An attempt was made to use an incomplete type as a constructor. Action: Complete the type definition before using it in a query. ORA-22801: invalid object row variable Cause: The specified object row variable is not available in the scope of name…
Oracle 11g Error Codes and Solution Suggestions from ORA-22300 to ORA-22700
Oracle 11g Error Codes and Solution Suggestions from ORA-22300 to ORA-22700 ORA-22303: type “string”.”string” not found Cause: The user is trying to obtain information for a type that cannot be found. Action: Check that the schema is correct and that the type has been created correctly. ORA-22304: input type is not an object type Cause: The user is…
Oracle 11g Error Codes and Solution Suggestions from ORA-22000 to ORA-22300
Oracle 11g Error Codes and Solution Suggestions from ORA-22000 to ORA-22300 ORA-22053: overflow error Cause: This operation’s result is above the range of Oracle number. Action: Decrease the input value(s) so that the result is in the range of Oracle number. ORA-22054: underflow error Cause: This operation’s result is below the range of Oracle number. Action: Increase the input…
Oracle 11g Error Codes and Solution Suggestions from ORA-20000 to ORA-22000
Oracle 11g Error Codes and Solution Suggestions from ORA-20000 to ORA-22000 ORA-20000: string Cause: The stored procedure ‘raise_application_error’ was called which causes this error to be generated. Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information. ORA-21300: objects option not installed Cause: The objects option is not installed…