Authority with Synonym

Authorities / Grant option:

Alter

Delete

Index

Insert

References

Select

Update

When drop synonym

Private synonym: Drop any synonym

Public synonym: Drop public synonym

Delete data by using synonym in Oracle

Code:

DELETE FROM syn_table_name;

Attention

User have delete authority.

Why don't use truncate?

As Synonyms can't be used in drop table/view or truncate table/cluster statements. It will cause ORA-00942: table or view does not exist.

VBA: Get all files' name and content in folder

As an example, save txt files in C://temp/

Code:

Dim file As String
Dim r As Integer
Dim text As String

Path = "C://temp/"
'get first file name
fileName =Dir(Path + "*.txt")
r = 1
'Start the loop.
Do While Len(fileName) > 0
    'output file name
    Cells(r, 1).Value = fileName
    'get content
    filePath = Path + fileName
    Open filePath For Input As #1
        Do Until EOF(1)
            Line Input #1, text
        Loop
        'output content
        Cells(r, 2).Value = text
    Close #1

    'Get next file.
    file = Dir()
Loop

Reference:
Dir Function:Returns a string representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

VBA: Get max row/column number|最大行/列取得

Max row number

MaxRow = Range("A1").End(xlToRight).End(xlDown).Row

Max Column number

MaxCol = Range("A1").End(xlToRight).End(xlToRight).Column

Or count back of end

MaxRow = Range("A666666").End(xlUp).Row
MaxCol = Range("IV1").End(xlToLeft).Column

In Micsoft Excel 2010, the maximum row is 1048576, the maximum column is XFD

Insert: Ignore duplicate key｜插入数据时无视一致性错误｜インサート: 一意制約を無視する

Use hint:
IGNORE_ROW_ON_DUPKEY_INDEX

Example｜例
example_tbl and example_tbl_pk
Code:

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(example_tbl, example_tbl_pk) */
INTO example_tbl
VALUES (id, ...);

If this id (primary key) isn't exist in example_tbl, insert this record. Or ignore this record and do next function.

Automatically number rows in Select自動採番

Use rownum, number count from 1;

Example:

insert into table2
select rownum, t3.name
from table1 t1
join table3 t3 on t1.id=t3.id

Oracle performance tuning Tips: speed up

In order to speed up, index is important.
Avoid doing Full Scan.

Speed:
Index Unique scan > Index range scan > Index full scan

Index points:
Unique index 一意性が高い索引を
Start with frequently-used columns 使用頻度の高い列を先頭に
Start with low selectivity columns 選択率の低い列を先頭に
Include key with non-unique index 非ユニーク索引にはキー圧縮

Other:
Use hint efficiently.
/*+ INDEX( e e_index_1) */

It's better to use NVL(e1,e2) = e2 instead of "is null" if null data is less than no-null data.

Window Startup setting

Set the startup application.

Use key [Win+R]

Input [msconfig] and press Enter key or click OK button.

Select tab [startup]

Uncheck/check the app and Apply or OK.

Unchecked app means it wantn't startup with Windows, and when it been uncheck is recorded.

Page Redirect/画面遷移/页面跳转

By meta tag:

<meta http-equiv="refresh" content="seconds;URL=url">

Example:

<meta http-equiv="refresh" content="0;URL=https://randinblogger.blogspot.com">

By JavaScript:

window.location = "url";

window.location.href = "url";

window.location.assign = "url";

window.location.replace = "url";

By .htaccess file:

mod_rewrite need be actived.

Redirect 301 / url 

How to set PHPExcel header

Code:

//.xls file
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=download.xls");

//.xlsx file
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=download.xlsx");

//Same part:
header("Cache-Control: max-age=0");

// Sample
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-ravalidate, post-check=0, pre-check=0');
header('Content-Type: application/force-download');
header('Content-Type: application/octet-stream');
header('Content-Type: application/download');
header("Content-disposition: attachment; filename=sample.xls");

References:
https://support.microsoft.com/en-us/kb/936496

SQL Developer "Enter the full pathname for java.exe"

When start Oracle SQL Developer first time, it will ask for full pathname for the "java.exe".

SQL Developer path:

...\sqldeveloper\sqldeveloper\bin

java.exe path:

...\jdk

"..." means "C:\\app\[user]\product\[Oracle version number]\client_1
For example:
SQL Developer path:

C:\\app\administrator\product\11.2.0\client_1\sqldeveloper\sqldeveloper\bin

java.exe path:

C:\\app\administrator\product\11.2.0\client_1\jdk

Or modify

...\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

Add "SetJavaHome ..\..\..\jdk, then no need to input java.exe pathname anymore.

JavaScript get browser info by navigator获取浏览器信息

The information from navigator may be misleading, unrecommend to be used to detect browser.

Platform:

javascript:(function()%7Balert(navigator.platform);%7D)();

App Name:

javascript:(function()%7Balert(navigator.appName);%7D)();

Netscape is name of both IE11, Chrome, Firefox, Safari

Cookie status:

javascript:(function()%7Balert(navigator.cookieEnabled);%7D)();

App code name:

javascript:(function()%7Balert(navigator.appCodeName);%7D)();

Mozilla is app code name of both IE, Chrome, Firefox, Safari, Opera

Engine:

javascript:(function()%7Balert(navigator.product);%7D)();

Version:

javascript:(function()%7Balert(navigator.appVersion);%7D)();

Agent:

javascript:(function()%7Balert(navigator.userAgent);%7D)();

Screen width:

javascript:(function()%7Balert(window.screen.width);%7D)();

Screen height:

javascript:(function()%7Balert(window.screen.height);%7D)();