Oracle Magazine, September/October 2017
Database Application Developer and DBA BEYOND SQL 101 Now compare the result in Listing 2 with that in Listing 3 which places the SQL statement from Listing 2 into a script file parameterize_ your_ script sql and calls the file from a SQL Plus command prompt Note that the substitution in Listing 3 is done without prompting and that the substitution is done positionally Positional replacement means that in order from left to right any parameter values passed to the parameterize_ your_ script sql file at the SQL Plus command prompt are passed as substitution values to any numbered parameters In Listing 3 the parameterize_ your_ script sql file has just one numbered parameter 1 Listing 4 expands the parameterize_ your_ script sql script created in Listing 3 to further demonstrate positional notation in parameter value replacement Code Listing 3 Place the parameterized statement in a script and execute it from the SQL Plus command prompt Create a database script called parameterize_ your_ script sql select table_ name num_ rows to_ char last_ analyzed DD MON YYYY HH24 MI SS last_ analyzed from user_ tables where table_ name 1 ORACLE MAGAZINE SEPTEMBER OCTOBER 2017 112 Execute the script with a parameter SQL @ parameterize_ your_ script sql EMPLOYEE old 3 where table_ name 1 new 3 where table_ name EMPLOYEE
You must have JavaScript enabled to view digital editions.
