愛晴狂想曲

假設有一個Oracle Package Stored Procedure，相關架構如下， 名稱：Package1.StoredProcedure1 參數1：input，料號 參數2：output，成本 VBS可以透過下面的寫法取得Output參數的回傳值， SET con = CreateObject("ADODB.Connection") con.Open DB_Connection_String strItemNo = "ABC00001" curItemCost = 0 SET cmd = CreateObject("ADODB.Command") cmd.CommandText = "Package1.StoredProcedure1" cmd.ActiveConnection = con cmd.NamedParameters = true cmd.CommandType = adCmdStoredProc cmd.Parameters.Append(cmd.CreateParameter("ItemNo", adVarChar, adParamInput, 50, strItemNo)) cmd.Parameters.Append(cmd.CreateParameter("ItemCost", adCurrency, adParamOutput, , curItemCost)) cmd.Execute msgbox "Item Cost: " & cmd.Parameters("ItemCost").Value Reference Web: http://docs.starquest.com/Supportdocs/techStarSQL/StarSQLODBC/Programming/SQ055_VBScript.shtml http://stackoverflow.com/questions/10017872/calling-sql-stored-procedure-with-output-parameter-in-vbscript

因為Stored Procedure只允許執行DML(Data Manipulation Language)指令，如SELECT、INSERT，而Truncate Table指令屬於DDL(Data Definition Language)指令，如Create Table、Alter Table。 所以若直接在Stored Procedure裡下Truncate Table語法，會發生錯誤， CREATE OR REPLACE PROCEDURE SP_ALEX_TEST AS BEGIN TRUNCATE TABLE tablename; END SP_ALEX_TEST; / 錯誤(3,12): PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue. 所以必需改由下列方法來達到執行效果， --<方法一> execute immediate 'truncate table tablename'; --<方法二> DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE tablename;'); 不過實測後，都可以compile，但只有<方法一>會刪除資料，而<方法二>雖有正常執行完成，但資料並未被刪除。 reference web: http://stackoverflow.com/questions/625743/truncating-a-table-in-a-stored-procedure