SQL developer tips

	SET sqlformat csv
	SELECT /*json*/ * FROM Client;
	{"items":[
	{"clientid":1,"ssn":"2006766317","currency":"RUR","clienttype":"HNWI","riskclassid":1,"advisorid":"00F3","load_date":"29.04.2016 13:19:04","src_id":1,"id":1},{"clientid":2,"ssn":"4603251821","currency":"RUR","clienttype":"HNWI","riskclassid":2,"advisorid":"0035","load_date":"29.04.2016 13:20:33","src_id":1,"id":2},{"clientid":3,"ssn":"4611445112","currency":"RUR","clienttype":"HNWI","riskclassid":4,"advisorid":"006D","load_date":"29.04.2016 13:21:57","src_id":1,"id":3}]}
	SELECT /*csv*/ * FROM Client;
	"CLIENTID","SSN","TITLE","FIRSTNAME","LASTNAME","BIRTHDATE","EMAIL","PHONENUMBER","MOBILENUMBER","STREETADDRESS","STREETADDRESS2","ZIPCODE","STATE","COUNTRY","CURRENCY","CLIENTTYPE","MIFIDRISKDATE","RISKCLASSID","ADVISORID","LOAD_DATE","SRC_ID","ID"
	1,"2006766317","","","","","","","","","","","","","RUR","HNWI","",1,"00F3",29.04.2016 13:19:04,1,1
	2,"4603251821","","","","","","","","","","","","","RUR","HNWI","",2,"0035",29.04.2016 13:20:33,1,2
	3,"4611445112","","","","","","","","","","","","","RUR","HNWI","",4,"006D",29.04.2016 13:21:57,1,3
	SELECT /*xml*/ * FROM Client;
	SELECT /*html*/ * FROM Client;
	SELECT /*delimited*/ * FROM Client;
	SELECT /*insert*/ * FROM Client;
	REM INSERTING into Client
	SET DEFINE OFF;
	Insert into "Client" (CLIENTID,SSN,TITLE,FIRSTNAME,LASTNAME,BIRTHDATE,EMAIL,PHONENUMBER,MOBILENUMBER,STREETADDRESS,STREETADDRESS2,ZIPCODE,STATE,COUNTRY,CURRENCY,CLIENTTYPE,MIFIDRISKDATE,RISKCLASSID,ADVISORID,LOAD_DATE,SRC_ID,ID) values ('1','2006766317',null,null,null,null,null,null,null,null,null,null,null,null,'RUR','HNWI',null,'1','00F3',to_date('29.04.2016 13:19:04','DD.MM.YYYY HH24:MI:SS'),'1','1');
	Insert into "Client" (CLIENTID,SSN,TITLE,FIRSTNAME,LASTNAME,BIRTHDATE,EMAIL,PHONENUMBER,MOBILENUMBER,STREETADDRESS,STREETADDRESS2,ZIPCODE,STATE,COUNTRY,CURRENCY,CLIENTTYPE,MIFIDRISKDATE,RISKCLASSID,ADVISORID,LOAD_DATE,SRC_ID,ID) values ('2','4603251821',null,null,null,null,null,null,null,null,null,null,null,null,'RUR','HNWI',null,'2','0035',to_date('29.04.2016 13:20:33','DD.MM.YYYY HH24:MI:SS'),'1','2');
	Insert into "Client" (CLIENTID,SSN,TITLE,FIRSTNAME,LASTNAME,BIRTHDATE,EMAIL,PHONENUMBER,MOBILENUMBER,STREETADDRESS,STREETADDRESS2,ZIPCODE,STATE,COUNTRY,CURRENCY,CLIENTTYPE,MIFIDRISKDATE,RISKCLASSID,ADVISORID,LOAD_DATE,SRC_ID,ID) values ('3','4611445112',null,null,null,null,null,null,null,null,null,null,null,null,'RUR','HNWI',null,'4','006D',to_date('29.04.2016 13:21:57','DD.MM.YYYY HH24:MI:SS'),'1','3');
	SELECT /*loader*/ * FROM Client;
	1|"2006766317"|||||||||||||"RUR"|"HNWI"||1|"00F3"|2016-04-29 13:19:04|1|1|
	2|"4603251821"|||||||||||||"RUR"|"HNWI"||2|"0035"|2016-04-29 13:20:33|1|2|
	3|"4611445112"|||||||||||||"RUR"|"HNWI"||4|"006D"|2016-04-29 13:21:57|1|3|
	SELECT /*fixed*/ * FROM Client;
	"CLIENTID" "SSN" "TITLE" "FIRSTNAME" "LASTNAME" "BIRTHDATE" "EMAIL" "PHONENUMBER" "MOBILENUMBER" "STREETADDRESS" "STREETADDRESS2" "ZIPCODE" "STATE" "COUNTRY" "CURRENCY" "CLIENTTYPE" "MIFIDRISKDATE" "RISKCLASSID" "ADVISORID" "LOAD_DATE" "SRC_ID" "ID"
	"1" "2006766317" "" "" "" "" "" "" "" "" "" "" "" "" "RUR" "HNWI" "" "1" "00F3" "29.04.2016 13:19:04" "1" "1"
	"2" "4603251821" "" "" "" "" "" "" "" "" "" "" "" "" "RUR" "HNWI" "" "2" "0035" "29.04.2016 13:20:33" "1" "2"
	"3" "4611445112" "" "" "" "" "" "" "" "" "" "" "" "" "RUR" "HNWI" "" "4" "006D" "29.04.2016 13:21:57" "1" "3"
	SELECT /*text*/ * FROM Client;
	cool feauter is
	ctrl+shift+' change up-case,l-case
	ctrl-/ comment
	ctrl-f7 format
	f10 explain plan
	f8 sql history
	f9 run statements
	f5 run script
	shift-f4 desc (very useful)
	http://www.thatjeffsmith.com/archive/2012/11/keyboard-shortcuts-in-oracle-sql-developer/

view raw SQL developer tips.sql hosted with ❤ by GitHub

	perl -pi.bak -e "BEGIN{undef $/;} s#^\s*(create table \w+)\s+\(#$1\(#msg;" %1
	perl -pi.bak -e "BEGIN{undef $/;} s# # #msg;" %1
	perl -pi.bak -e "BEGIN{undef $/;} s#^\s*\)\s*;$#\)\n\/#msg;" %1

view raw parse.bat hosted with ❤ by GitHub

	<?xml version = '1.0' encoding = 'UTF-8'?>
	<SQLprofiles class="oracle.dbtools.proformatter.treePreferences.style.profile.CodingStyleSQLProfiles" xmlns="http://xmlns.oracle.com/jdeveloper/110000/default-coding-sql-style-profiles">
	<profileMap class="java.util.HashMap">
	<Item>
	<Key>1:mishin_lower</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>true</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>1</bracketSpacing>
	<bracketSpacingAndOrWhen>false</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>false</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>false</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>false</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>false</breakJoin>
	<breakKeyword>false</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>false</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>1</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>999</lineNum>
	<lineWidth>130</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>mishin_lower</name>
	<numCommas>1</numCommas>
	<numSpaces>4</numSpaces>
	<operatorSpacing>1</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>80</smallSql>
	<sourceSql>1</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>3</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	<Item>
	<Key>1:Old Preferences</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>false</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>0</bracketSpacing>
	<bracketSpacingAndOrWhen>false</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>true</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>true</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>true</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>true</breakJoin>
	<breakKeyword>false</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>false</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>0</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>999</lineNum>
	<lineWidth>999</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>Old Preferences</name>
	<numCommas>1</numCommas>
	<numSpaces>2</numSpaces>
	<operatorSpacing>0</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>80</smallSql>
	<sourceSql>1</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>0</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	<Item>
	<Key>1:SQL</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>false</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>0</bracketSpacing>
	<bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>true</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>true</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>true</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>true</breakJoin>
	<breakKeyword>true</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>true</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>0</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>10</lineNum>
	<lineWidth>80</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>SQL</name>
	<numCommas>1</numCommas>
	<numSpaces>2</numSpaces>
	<operatorSpacing>0</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>20</smallSql>
	<sourceSql>1</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>0</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	<Item>
	<Key>2:SQL</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>false</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>0</bracketSpacing>
	<bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>true</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>true</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>true</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>true</breakJoin>
	<breakKeyword>true</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>true</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>0</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>10</lineNum>
	<lineWidth>80</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>SQL</name>
	<numCommas>1</numCommas>
	<numSpaces>2</numSpaces>
	<operatorSpacing>0</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>20</smallSql>
	<sourceSql>2</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>0</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	<Item>
	<Key>3:SQL</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>false</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>0</bracketSpacing>
	<bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>true</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>true</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>true</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>true</breakJoin>
	<breakKeyword>true</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>true</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>0</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>10</lineNum>
	<lineWidth>80</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>SQL</name>
	<numCommas>1</numCommas>
	<numSpaces>2</numSpaces>
	<operatorSpacing>0</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>20</smallSql>
	<sourceSql>3</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>0</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	<Item>
	<Key>4:SQL</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>false</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>0</bracketSpacing>
	<bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>true</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>true</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>true</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>true</breakJoin>
	<breakKeyword>true</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>true</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>0</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>10</lineNum>
	<lineWidth>80</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>SQL</name>
	<numCommas>1</numCommas>
	<numSpaces>2</numSpaces>
	<operatorSpacing>0</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>20</smallSql>
	<sourceSql>4</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>0</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	<Item>
	<Key>5:SQL</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>false</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>0</bracketSpacing>
	<bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>true</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>true</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>true</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>true</breakJoin>
	<breakKeyword>true</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>true</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>0</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>10</lineNum>
	<lineWidth>80</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>SQL</name>
	<numCommas>1</numCommas>
	<numSpaces>2</numSpaces>
	<operatorSpacing>0</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>20</smallSql>
	<sourceSql>5</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>0</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	<Item>
	<Key>6:SQL</Key>
	<Value class="oracle.dbtools.proformatter.treePreferences.CodingStyleSQLProfile">
	<alignAs>true</alignAs>
	<alignComma>false</alignComma>
	<alignComment>true</alignComment>
	<alignConcat>true</alignConcat>
	<alignDecl>true</alignDecl>
	<alignEqual>true</alignEqual>
	<alignKeyword>false</alignKeyword>
	<alignOperator>true</alignOperator>
	<bracketSpacing>0</bracketSpacing>
	<bracketSpacingAndOrWhen>true</bracketSpacingAndOrWhen>
	<breakAfterAnd>false</breakAfterAnd>
	<breakAfterComma>true</breakAfterComma>
	<breakAfterConcat>false</breakAfterConcat>
	<breakBeforeAnd>true</breakBeforeAnd>
	<breakBeforeComma>false</breakBeforeComma>
	<breakBeforeComment>false</breakBeforeComment>
	<breakBeforeConcat>true</breakBeforeConcat>
	<breakCase>true</breakCase>
	<breakCaseAndOr>true</breakCaseAndOr>
	<breakCaseElse>true</breakCaseElse>
	<breakCaseThen>true</breakCaseThen>
	<breakCaseWhen>true</breakCaseWhen>
	<breakJoin>true</breakJoin>
	<breakKeyword>true</breakKeyword>
	<breakSchema>0</breakSchema>
	<breakSelectBracket>true</breakSelectBracket>
	<colored>false</colored>
	<commaSpacing>0</commaSpacing>
	<dblIndent>false</dblIndent>
	<forceDifference>true</forceDifference>
	<indentAnd>false</indentAnd>
	<lineNum>10</lineNum>
	<lineWidth>80</lineWidth>
	<memberOrderHashStructure/>
	<moreNewlines>false</moreNewlines>
	<name>SQL</name>
	<numCommas>1</numCommas>
	<numSpaces>2</numSpaces>
	<operatorSpacing>0</operatorSpacing>
	<preserveNewlines>0</preserveNewlines>
	<quoteChar>0</quoteChar>
	<replaceComment>false</replaceComment>
	<smallSql>20</smallSql>
	<sourceSql>6</sourceSql>
	<sqlSourceEnclosed>0</sqlSourceEnclosed>
	<sqlSourceEscape>0</sqlSourceEscape>
	<sqlsourceCopied>false</sqlsourceCopied>
	<suppressComment>false</suppressComment>
	<targetSql>0</targetSql>
	<uppercase>0</uppercase>
	<useTab>false</useTab>
	</Value>
	</Item>
	</profileMap>
	</SQLprofiles>

view raw code_format.xml hosted with ❤ by GitHub

	perl -pi.bak -e "BEGIN{undef $/;} s#^\s*(create table \w+)\s+\(#$1\(#msg;" %1
	perl -pi.bak -e "BEGIN{undef $/;} s# # #msg;" %1
	perl -pi.bak -e "BEGIN{undef $/;} s#^\s*\)\s*;$#\)\n\/#msg;" %1

view raw parse.bat hosted with ❤ by GitHub

	<?xml version="1.0" encoding="UTF-8" ?>
	- <rows>
	- <row>
	<key>cf</key>
	<value>CREATE FUNCTION function_name [ (parameter) ] RETURN return_datatype IS [declaration_section] BEGIN ... END function_name;</value>
	</row>
	- <row>
	<key>cp</key>
	<value>CREATE PROCEDURE procedure_name [ (parameter]) ] IS [declaration_section] BEGIN ... END procedure_name;</value>
	</row>
	- <row>
	<key>ct</key>
	<value>CREATE TABLE table ()</value>
	</row>
	- <row>
	<key>df</key>
	<value>DELETE FROM table WHERE</value>
	</row>
	- <row>
	<key>gdwh</key>
	<value>DECLARE PROCEDURE GRANT_DML_ON_CURRENT_SCHEME_TO(PAR_TO_SCHEMA_NAME VARCHAR2) IS VAR_CHECK_SCHEMA_NAME VARCHAR2(30) := 'DWH2ASPLINK'; BEGIN IF VAR_CHECK_SCHEMA_NAME IS NOT NULL AND USER != VAR_CHECK_SCHEMA_NAME THEN RAISE_APPLICATION_ERROR(-20001, 'Current schema differ from "'|| VAR_CHECK_SCHEMA_NAME ||'"'); END IF; FOR REC IN (SELECT * FROM USER_TABLES ORDER BY TABLE_NAME) LOOP EXECUTE IMMEDIATE 'GRANT SELECT ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME; EXECUTE IMMEDIATE 'GRANT INSERT ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME; EXECUTE IMMEDIATE 'GRANT UPDATE ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME; EXECUTE IMMEDIATE 'GRANT DELETE ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME; END LOOP; FOR REC IN (SELECT * FROM USER_SEQUENCES ORDER BY SEQUENCE_NAME) LOOP EXECUTE IMMEDIATE 'GRANT SELECT ON "'|| REC.SEQUENCE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME; END LOOP; END; BEGIN GRANT_DML_ON_CURRENT_SCHEME_TO('ASPLINK'); GRANT_DML_ON_CURRENT_SCHEME_TO('SVALIEV_AL'); GRANT_DML_ON_CURRENT_SCHEME_TO('IMATUSHAK_AL'); GRANT_DML_ON_CURRENT_SCHEME_TO('NMISHIN_AL'); GRANT_DML_ON_CURRENT_SCHEME_TO('ACHALOV_AL'); GRANT_DML_ON_CURRENT_SCHEME_TO('IKAZANSKY_AL'); GRANT_DML_ON_CURRENT_SCHEME_TO('ASPLINK_TEST'); END; /</value>
	</row>
	- <row>
	<key>ii</key>
	<value>INSERT INTO table VALUES ()</value>
	</row>
	- <row>
	<key>slb</key>
	<value>(select * from databasechangelog where id like '%62%');</value>
	</row>
	- <row>
	<key>slbs</key>
	<value>select * from asplink.databasechangelog T ORDER BY T.DATEEXECUTED DESC;</value>
	</row>
	- <row>
	<key>ssf</key>
	<value>SELECT * FROM table</value>
	</row>
	- <row>
	<key>ulb</key>
	<value>update DATABASECHANGELOG set MD5SUM=null, ID='asplink-2.1-UCASPL-61-synonyms' where ID='asplink-2.0-UCASPL-61-synonyms';</value>
	</row>
	</rows>

view raw CodeTemplate.xml hosted with ❤ by GitHub

	<?xml version = '1.0' encoding = 'UTF-8'?>
	<snippets>
	<group category="Asplink Functions" language="PLSQL">
	<snippet name="grant dwh_asplink" description="Права на объекты схемы DWH2ASPLINK ">
	<code>
	<![CDATA[DECLARE
	PROCEDURE GRANT_DML_ON_CURRENT_SCHEME_TO(PAR_TO_SCHEMA_NAME VARCHAR2) IS
	VAR_CHECK_SCHEMA_NAME VARCHAR2(30) := 'DWH2ASPLINK';
	BEGIN
	IF VAR_CHECK_SCHEMA_NAME IS NOT NULL AND USER != VAR_CHECK_SCHEMA_NAME THEN
	RAISE_APPLICATION_ERROR(-20001, 'Current schema differ from "'|| VAR_CHECK_SCHEMA_NAME ||'"');
	END IF;
	FOR REC IN (SELECT *
	FROM USER_TABLES
	ORDER BY TABLE_NAME)
	LOOP
	EXECUTE IMMEDIATE 'GRANT SELECT ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME;
	EXECUTE IMMEDIATE 'GRANT INSERT ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME;
	EXECUTE IMMEDIATE 'GRANT UPDATE ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME;
	EXECUTE IMMEDIATE 'GRANT DELETE ON "'|| REC.TABLE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME;
	END LOOP;
	FOR REC IN (SELECT *
	FROM USER_SEQUENCES
	ORDER BY SEQUENCE_NAME)
	LOOP
	EXECUTE IMMEDIATE 'GRANT SELECT ON "'|| REC.SEQUENCE_NAME ||'" TO '|| PAR_TO_SCHEMA_NAME;
	END LOOP;
	END;
	BEGIN
	GRANT_DML_ON_CURRENT_SCHEME_TO('ASPLINK');
	GRANT_DML_ON_CURRENT_SCHEME_TO('SVALIEV_AL');
	GRANT_DML_ON_CURRENT_SCHEME_TO('IMATUSHAK_AL');
	GRANT_DML_ON_CURRENT_SCHEME_TO('NMISHIN_AL');
	GRANT_DML_ON_CURRENT_SCHEME_TO('ACHALOV_AL');
	GRANT_DML_ON_CURRENT_SCHEME_TO('IKAZANSKY_AL');
	GRANT_DML_ON_CURRENT_SCHEME_TO('ASPLINK_TEST');
	END;
	/]]>
	</code>
	</snippet>
	<snippet name="sql developer tips" description="hint">
	<code>
	<![CDATA[spool c:\Users\nmishin\Documents\masterdomino\work_line\18052016\exp.html
	SET sqlformat csv
	SELECT /*json*/ * FROM Client;
	SELECT /*csv*/ * FROM Client;
	SELECT /*xml*/ * FROM Client;
	SELECT /*html*/ * FROM Client;
	SELECT /*delimited*/ * FROM Client;
	SELECT /*insert*/ * FROM Client;
	SELECT /*loader*/ * FROM Client;
	SELECT /*fixed*/ * FROM Client;
	SELECT /*text*/ * FROM Client;
	ctrl-' change up-case,l-case
	ctrl-/ comment
	ctrl-f7 format
	f10 explain plan
	f8 sql history
	f9 run statements
	f5 run script ]]>
	</code>
	</snippet>
	<snippet name="Eclipse tips" description="shortcut">
	<code>
	<![CDATA[My Lovely Keyboard Shortcuts for Eclipse.
	Is there a key binding for Next Difference and Previous Difference in Eclipse Compare Editor? in Merge Tool
	default shortcuts for these: CTRL-. (next) and CTRL-, (previous)
	ALT+SHIFT+Q, C - show console!
	ALT+SHIFT+Q, U - show JUnit window
	F12 - activate editor!
	ALT+ SHIFT+ R - Refactor Actions, Rename Method
	ALT+Arrow Up/Down Move current line or selection up or down
	CTRL+ALT+Arrow Up Duplicate current line or selection up or down
	CTRL+# commit
	CTRL+SHIFT+5 - Pull
	CTRL+SHIFT+4 - Commit
	CTRL+SHIFT+6 - Merge
	CTRL+SHIFT+7 - Merge Tool
	CTRL+SHIFT+9 - Clean
	CTRL+SHIFT+= - Push to Upstream
	CTRL+SHIFT+C - Build Clean
	CTRL+SHIFT+F - Format Source
	ALT+SHIFT+A - Select Rectangle
	CTRL+ALT+W - Wrap Lines
	CTRL+D - delete Lines
	CTRL+ALT+J - Join Lines
	ALT+F+. - Open File
	CTRL+SHIFT+G - Find all references
	F5 - Refresh project explorer
	ALT+SHIFT+X, T - Run JUnit Test
	CTRL+SHIFT+Y - Changes the selection to lower case
	CTRL+SHIFT+X - Changes the selection to upper case
	CTRL+SHIFT+R - Open an editor on a particular resource, files
	CTRL+SHIFT+T - Open a type in a Java editor, Java classes, interfaces
	CTRL+M - Toggles maximize/restore state of active view or editor
	CTRL+SHIFT+L - Show the key assist dialog, keyboard shortcuts
	ALT+R+H - Run Maven Build or JUnit4 test]]>
	</code>
	</snippet>
	<snippet name="start weblogic" description="старт weblogic">
	<code>
	<![CDATA["c:\oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\bin\startWebLogic.cmd"]]>
	</code>
	</snippet>
	<snippet name="get drools" description="забрать друллзы">
	<code>
	<![CDATA[cd c:\oracle\myRepoDirectory
	git reset --hard
	git clean -df
	git pull
	]]>
	</code>
	</snippet>
	<snippet name="commit drools" description="коммит друллзов">
	<code>
	<![CDATA[cd c:\oracle\myRepoDirectory
	git add --all
	git commit -m "UCASPL-62 add rules for 3 dwh and 1 xls tables"
	git push]]>
	</code>
	</snippet>
	<snippet name="update DATABASECHANGELOG" description="">
	<code>
	<![CDATA[update databasechangelog set md5sum=null where id like '%62%';
	update DATABASECHANGELOG
	set MD5SUM=null,
	ID='asplink-2.1-UCASPL-61-synonyms'
	where ID='asplink-2.0-UCASPL-61-synonyms';
	]]>
	</code>
	</snippet>
	<snippet name="databasechangelog T ORDER BY T.DATEEXECUTED DESC" description="">
	<code>
	<![CDATA[select *
	from asplink.databasechangelog T ORDER BY T.DATEEXECUTED DESC;
	]]>
	</code>
	</snippet>
	<snippet name="databasechangelog" description="">
	<code>
	<![CDATA[(select *
	from databasechangelog
	where id like '%62%');
	]]>
	</code>
	</snippet>
	<snippet name="start_asp_project.bat" description="">
	<code>
	<![CDATA["c:\Users\nmishin\Documents\masterdomino\work_line\26032016\start_asp_project.bat"]]>
	</code>
	</snippet>
	<snippet name="dwh_process" description="">
	<code>
	<![CDATA[create or replace procedure dwh_process(p_dttm in date default sysdate)
	is
	l_report_dttm sys_data_load_log.report_dttm%type;
	l_load_type sys_data_load_log.load_type%type;
	l_status_cd sys_data_load_log.status_cd%type;
	l_max_log_id sys_data_load_log.log_id%type;
	l_next_log_id sys_data_load_log.log_id%type;
	begin
	select max(log_id) into l_max_log_id from sys_data_load_log l;
	if l_max_log_id is not null then
	select report_dttm, load_type, status_cd
	into l_report_dttm, l_load_type, l_status_cd
	from sys_data_load_log
	where log_id = l_max_log_id;
	end if;
	if (l_load_type = 'ASPLINK' and (l_status_cd = 'D' or l_status_cd = 'E')) or
	l_max_log_id is null then
	select sys_data_load_log_seq.nextval into l_next_log_id from dual;
	insert into sys_data_load_log
	(log_id, load_type, report_dttm, status_cd, loading_start_dttm,
	loading_end_dttm)
	values (l_next_log_id, 'DWH', trunc(cast(p_dttm as timestamp)), 'L', sysdate, null);
	commit;
	end if;
	delete from dwh_client;
	insert into dwh_client (clientid, ssn, title, firstname, lastname, birthdate, email,
	phonenumber, mobilenumber, streetaddress, streetaddress2, zipcode,
	state, country, currency, clienttype, mifidriskdate, riskclassid,
	advisorid)
	values (1, '', '', '', '', '', '', '', '', '', '', '', '', '', 'RUR', 'HNWI', '', 1, '00F3');
	insert into dwh_client (clientid, ssn, title, firstname, lastname, birthdate, email,
	phonenumber, mobilenumber, streetaddress, streetaddress2, zipcode,
	state, country, currency, clienttype, mifidriskdate, riskclassid,
	advisorid)
	values (2, '', '', '', '', '', '', '', '', '', '', '', '', '', 'RUR', 'HNWI', '', 2, '0035');
	insert into dwh_client (clientid, ssn, title, firstname, lastname, birthdate, email,
	phonenumber, mobilenumber, streetaddress, streetaddress2, zipcode,
	state, country, currency, clienttype, mifidriskdate, riskclassid,
	advisorid)
	values (3, '', '', '', '', '', '', '', '', '', '', '', '', '', 'RUR', 'HNWI', '', 4, '006D');
	insert into dwh_client (clientid, ssn, title, firstname, lastname, birthdate, email,
	phonenumber, mobilenumber, streetaddress, streetaddress2, zipcode,
	state, country, currency, clienttype, mifidriskdate, riskclassid,
	advisorid)
	values (00106225, '', '', '', '', '', '', '', '', '', '', '', '', '', 'RUR', 'HNWI', '', 1, '00NP');
	insert into dwh_client (clientid, ssn, title, firstname, lastname, birthdate, email,
	phonenumber, mobilenumber, streetaddress, streetaddress2, zipcode,
	state, country, currency, clienttype, mifidriskdate, riskclassid,
	advisorid)
	values (00113054, '', '', '', '', '', '', '', '', '', '', '', '', '', 'RUR', 'HNWI', '', 2, '00VN');
	insert into dwh_client (clientid, ssn, title, firstname, lastname, birthdate, email,
	phonenumber, mobilenumber, streetaddress, streetaddress2, zipcode,
	state, country, currency, clienttype, mifidriskdate, riskclassid,
	advisorid)
	values (00104736, '', '', '', '', '', '', '', '', '', '', '', '', '', 'RUR', 'HNWI', '', 3, '00QQ');
	delete from dwh_risk_class;
	insert into dwh_risk_class (riskclassid, rank, "name", description)
	values (1, 1, 'low', 'low');
	insert into dwh_risk_class (riskclassid, rank, "name", description)
	values (2, 2, 'normal', 'normal');
	insert into dwh_risk_class (riskclassid, rank, "name", description)
	values (3, 3, 'high', 'high');
	insert into dwh_risk_class (riskclassid, rank, "name", description)
	values (4, 4, 'maximum', 'maximum');
	delete from dwh_client_portfolio;
	insert into dwh_client_portfolio (portfolioid, inceptiondate, clientid, portfolioname,
	portfolionumber, servicetype, portfoliotypeid, frozen)
	values ('501040043045241000000', '01.02.2016', 1,
	'Долговые обязательства Российской Федерации',
	'501040043045241000000', 'C', 1, null);
	insert into dwh_client_portfolio (portfolioid, inceptiondate, clientid, portfolioname,
	portfolionumber, servicetype, portfoliotypeid, frozen)
	values ('501040043045241000342', '01.02.2016', 2,
	'Долговые обязательства Российской Федерации',
	'501040043045241000342', 'C', 2, null);
	insert into dwh_client_portfolio (portfolioid, inceptiondate, clientid, portfolioname,
	portfolionumber, servicetype, portfoliotypeid, frozen)
	values ('501040043045241001443', '01.02.2016', 3,
	'Долговые обязательства Российской Федерации',
	'501040043045241001443', 'C', 3, null);
	delete from dwh_client_bank_account;
	insert into dwh_client_bank_account (bankaccountid, inceptiondate, clientid, bankaccountname,
	bankaccountnumber, currency, servicetype, bankaccounttypeid,
	redemptiondate, "value", securityid, valuedate)
	values (40817810000000000001, '01.02.2016', 1, '40817810000000000001',
	'40817810000000000001', 'RUR', 'C', 1, '', 100, 'cash',
	'01.02.2016');
	insert into dwh_client_bank_account (bankaccountid, inceptiondate, clientid, bankaccountname,
	bankaccountnumber, currency, servicetype, bankaccounttypeid,
	redemptiondate, "value", securityid, valuedate)
	values (40817810000000000002, '01.02.2016', 2, '40817810000000000002',
	'40817810000000000002', 'RUR', 'C', 1, '', 200, 'cash',
	'01.02.2016');
	insert into dwh_client_bank_account (bankaccountid, inceptiondate, clientid, bankaccountname,
	bankaccountnumber, currency, servicetype, bankaccounttypeid,
	redemptiondate, "value", securityid, valuedate)
	values (40817810000000000003, '01.02.2016', 3, '40817810000000000003',
	'40817810000000000003', 'RUR', 'C', 1, '', 300, 'cash',
	'01.02.2016');
	delete from dwh_client_portfolio_holding;
	insert into dwh_client_portfolio_holding ("date", portfolioid, securityid, "value", amount,
	averagepurchasepriceinstcurr, averagepurchasepricebasiccurr,
	servicetype)
	values ('2016-04-01', '1', '1', null, 2, null, null, '');
	insert into dwh_client_portfolio_holding ("date", portfolioid, securityid, "value", amount,
	averagepurchasepriceinstcurr, averagepurchasepricebasiccurr,
	servicetype)
	values ('2016-04-01', '2', '2', null, 3, null, null, '');
	insert into dwh_client_portfolio_holding ("date", portfolioid, securityid, "value", amount,
	averagepurchasepriceinstcurr, averagepurchasepricebasiccurr,
	servicetype)
	values ('2016-04-01', '3', '3', null, 4, null, null, '');
	delete from dwh_client_transaction;
	insert into dwh_client_transaction (transactionid, transactiondate, settlementdate, portfolioid,
	bankaccountid, securityid, amount, price, "value", currency,
	operationtypeid, transactionfee, servicetype, transactiontax,
	canceledtype)
	values (1, '01.02.2016', '01.02.2016', '501040043045241000000',
	40817810000000000001, '1', 2, 10, 20, 'RUR', 1, 0, '', null, null);
	insert into dwh_client_transaction (transactionid, transactiondate, settlementdate, portfolioid,
	bankaccountid, securityid, amount, price, "value", currency,
	operationtypeid, transactionfee, servicetype, transactiontax,
	canceledtype)
	values (2, '01.02.2016', '01.02.2016', '501040043045241000342',
	40817810000000000002, '2', 4, 20, 80, 'RUR', 1, 0, '', null, null);
	insert into dwh_client_transaction (transactionid, transactiondate, settlementdate, portfolioid,
	bankaccountid, securityid, amount, price, "value", currency,
	operationtypeid, transactionfee, servicetype, transactiontax,
	canceledtype)
	values (3, '01.02.2016', '01.02.2016', '501040043045241001443',
	40817810000000000003, '3', 5, 30, 150, 'RUR', 2, 0, '', null, null);
	delete from dwh_security;
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('US5949181045', 'US5949181045', 'USD', 'Microsoft Corp.',
	'Microsoft Corp.', 3, 'Equity, ISIN US5949181045, WKN 870747, MSF',
	'', 'US', null, null, null, null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('US38259P5089', 'US38259P5089', 'USD', 'Google Inc.',
	'Google Inc.', 3, 'Equity, ISIN US38259P5089, WKN A0B7FY, GGQ1', '',
	'US', null, null, null, null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('US0378331005', 'US0378331005', 'USD', 'Apple Inc.', 'Apple Inc.',
	3, 'Equity, ISIN US0378331005, WKN 865985, APC', '', 'US', null,
	null, null, null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('NL0000729408', 'NL0000729408', 'EUR', 'Bear Zertifikat auf DAX',
	'Bear Zertifikat auf DAX', 3,
	'Investment Product, ISIN NL0000729408', '', 'NL', null, null, null,
	null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('JP3946600008', 'JP3946600008', 'JPY', 'Yusen Logistics Co.',
	'Yusen Logistics Co.', 3,
	'Equity, ISIN JP3946600008, WKN 919744, YV5', '', 'JP', null, null,
	null, null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('DE000DZ21632', 'DE000DZ21632', 'EUR', 'Aktienanleihe auf BASF',
	'Aktienanleihe auf BASF', 3,
	'Investment Product, ISIN DE000DZ21632, WKN', '', 'DE', null, null,
	null, null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('DE000DB7HWY7', 'DE000DB7HWY7', 'EUR', 'Aktienanleihe Plus auf Al',
	'Aktienanleihe Plus auf Al', 3,
	'Investment Product, ISIN DE000DB7HWY7, WKN', '', 'DE', null, null,
	null, null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('DE000CM7VX13', 'DE000CM7VX13', 'EUR', 'Aktienanleihe Plus auf',
	'Aktienanleihe Plus auf', 1,
	'Investment Product, ISIN DE000CM7VX13, WKN', '', 'DE', null, null,
	null, null);
	insert into dwh_security (securityid, isin, currency, "name", shortname, securitytypeid,
	description, wwwlink, country, issuerid, riskclassid,
	mifidsecuritytypeid, facevalue)
	values ('CH0031240127', 'CH0031240127', 'CHE', 'BMW Australia',
	'BMW Australia', 5, 'Bond, ISIN CH0031240127, WKN A0NWXQ', '', 'CH',
	null, null, null, null);
	delete from dwh_security_quotation;
	insert into dwh_security_quotation ("date", securityid, currency, quotationvalue, unitamount,
	quotationtype, percentage)
	values (to_date('01.02.2016', 'dd.mm.yyyy'), '5', 'RUR', 70, 1, 'Курс USD', 0);
	insert into dwh_security_quotation ("date", securityid, currency, quotationvalue, unitamount,
	quotationtype, percentage)
	values (to_date('01.02.2016', 'dd.mm.yyyy'), '6', 'RUR', 80, 1, 'Курс EUR', 0);
	insert into dwh_security_quotation ("date", securityid, currency, quotationvalue, unitamount,
	quotationtype, percentage)
	values (to_date('01.02.2016', 'dd.mm.yyyy'), '7', 'RUR', 12, 1, 'Курс CHN', 0);
	delete from dwh_advisor;
	insert into dwh_advisor (advisorid, firstname, lastname, "identifier", email, phonenumber,
	mobilenumber, country)
	values ('00F3', '', '', '10023', '', '', '', '');
	insert into dwh_advisor (advisorid, firstname, lastname, "identifier", email, phonenumber,
	mobilenumber, country)
	values ('0035', '', '', '43224', '', '', '', '');
	insert into dwh_advisor (advisorid, firstname, lastname, "identifier", email, phonenumber,
	mobilenumber, country)
	values ('006D', '', '', '43245', '', '', '', '');
	insert into dwh_advisor (advisorid, firstname, lastname, "identifier", email, phonenumber,
	mobilenumber, country)
	values ('00NP', '', '', '19998', '', '', '', '');
	insert into dwh_advisor (advisorid, firstname, lastname, "identifier", email, phonenumber,
	mobilenumber, country)
	values ('00QQ', '', '', '16875', '', '', '', '');
	insert into dwh_advisor (advisorid, firstname, lastname, "identifier", email, phonenumber,
	mobilenumber, country)
	values ('00VN', '', '', '11645', '', '', '', '');
	delete from dwh_operation_type;
	insert into dwh_operation_type (operationtypeid, code, "name", description, direction)
	values (120, 'BUY', 'Buy', 'Buy transaction on secondary market', 'CREDIT');
	insert into dwh_operation_type (operationtypeid, code, "name", description, direction)
	values (121, 'SELL', 'Sell', '', 'DEBIT');
	insert into dwh_operation_type (operationtypeid, code, "name", description, direction)
	values (122, 'CANCEL', 'Cancel', 'Cancellation of earlier transaction', 'NO CHANGE');
	delete from dwh_security_type;
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (1, 'IF', 'Investment fund', 'Investment fund');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (2, 'UL', 'Unit linked', 'Unit linked');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (3, 'Equity', 'Капитал', 'Капитал');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (4, 'STOCK', 'акция', 'акция');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (5, 'BOND', 'облигация', 'облигация');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (6, 'BILL', 'вексель', 'вексель');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (7, 'BANK_CER', 'банковские сертификаты', 'банковские сертификаты');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (8, 'BILL_OF_LOADING', 'коносамент', 'коносамент');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (9, 'CHECK', 'чек', 'чек');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (10, 'WARRANT', 'варрант', 'варрант');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (11, 'CER_DEP', 'депозитный сертификат', 'депозитный сертификат');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (12, 'SAVING_CER', 'сберегательный сертификат', 'сберегательный сертификат');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (13, 'MORTGAGE', 'закладная', 'закладная');
	insert into dwh_security_type (securitytypeid, code, "name", description)
	values (14, 'TRUST_CER', 'трастовый сертификат', 'трастовый сертификат');
	delete from dwh_bank_account_type;
	insert into dwh_bank_account_type (bankaccounttypeid, code, "name", description, deposit)
	values (1, 'CA', 'Current Account', 'Текущий счет', 0);
	insert into dwh_bank_account_type (bankaccounttypeid, code, "name", description, deposit)
	values (2, 'CD', 'Call Depoit', 'Депозит до востребования', 1);
	insert into dwh_bank_account_type (bankaccounttypeid, code, "name", description, deposit)
	values (3, 'DC', 'Debit Card', 'Дебетовая карта', 0);
	delete dwh_calendar;
	insert into dwh_calendar ("date", description)
	values (to_date('06.02.2016', 'dd.mm.yyyy'), '');
	insert into dwh_calendar ("date", description)
	values (to_date('07.02.2016', 'dd.mm.yyyy'), '');
	insert into dwh_calendar ("date", description)
	values (to_date('13.02.2016', 'dd.mm.yyyy'), '');
	delete from dwh_department;
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('60374', 'Департамент частного банковского обслуживания', null, 'RU', null);
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('60375', 'Управление продаж продуктов частного банковского обслуживания', null, 'RU', '60374');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('60376', 'Дополнительный офис для привилегированных клиентов "Чистые пруды"', null, 'RU', '60375');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('60377', 'Отдел продаж и развития сети в Москве и регионах', null, 'RU', '60375');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('60378', 'Отдел разработки продуктов частного банковского обслуживания', null, 'RU', '60374');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('60379', 'Отдел разработки инвестиционной стратегии', null, 'RU', '60374');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('1382926', 'Отдел координации и развития региональной сети', null, 'RU', '60375');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('1382927', 'Отдел разработки инвестиционной стратегии и банковских продуктов', null, 'RU', '60374');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('1383053', 'Отдел координации и развития региональной сети', null, 'RU', '1382926');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('1383054', 'Группа по работе с привилегированными клиентами (Санкт-Петербург)', null, 'RU', '1382926');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('1383055', 'Отдел разработки инвестиционной стратегии и банковских продуктов', null, 'RU', '1382927');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61567', 'Дополнительный офис для привилегированных клиентов "Чистые пруды"', null, 'RU', '60376');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61568', 'Группа по обслуживанию привилегированных клиентов', null, 'RU', '60376');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61569', 'Группа по работе с привилегированными клиентами', null, 'RU', '60376');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61570', 'Группа клиентской поддержки', null, 'RU', '60376');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61571', 'Отдел продаж и развития сети в Москве и регионах', null, 'RU', '60377');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61572', 'Группа по работе с привилегированными клиентами в Москве', null, 'RU', '60377');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61573', 'Группа координации региональной сети', null, 'RU', '60377');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61574', 'Группа планирования и поддержки продаж', null, 'RU', '1361540');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61576', 'Отдел разработки продуктов частного банковского обслуживания', null, 'RU', '60378');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61577', 'Отдел разработки инвестиционной стратегии', null, 'RU', '60379');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61565', 'Руководство', null, 'RU', '60374');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('61566', 'Руководство', null, 'RU', '60375');
	insert into dwh_department (departmentid, "name", description, country, parentdepartmentid)
	values ('1361540', 'Группа планирования и поддержки продаж', null, 'RU', '60375');
	update sys_data_load_log
	set status_cd = 'D', loading_end_dttm = sysdate
	where log_id = l_next_log_id;
	commit;
	end dwh_process;]]>
	</code>
	</snippet>
	</group>
	</snippets>

view raw UserSnippets.xml hosted with ❤ by GitHub

	<?xml version="1.0" encoding="UTF-8" ?>
	<displays>
	<folder>
	<name><![CDATA[lock_reports]]></name>
	<tooltip><![CDATA[lock_reports]]></tooltip>
	<description><![CDATA[ ]]></description>
	<display id="2e939ded-0154-1000-8003-c080002ecd22" type="" style="Table" enable="true">
	<name><![CDATA[who_block?]]></name>
	<description><![CDATA[]]></description>
	<tooltip><![CDATA[]]></tooltip>
	<drillclass><![CDATA[]]></drillclass>
	<CustomValues>
	<TYPE>horizontal</TYPE>
	</CustomValues>
	<query>
	<sql><![CDATA[select (select username
	from v$session
	where sid = a.sid) blocker
	, a.sid
	, ' is blocking '
	, (select username
	from v$session
	where sid = b.sid) blockee
	, b.sid
	from v$lock a
	, v$lock b
	where a.block = 1
	and b.request > 0
	and a.id1 = b.id1
	and a.id2 = b.id2]]></sql>
	</query>
	<pdf version="VERSION_1_7" compression="CONTENT">
	<docproperty title="null" author="null" subject="null" keywords="null" />
	<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
	<column>
	<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
	<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
	<blob blob="NONE" zip="false" />
	</column>
	<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
	<header enable="false" generatedate="false">
	<data>
	null </data>
	</header>
	<footer enable="false" generatedate="false">
	<data value="null" />
	</footer>
	<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
	<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
	</security>
	<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
	</pdf>
	</display>
	<display id="2e9a207d-0154-1000-8006-c080002ecd22" type="" style="Table" enable="true">
	<name><![CDATA[user_lock]]></name>
	<description><![CDATA[]]></description>
	<tooltip><![CDATA[]]></tooltip>
	<drillclass><![CDATA[]]></drillclass>
	<CustomValues>
	<TYPE>horizontal</TYPE>
	</CustomValues>
	<query>
	<sql><![CDATA[select * from v$lock where type='UL']]></sql>
	</query>
	<pdf version="VERSION_1_7" compression="CONTENT">
	<docproperty title="null" author="null" subject="null" keywords="null" />
	<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
	<column>
	<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
	<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
	<blob blob="NONE" zip="false" />
	</column>
	<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
	<header enable="false" generatedate="false">
	<data>
	null </data>
	</header>
	<footer enable="false" generatedate="false">
	<data value="null" />
	</footer>
	<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
	<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
	</security>
	<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
	</pdf>
	</display>
	<display id="2e88d6c3-0154-1000-8001-c080002ecd22" type="" style="Table" enable="true">
	<name><![CDATA[lock]]></name>
	<description><![CDATA[]]></description>
	<tooltip><![CDATA[]]></tooltip>
	<drillclass><![CDATA[]]></drillclass>
	<CustomValues>
	<TYPE>horizontal</TYPE>
	</CustomValues>
	<query>
	<sql><![CDATA[with sess as (select s.sid,
	to_char(s.logon_time, 'dd.mm hh24:mi') logon_time,
	s.username,
	s.module,
	s.command cmd,
	s.last_call_et lc,
	s.seconds_in_wait sw,
	s.process,
	s.status
	from gv$session s join gv$process p
	on p.addr = s.paddr
	and p.inst_id = s.inst_id
	where s.type != 'BACKGROUND'),
	blocked_sess as
	(select la.name,
	la.lockid,
	lo.lock_id2,
	to_char(la.expiration, 'dd.mm') exp,
	lo.session_id sid,
	lo.lock_type,
	lo.mode_held,
	lo.mode_requested,
	lo.blocking_others
	from sys.dbms_lock_allocated la join dba_locks lo
	on (la.lockid = lo.lock_id1)
	where la.name not like 'ORA$%')
	select bs.*, s.*
	from sess s left join blocked_sess bs on s.sid = bs.sid
	where bs.lockid is not null]]></sql>
	</query>
	<pdf version="VERSION_1_7" compression="CONTENT">
	<docproperty title="null" author="null" subject="null" keywords="null" />
	<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
	<column>
	<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
	<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
	<blob blob="NONE" zip="false" />
	</column>
	<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
	<header enable="false" generatedate="false">
	<data>
	null </data>
	</header>
	<footer enable="false" generatedate="false">
	<data value="null" />
	</footer>
	<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
	<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
	</security>
	<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
	</pdf>
	</display>
	<display id="2e923975-0154-1000-8002-c080002ecd22" type="" style="Table" enable="true">
	<name><![CDATA[lock_big_report]]></name>
	<description><![CDATA[]]></description>
	<tooltip><![CDATA[]]></tooltip>
	<drillclass><![CDATA[]]></drillclass>
	<CustomValues>
	<TYPE>horizontal</TYPE>
	</CustomValues>
	<query>
	<sql><![CDATA[with sess as
	(select s.inst_id iid,
	s.sid,
	s.serial#,
	p.spid,
	s.logon_time,
	s.osuser,
	s.machine,
	s.username,
	s.module,
	s.action,
	s.command cmd,
	s.last_call_et lc,
	s.seconds_in_wait sw,
	s.process,
	s.status
	from gv$session s
	join gv$process p
	on p.addr = s.paddr
	and p.inst_id = s.inst_id
	where s.type != 'BACKGROUND'
	and s.username in ('NMISHIN_AL','DWH2ASPLINK')),-- 'ASPLINK',
	blocked_sess as
	(select la.name,
	la.lockid,
	la.expiration,
	lo.session_id,
	lo.lock_type,
	lo.mode_held,
	lo.mode_requested,
	lo.lock_id1,
	lo.lock_id2,
	lo.last_convert,
	lo.blocking_others
	from sys.dbms_lock_allocated la
	join dba_locks lo
	on (la.lockid = lo.lock_id1)
	where la.name not like 'ORA$%'
	and la.name = 'control_lock')
	select s.*, bs.*
	from sess s
	left join blocked_sess bs
	on s.sid = bs.session_id
	order by s.logon_time desc, s.username, s.status]]></sql>
	</query>
	<pdf version="VERSION_1_7" compression="CONTENT">
	<docproperty title="null" author="null" subject="null" keywords="null" />
	<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
	<column>
	<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
	<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
	<blob blob="NONE" zip="false" />
	</column>
	<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
	<header enable="false" generatedate="false">
	<data>
	null </data>
	</header>
	<footer enable="false" generatedate="false">
	<data value="null" />
	</footer>
	<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
	<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
	</security>
	<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
	</pdf>
	</display>
	</folder>
	</displays>

view raw UserReports.xml hosted with ❤ by GitHub

synchronized accessing table in Oracle

We have 2 session and 1 table to operate with

If we want to edit, we check lock and throw an error if the table is already busy:

	declare
	v_result number;
	v_lockhandle varchar2(200);
	v_lock_name varchar2(200);
	in_use exception;
	pragma exception_init(in_use, -20001);
	l_message varchar2(200) := 'System waiting while loading';
	begin
	v_lock_name := user || '_queue_lock';
	dbms_lock.allocate_unique(lockname => v_lock_name,
	lockhandle => v_lockhandle);
	v_result := dbms_lock.request(lockhandle => v_lockhandle,
	lockmode => dbms_lock.x_mode,
	timeout => 0,
	release_on_commit => true);
	if v_result != 0 then
	raise_application_error(-20001, l_message);
	end if;
	end;

view raw wait_while_loading.sql hosted with ❤ by GitHub

While load we make lock befor

	declare
	v_result number;
	v_lockhandle varchar2(200);
	v_lock_name varchar2(200);
	begin
	v_lock_name := user || '_queue_lock';
	dbms_lock.allocate_unique(lockname => v_lock_name,
	lockhandle => v_lockhandle);
	v_result := dbms_lock.request(lockhandle => v_lockhandle,
	lockmode => dbms_lock.x_mode,
	timeout => 600,
	release_on_commit => false);
	end;

view raw lockTasksQueue.sql hosted with ❤ by GitHub

And unlock after

	declare
	v_result number;
	v_lockhandle varchar2(200);
	v_lock_name varchar2(200);
	begin
	v_lock_name := user || '_queue_lock';
	dbms_lock.allocate_unique(v_lock_name, v_lockhandle);
	v_result := dbms_lock.release(v_lockhandle);
	end;

view raw unlockTasksQueue.sql hosted with ❤ by GitHub

For working with dbms_lock you need get right:

grant execute on sys.dbms_lock to lock_user;

view raw grant_dbms_lock.sql hosted with ❤ by GitHub

To view locks

	with sess as (select s.sid,
	to_char(s.logon_time, 'dd.mm hh24:mi') logon_time,
	s.username,
	s.module,
	s.command cmd,
	s.last_call_et lc,
	s.seconds_in_wait sw,
	s.process,
	s.status
	from gv$session s join gv$process p
	on p.addr = s.paddr
	and p.inst_id = s.inst_id
	where s.type != 'BACKGROUND'
	and s.username in ('LOCK_USER')),
	blocked_sess as
	(select la.name,
	la.lockid,
	lo.lock_id2,
	to_char(la.expiration, 'dd.mm') exp,
	lo.session_id sid,
	lo.lock_type,
	lo.mode_held,
	lo.mode_requested,
	lo.blocking_others
	from sys.dbms_lock_allocated la join dba_locks lo
	on (la.lockid = lo.lock_id1)
	where la.name not like 'ORA$%')
	select bs.*, s.*
	from sess s left join blocked_sess bs on s.sid = bs.sid
	order by s.logon_time desc, s.username, s.status;

view raw view_lock_1.sql hosted with ❤ by GitHub

select * from v$lock where type='UL';

view raw view_lock_2.sql hosted with ❤ by GitHub

	select (select username
	from v$session
	where sid = a.sid) blocker
	, a.sid
	, ' is blocking '
	, (select username
	from v$session
	where sid = b.sid) blockee
	, b.sid
	from v$lock a
	, v$lock b
	where a.block = 1
	and b.request > 0
	and a.id1 = b.id1
	and a.id2 = b.id2;

view raw one session blocked another.sql hosted with ❤ by GitHub

User defined locking with DBMS_LOCK
DBMS_LOCK.REQUEST (PLS5H3)

tips_of_java

	// 1. Date calculating
	// old code:
	Calendar calLoadDate = Calendar.getInstance();
	calLoadDate.setTime(loadDate);
	Calendar cal = Calendar.getInstance();
	cal.setTime(date);
	cal.set(Calendar.HOUR_OF_DAY, calLoadDate.get(Calendar.HOUR_OF_DAY));
	cal.set(Calendar.MINUTE, calLoadDate.get(Calendar.MINUTE));
	cal.set(Calendar.SECOND, calLoadDate.get(Calendar.SECOND));
	date = cal.getTime();
	// new code:
	date = new Date(date.getTime() + loadDate.getTime() - DateUtils.truncate(loadDate, Calendar.DATE).getTime());
	// 2. Check if file exists on sftp
	// old code:
	/**
	* Checks if is target files exists.
	* @param client sftp client object
	* @param trgDir the trg dir
	* @param fileName the file name
	* @return true, if is target files exists
	* @throws Exception directory does not exists
	*/
	private boolean isTargetFilesExists(SftpClient client, String trgDir, String fileName) throws Exception {
	boolean isExists = false;
	try {
	client.cd(trgDir);
	SftpFile[] listing = client.ls();
	for (SftpFile f : listing) {
	if (f.isFile() && f.getFilename().equals(fileName)) {
	isExists = true;
	}
	}
	} catch (Exception e) {
	throw new Exception(String.format(messages.getProperty("error.dir.not.exists.set.dir"), trgDir));
	}
	return isExists;
	}
	// new code:
	/**
	* Checks if is target files exists.
	* @param client sftp client object
	* @param trgDir the trg dir
	* @param fileName the file name
	* @return true, if is target files exists
	* @throws Exception directory does not exists
	*/
	private boolean isTargetFilesExists(SftpClient client, String trgDir, String fileName) throws Exception {
	try {
	client.cd(trgDir);
	return Stream.of(client.ls()).anyMatch(f -> f.getFilename().equals(fileName));
	} catch (Exception e) {
	throw new Exception(String.format(messages.getProperty("error.dir.not.exists.set.dir"), trgDir));
	}
	}
	// 3. Move to AsserJ
	- compableData.stream().forEach(value -> assertThat(recAsplClient).contains(value));
	+ assertThat(recAsplClient).containsAll(compableData);
	- assertEquals(String.format("Difference between Table %s and Data must be zero!", asplTable), actual, primitive);
	+ assertThat(actual).as("Difference between Table %s and Data must be zero!", asplTable).isEqualTo(0);
	+ assertTrue(CollectionUtils.isEqualCollection(recAsplClient, recAsplClientShoudBe));
	// 4. Boolean -> boolean - you never got npe!

view raw tips_of_java.java hosted with ❤ by GitHub

remove advertising from skype in Windows 10

in
c:\Windows\System32\drivers\etc\hosts

add lines

127.0.0.1 rad.msn.com
127.0.0.1 apps.skype.com