Problem: Data got committed in another/same session, cannot update row. 

Env: Oracle Sql Developer

How to Solve:

Uncheck below option

Continue reading →

Importing into Oracle Database Table using Sqlplus command

 sqlplus sys/oracle as sysdba

run this command at sqlplus prompt->

SQL>@"/mnt/hgfs/temp/QUERY.sql"; 

and dont forget to commit it at the end.

Continue reading →

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 →

Setting listener.ora in Linux

To set up listener.ora file in linux follow these steps:

• change directory to oracle folder then network and admin folders consequtively: 
• cd /u01/oracle/product/11.2.0.4/db_1
• cd network
• cd admin
• run su command enter password (needed to edit listener.ora). open listener.ora to edit in your favorite editor. I used gedit:
• su
• gedit listener.ora

• configure listener.ora file (you need to set up HOST, PORT, ORACLE_HOME, SERVICE_NAME), here is a sample file configured for my needs:

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.201)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    

    )

  )

SID_LIST_LISTENER = 

(SID_LIST = 

 (SID_DESC = 

(ORACLE_HOME = /u01/oracle/product/11.2.0.4/db_1/)

(SID_NAME = ORACLEVM)

(SERVICE_NAME = ORACLEVM)

(GLOBAL_NAME = ORACLEVM)

 ) 

) 

ADR_BASE_LISTENER = /u01/oracle

• stop ans start listener by using these commands:
• lsnrctl stop
• lsnrctl start

Continue reading →

Accessing a VMware Player (Workstation 12) machine from network

To be able to access a VMware Workstation 12 Player machine (to receive a ping request at least) follow these steps:

• Open virtual machine settings from player -> manage -> virtual machine settings and click network adapter tab

• Select Bridged mode.
• Make sure that you setup DHCP or a static ip for this virtual machine. To setup a static ip for a linux server follow this link: http://www.ozanmut.com/2017/03/setting-static-ip-for-red-hat.html
• Now you should be able to access virtual machine from network!

Continue reading →

Setting a Static IP for Red Hat Enterprise Linux Server

  To set a static Ip in Red Hat Enterprise Linux Server follow the steps below:

• Open a terminal 
• run this command to change directory to list network interfaces
• cd /etc/sysconfig/network-scripts/
• ls

• We need to edit the network interface file starting with ifcfg-enolxxxx. To be able to edit the file, first we need to run su. Run these commands:
• su
• gedit ifcfg-eno16777736

• Change these parts accordingly and save it:

        IPADDR=192.168.1.201

        GATEWAY=192.168.1.1

        DNS1=192.168.1.227

Continue reading →

Revert strip in Hg Mercurial

So you are using Mercurial version control system and Tortoise Hg as its windows GUI tool. And after you made changes you accidentally clicked "Modify History -> Strip",

So strip command  removed all your changes! Don't worry, there is a way to recover from this action and bring those changes back. Follow these steps:

1. For every strip action, the changes are backed up automatically to the "REPO/.hg/strip-backup". Open the backup folder and copy the latest modified file name.

2. Open Hg terminal by clicking Repository->Terminal.

3. Run this command: hg pull $(hg root)/.hg/strip-backup/$(backup file name)

    Voila! Here comes the changes back:

Continue reading →

How To Clear Wifi Password

If you have trouble with changing wifi password and cannot find and option to forget or clear an old wifi password on windows 8, here is a simple solution:

1. Open command prompt screen by pressing windows + r and type "cmd" enter.
2. run this command (profilename is the name of the wifi):
    netsh wlan delete profile name='profilename'

Thats all! Now when you try to connect it will ask your key again.

Continue reading →

How To Host Your Domain in Azure VPS

Let's say we have a domain name www.damaturk.com and we want to host it in Azure VPS (Virtual Private Server). The steps are as follows:

1. Remote desktop to the Azure VPS.
Navigate to https://manage.windowsazure.com click Virtual Machines, click connect.

2. Open IIS Manager. Assuming that your web site related files are copied under C:\inetpub\wwwroot, add your web site under sites.

3. Right click on your site and click edit bindings.
Click Add, enter your domain name and choose the internal ip from the combo box, at the end you should have two rows one with www prefix and one without.

4. Open Azure management portal again, click your virtual machine and click Endpoints. Add a new end point with port 80.

5. Go to your domain registrar site, open Manage DNS or something similar to add or change A record.

Enter (A) records as seen below:

Continue reading →

T-SQL Reading Image Type into Varchar From Database

declare @hexbin varbinary(max);
declare @vbin varbinary(max);
declare @hexstring1 varchar(8000)


-- convert varbinary to hexstring
select @hexbin = Data1 from Table1;
select @hexstring1 = '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)');

-- replace '000' to '0' three '0's mean enter character
select @hexstring1 = replace(@hexstring1, '000', '0')

-- convert hexstring to varbinary
select @vbin = cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring1"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@hexstring1, 1, 2) when '0x' then 3 else 0 end) as t(pos)

-- cast varbinary as varchar
print cast(@vbin as varchar)

Continue reading →

Useful T-SQL (MSSQL 2000, SQLEXPRESS, MSDE) Queries

Most of database jobs in the MSSQL2000 or MSSQL2005 world are done with Enterprise Manager or SQL Server Management Studio Express respectively. In some occasions where those tools are corrupted or even doesn't exist (especially if you are using MSDE) you have to use T-SQL queries.

Most useful T-SQL queries;

• How to CREATE a new database;

CREATE DATABASE DB1
ON
( NAME = DB1_dat,
FILENAME = 'C:\DB1_dat.MDF' )
LOG ON
( NAME = DB1_log,
FILENAME = 'C:\DB1_log.LDF' )
COLLATE SQL_Latin1_General_CP1254_CI_AS

• How to BACKUP a database;

BACKUP DATABASE DB1
TO DISK = 'C:\DB1.Bak'

• How to RESTORE a database from a backup file;

RESTORE DATABASE DB1
FROM DISK = 'c:\DB1.bak'

• How to DETACH a database

sp_detach_db DB1

• How to ATTACH a database

EXEC sp_attach_db @dbname = N'DB1',
@filename1 = N'c:\DB1_dat.mdf',
@filename2 = N'c:\DB1_log.ldf'

• "is dependent on column 'Description'." error:

ALTER TABLE Table1
DROP COLUMN Depth

Note: if you get such errors like;
-The object 'DF__Table1__Descript__5C37ACAD' is dependent on column 'Description'.
-The object 'CK__Table1__Height__5D2BD0E6' is dependent on column 'Height'.
-The object 'PK__Table1__5B438874' is dependent on column 'RowId'.

Let's explain the errors. The first error is raised while trying to drop column name "Description" from the table. The error states that there is an object dependent to this column. Note that the name of the object starts with "DF_" which tells that this is a "Default Constraint" defined for the column. So we have to drop this constraint object first, to avoid the error. We can do that with the following query;

ALTER TABLE Table1
DROP CONSTRAINT DF__Table1__Descript__5C37ACAD

The rest of the errors are respectively for the objects Check Constraint for the column "Height" and a Primary Key Constraint for the column "RowId". We can handle the errors running the DROP CONSTRAINT commands for each constraint.

• How to CREATE INDEX for a table

nonclustered index
CREATE NONCLUSTERED INDEX Idx_Depth
ON Table1 (Depth)

clustered indexCREATE CLUSTERED INDEX Idx_Depth2
ON Table1 (Depth)

• How to make a Case Sensitive Search;

If a database is created with default collation and the default database collation is case insensitive then the search for varchar columns will be case insensitive. To get the result we want, we can force the query to use another collation which is case sensitive.
SELECT * FROM Table1 WHERE description COLLATE Latin1_General_CS_AS = 'avalue'

• How To Shrink Database Log File

We first find the logical names;
Select name,* from sysfiles

name field gives us the logical name information. Using the logical names we run the sql below;

DBCC SHRINKFILE(Northwind_log, 1)
BACKUP LOG Northwind WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Northwind_log, 1)

Continue reading →