Creating Test Script With Bind Variable

[20150814] Creating Test Script With Bind Variable.txt

--原連結:
http://blog.itpub.net/267265/viewspace-1700792/
http://blog.itpub.net/267265/viewspace-1401633/
http://blog.itpub.net/267265/viewspace-764543/

--我在原來的基礎上做了許多修正。純粹是工作需要，解決一些最佳化問題。

--我們的生產系統我設定會話cursor_sharing=force，主要程式存在大量沒有使用繫結變數的情況，這樣一些常數引數也變成了引數，
--今天建立一個新的指令碼，把裡面:"SYS_B_NN" 替換為 常量， 歡迎大家測試：

--注意我日期型別選擇環境變數
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

--這樣直接使用字串表示時間就沒有問題。

-------------------------------------------------------------------------------------------------------
--
-- File name:   build_bind_vars3.sql
--
-- Purpose:     Build SQL*Plus test script with variable definitions
--
-- Author:      Jack Augustin and Kerry Osborne
--
-- Description: This script creates a file which can be executed in SQL*Plus. It creates bind variables,
--              sets the bind variables to the values stored in V$SQL_BIND_CAPTURE, and then executes
--              the statement. The sql_id is used for the file name and is also placed in the statement
--              as a comment. Note that numeric bind variable names are not permited in SQL*Plus, so if
--              the statement has numberic bind variable names, they have an 'N' prepended to them. Also
--              note that CHAR variables are converted to VARCHAR2.
--
-- Usage:       This scripts prompts for two values.
--
--              sql_id:   this is the sql_id of the statement you want to duplicate
--
--              child_no: this is the child cursor number from v$sql
--                        (the default is 0 second)
--
--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1771755/，如需轉載，請註明出處，否則將追究法律責任。