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…
Category: Oracle
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…
Oracle 11g Oracle Checkpoint Not Complete, Cannot Allocate New Log Warning
Error: Checkpoint not complete, Cannot Allocate New Log Warning Thread 1 cannot allocate new log, sequence 125487 Checkpoint not complete Solution: Checkpoint not complete messages are generated due to the logs are switching so fast that the checkpoint associated with the log switch isn’t complete. You should increase redo log file size and amount to…
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…
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19700 to ORA-20000
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19700 to ORA-20000 ORA-19700: device type exceeds maximum length of string Cause: The device type indicated is invalid. Action: Supply a correct device type and retry the allocation. ORA-19701: device name exceeds maximum length of string Cause: The device name indicated is invalid. Action: Supply a correct device name and retry the…
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19500 to ORA-19700
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19500 to ORA-19700 ORA-19500: device block size string is invalid Cause: the device block size returned by sequential I/O OSD is invalid Action: If the block size was set by using the PARMS option of the Recovery Manager ALLOCATE CHANNEL command, then the specified block size must be changed. If…
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19200 to ORA-19400
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19200 to ORA-19400 ORA-19200: Invalid column specification Cause: All input arguments must be valid columns Action: Specify a valid list of columns ORA-19201: Datatype not supported Cause: The particular datatype is not supported in the XMLGEN operator Action: Specify only supported datatypes as arguments to the XMLGEN operator ORA-19202: Error…
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19000 to ORA-19200
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-19000 to ORA-19200 ORA-19000: missing RELATIONAL keyword Cause: The keyword RELATIONAL in the work OBJECT RELATIONAL is missing in the XMLTYPE storage clause specification. Action: Supply the RELATIONAL keyword in the storage clause ORA-19001: Invalid storage option specified Cause: An invalid storage option was specified for the XMLType. Action: Supply…
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18200 to ORA-19000
Oracle 12c R2 Error Codes and Solution Suggestions from ORA-18200 to ORA-19000 ORA-18201: The input MDX string is empty. Cause: The input MDX (MultiDimensional eXpression) string was empty. Action: Correct the input MDX (MultiDimensional eXpression) string. ORA-18202: MDX parser error at line string, column string: string. Cause: The input MDX (MultiDimensional eXpression) query had invalid syntax. Action: Correct the syntax error for…