Excel VBA Example – DDL Generator

DownloadDDLGenerator

(Rename ddlgenerator.xls to ddlgenerator.xlsm)

please rename to file from ddlgenerator.xlsx to ddlgenerator.xlsm

 

Function: It would generate Oracle SQL files for specified table manipulation.

 

Image 011

Image 002

Four files would be generated

Image 001Image 014

Image 003

In the working path of the excel, you could find two sql file. One is the add column ddl file while another is drop column ddl file. The file name is DDL_AddColumn_<table name>.sql,  DDL_DropColumn_<table name>.sql, DDL_CreateTable_<table name>.sql and DDL_DropTable_<table name>.sql

Source Code

 

Sub genDDL()

 For i = 1 To Worksheets.Count
 If Worksheets.Item(i).Name = "TableAddColumn" Then
 Dim currentWorksheet As Worksheet
 Set currentWorksheet = Worksheets.Item(i)
 
 ' Table Name
 tableName = currentWorksheet.Cells(1, 2)
 
 ' #1 as Add Column DDL file
 addColumnDDLFile = ActiveWorkbook.Path + "\DDL_AddColumn_" + tableName + ".sql"
 Open addColumnDDLFile For Output As #1
 
 ' #2 as Drop Column DDL file
 dropColumnDDLFile = ActiveWorkbook.Path + "\DDL_DropColumn_" + tableName + ".sql"
 Open dropColumnDDLFile For Output As #2
 
 ' #3 as Create Table DDL file
 createTableDDLFile = ActiveWorkbook.Path + "\DDL_CreateTable_" + tableName + ".sql"
 Open createTableDDLFile For Output As #3
 
 ' #4 as drop table DDL file
 dropTableDDLfile = ActiveWorkbook.Path + "\DDL_DropTable_" + tableName + ".sql"
 Open dropTableDDLfile For Output As #4
 
 Print #1, "ALTER TABLE " + tableName + " ADD ("
 Print #2, "ALTER TABLE " + tableName + " DROP ("
 Print #3, "Create Table " + tableName + " ("
 Print #4, "Drop Table " + tableName; ";"
 
 ' Reading columnValue until not empty
 i = 3 ' row number
 Do While currentWorksheet.Cells(i, 1) <> ""
 
 ' For add column statement
 preparedPartialAddColumnSQL = currentWorksheet.Cells(i, 1) + " " _
 + currentWorksheet.Cells(i, 2) + "(" _
 + currentWorksheet.Cells(i, 3).Text
 
 ' If it specify the unit, add unit other do not add it
 If (currentWorksheet.Cells(i, 4) <> "") Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + " " + currentWorksheet.Cells(i, 4)
 End If
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + ")"
 
 ' If it marked "Y" for not null then add not null to the prepared partial sql statement
 If (currentWorksheet.Cells(i, 5) = "Y") Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + " NOT NULL"
 End If
 
 ' If text line have text, add "," at the end of line
 If currentWorksheet.Cells(i + 1, 1) <> "" Then
 preparedPartialAddColumnSQL = preparedPartialAddColumnSQL + ","
 End If
 
 ' For drop column statement
 preparedPartialDropColumnSQL = currentWorksheet.Cells(i, 1)
 If currentWorksheet.Cells(i + 1, 1) <> "" Then
 preparedPartialDropColumnSQL = preparedPartialDropColumnSQL + ","
 End If
 
 Print #1, " " + preparedPartialAddColumnSQL
 Print #2, " " + preparedPartialDropColumnSQL
 Print #3, " " + preparedPartialAddColumnSQL
 i = i + 1
 columnValue = currentWorksheet.Cells(3, 1)
 Loop
 
 Print #1, ");"
 Print #2, ");"
 Print #3, ");"
 
 ' Close output file
 Close #1
 Close #2
 Close #3
 Close #4
 
 End If
 Next i

 
End Sub


Feasible Aspects

  • Index
  • Tablespace
  • DB Connection
  • Constraints
  • Primary Key
  • Foreign Key

 

Reference: Create Table SQL Statement Information

Image 001
Proposed New Excel Template (For provide more function)

 

Whatsapp 網頁版 和 手機的溝通

Whatsapp 網頁版 如何可得到手機的認證?

Whatsapp 網頁提供了QR Code. 用家只需用手機的Whatsapp 掃一掃這個 QR Code 就可以用到 Whatsapp 網頁版。

Whatapp Web Page

究竟Whatsapp 網頁版如何做到呢?

使用Chrome 開啟 Development Tool 的 Network,就會看到有一個 Websocket 的Connection。當用家掃了 QR Code , Whatsapp 網頁版的 Server 就會通知 用家的網頁,啟用 Whatsapp 網頁版。

Whatsapp Page Network

WebSphere – Enable / Disable Security and then Change User Password

Disable Security 

  1. <WAS_INSTALL_DIR>/bin/> wsadmin -conntype NONE
  2. wsadmin> securityoff
  3. wsadmin> exit
  4. Restart the servers.

Change User Password

After disabling the security of WebSphere, the user password can be changed in the Web Console.

Image 004Image 005

 

Enable Security

  1. <WAS_INSTALL_DIR>/bin/> wsadmin -conntype NONE
  2. wsadmin> $AdminTask setGlobalSecurity {-enabled true}
  3. wsadmin> $AdminConfig save
  4. wsadmin> exit
  5. Restart the servers.

 

Reference: 

Oracle Database Overview – Deployment

Deployment

General order for installation script

  1. Package specifications
  2. Tables (with constraints and indexes) in proper order
  3. Sequences (because they are most often used by triggers)
  4. Triggers
  5. Synonyms
  6. Views (because they may reference functions, procedures, or synonyms)
  7. Package bodies
  8. Data (optionally disabling all constraints before loading the data and re-enabling them afterwards)

(Please note that there would be exception)

Installation

Typically, the installation script is run using SQLplus

e.g

spool my_dir/create_log.txt
@my_dir/2day_tables.sql
@my_dir/2day_plsql.sql
@my_dir/2day_other.sql
@my_dir/2day_data.sql
commit;
spool off