Add quotation at the start and end of each line in Notepad++

Suppose we have column names

We want to prepare them to use in  a query and need to put double qutoes around them then put comma and combine all into one line.

press Ctrl + H and check Regular expression radio button.

 Find What: (.+) 

 Replace with: "\1"

it becomes

to put , (comma) at the ends of each line

 Find What: (.+) 

 Replace with: \1,

to combine them into one line

 Find What: \r\n 

 Replace with: 

Continue reading →

Search for a particular string in Oracle clob column

select *  
from TABLE_NAME
where dbms_lob.instr(COLUMNNAME,'searchtext') > 0;

Continue reading →

Fix ORA-28001 the password has expired

sqlplus sys as sysdba

alter user [username] identified by [password] account unlock;

Continue reading →

Shortcut solutions to Repetitive Tasks

How to add a character or some string to end of each line in a list of string?

Solution: Suppose that we want to add , (comma character) at the end of each line, using Notepad++

Replace

Find What: ^(.*)$
Replace: $0,

Continue reading →

ORA-28002: the password will expire within 7 days

If you get this error for your user and want to get rid of it, first get the profile of user with this query:

SELECT PROFILE
FROM dba_users
WHERE username = 'YOUR_USER';


You can check profile parameters:
 SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

Notice that PASSWORD_LIFE_TIME has a value. We can change it to UNLIMITED with this pl/sql query:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Now checking by running previous query we see that its done successfully.
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

Continue reading →

C# Decimal Vs Float/Double

Facts

Float Type: 32 bit floating point number in the range from approximately 

1.5 × 10⁻⁴⁵ to 3.4 × 10³⁸ with a precision of 7 digits.

Double Type: 64 bit floating point number in the range from approximately 

5.0 × 10⁻³²⁴ to 1.7 × 10³⁰⁸ with a precision of 15-16 digits.

Decimal Type: 128 bit floating point number in the range from approximately 

1.0 × 10⁻²⁸ to 7.9 × 10²⁸ with 28-29 significant digits.

When to use decimal, when to use float/double?

• Use decimal for counted values (like money, scores)
• Use float/double for measured values (like distance)

ref: difference-between-decimal-float-and-double-in-net

Microsoft CSharp Language Specification of decimal type

The decimal type is a 128-bit data type suitable for financial and monetary calculations. The decimal type can represent values ranging from 1.0 × 10⁻²⁸ to approximately 7.9 × 10²⁸ with 28-29 significant digits.

The finite set of values of type decimal are of the form (–1)^s × c × 10^-e, where the sign s is 0 or 1, the coefficient c is given by 0 ≤ c < 2⁹⁶, and the scale e is such that 0 ≤ e ≤ 28.The decimal type does not support signed zeros, infinities, or NaN's. A decimal is represented as a 96-bit integer scaled by a power of ten. For decimals with an absolute value less than 1.0m, the value is exact to the 28^th decimal place, but no further. For decimals with an absolute value greater than or equal to 1.0m, the value is exact to 28 or 29 digits. Contrary to the float and double data types, decimal fractional numbers such as 0.1 can be represented exactly in the decimal representation. In the float and double representations, such numbers are often infinite fractions, making those representations more prone to round-off errors.

If one of the operands of a binary operator is of type decimal, then the other operand must be of an integral type or of type decimal. If an integral type operand is present, it is converted to decimal before the operation is performed.

The result of an operation on values of type decimal is that which would result from calculating an exact result (preserving scale, as defined for each operator) and then rounding to fit the representation. Results are rounded to the nearest representable value, and, when a result is equally close to two representable values, to the value that has an even number in the least significant digit position (this is known as “banker’s rounding”). A zero result always has a sign of 0 and a scale of 0.

If a decimal arithmetic operation produces a value less than or equal to 5 × 10^-29 in absolute value, the result of the operation becomes zero. If a decimal arithmetic operation produces a result that is too large for the decimal format, a System.OverflowException is thrown.

The decimal type has greater precision but smaller range than the floating-point types. Thus, conversions from the floating-point types to decimal might produce overflow exceptions, and conversions from decimal to the floating-point types might cause loss of precision. For these reasons, no implicit conversions exist between the floating-point types and decimal, and without explicit casts, it is not possible to mix floating-point and decimal operands in the same expression.

ref: C# Language Specification 5.0

Continue reading →

PL/SQL Giving sys.DBMS_LOCK Execution Permission To a User

To be able to use sys.dbms_lock a user should have been granted execution.

begin
 DBMS_LOCK.Sleep( 60 );
end;
PLS-00201: identifier 'DBMS_LOCK' must be declared

To grant execution to user:

grant execute on <object> to <user>;

sqlplus / as sysdba

grant all on sys.dbms_lock to user;

Continue reading →