This will truncates or chops off digits of precision from a number.
Syntax: trunc (value, precision)
Ex:
SQL> select trunc(123.2345), trunc(123.2345,2), trunc(123.2354,2) from dual;
TRUNC(123.2345) TRUNC(123.2345,2) TRUNC(123.2354,2)
--------------------- ----------------------- -----------------------
123 123.23 123.23
SQL> select trunc(123.2345,-1), trunc(123.2345,-2), trunc(123.2345,-3),trunc(123.2345,-4) from dual;
TRUNC(123.2345,-1) TRUNC(123.2345,-2) TRUNC(123.2345,-3) TRUNC(123.2345,-4)
------------------------ ------------------------ ----------------------- ------------------------
120 100 0 0
SQL> select trunc(123,0), trunc(123,1), trunc(123,2) from dual;
TRUNC(123,0) TRUNC(123,1) TRUNC(123,2)
---------------- ---------------- -----------------
123 123 123
SQL> select trunc(-123,0), trunc(-123,1), trunc(-123,2) from dual;
TRUNC(-123,0) TRUNC(-123,1) TRUNC(-123,2)
----------------- ----------------- -----------------
-123 -123 -123
SQL> select trunc(123,-1), trunc(123,-2), trunc(123,-3), trunc(-123,-1), trunc(-123,2), trunc(-123,-3) from dual;
TRUNC(123,-1) TRUNC(123,-2) TRUNC(123,-3) TRUNC(-123,-1) TRUNC(-123,2) TRUNC(-
123,-3)
------------- ------------- ------------- -------------- ------------- --------------
120 100 0 -120 -123 0
SQL> select trunc(null,null), trunc(0,0), trunc(1,1), trunc(-1,-1), trunc(-2,-2) from dual;
TRUNC(NULL,NULL) TRUNC(0,0) TRUNC(1,1) TRUNC(-1,-1) TRUNC(-2,-2)
----------------------- ------------- ------------- --------------- ----------------
Hello There,
ReplyDeleteBest thing I have read in a while on this
Oracle Applications. There should be a standing ovation button. This is a great piece.
I am new to Oracle, I'm not a DBA and I ve tring to figure out this error in a log:
ERROR in QUETOL.tol_lc_movimientos_pg.movimientos SQLCODE[=ORA-20109:
ORA-20109: ORA-01422: exact fetch returns more than request]
The problem arises I don't even know exactly this error which object references to: ie package, procedure,table,etc
no rows selected
I thought it was a Package so I could see its DML code.
I appreciate your help in advanced to identify which Object this log error point to point.
Anyways great write up, your efforts are much appreciated.
Obrigado,
Nani
Hi There,
ReplyDeleteThanks for highlighting this and indicating about SQL-Trunc where more study and thought is necessary.
I have some statements that I need to schedule in a procedure.
Drop Table u_system.emp_load;
CREATE TABLE u_system.emp_load
(employee_number CHAR(5),
employee_atten_time TIMESTAMP,
machine_number CHAR(3),
in_out CHAR(2)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY user_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (employee_number CHAR(4),
employee_atten_time CHAR(20) date_format TIMESTAMP mask "dd/mm/yyyy hh24:mi:ss",
machine_number CHAR(4),
in_out CHAR(1)
)
)
LOCATION ('emp2.dat')
);
insert into u_system.emp_attend(employee_number,employee_atten_time,machine_number,in_out)
select * from u_system.emp_load;
Follow my new blog if you interested in just tag along me in any social media platforms!
Thanks,
Preethi
Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete