Prompt for variable in SQL Script

person Jason Huangfolder_openCode, Oraclelocal_offer, , , access_time October 9, 2009

Simple tutorial on how to write a Oracle P-SQL script that asks user for a value in a prompt.

/*
-- this script will update MLP identity across all related tables in LIMS correctly --
-- when executing in SQL navigator:
-- make sure the following is toggled on:
-- updateable, scan defines, spool outputs, fetch all
-- the && marks a permenant variable,
-- whatever defined is passed to all the sql statements below to execute without prompting
-- to use this script, subsitute old_product, new_product variable value.
-- the query will rename old_product to new_product
-- Min --
 
TESTED on MIGPRJ using TOAD & SQL Navigator
*/
 
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
 
SET ECHO ON
SET PAGESIZE 0
 
spool Update MLP Identity.log
 
-- prompt for parameters
DEFINE old_product = ''
ACCEPT old_product PROMPT "Enter Old Product Name:"
DEFINE new_product = ''
ACCEPT new_product PROMPT "Enter New Product Name:"
 
-- sample --
UPDATE SAMPLE SET PRODUCT = '&&new_product' WHERE PRODUCT = '&&old_product'
/
 
-- MLP values
-- LEVEL relations
UPDATE MLP_VALUES SET PRODUCT_ID = '&&new_product' WHERE PRODUCT_ID = '&&old_product'
/
 
-- MLP Components --
UPDATE MLP_COMPONENTS SET PRODUCT_ID = '&&new_product' WHERE PRODUCT_ID = '&&old_product'
/
 
-- MLP ANALYSIS
UPDATE MLP_ANALYSIS SET PRODUCT_ID = '&&new_product' WHERE PRODUCT_ID = '&&old_product'
/
 
-- MLP HEADER
UPDATE MLP_HEADER SET IDENTITY = '&&new_product' WHERE IDENTITY = '&&old_product'
/
 
SET ECHO OFF
 
COMMIT;
 
spool off

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>