SQL*Engine - Part 1 - Initial Overview/Demonstration
SQL*Engine - Part 2 - Launching PeopleSoft Process Requests
SQL*Engine - Part 3 - Command Guide
SQL*Engine - Part 4 - @SET Variables
I frequently use SQL*Engine to resolve mundane tasks. For instance, when you create tables in PeopleSoft using Application Designer the DDL model may not include synonyms & grant permissions (This typically applies to test environments) or they may have been dropped for some reason. This Blog Entry shows a quick method of creating synonyms (if needed) & assigning the default grants required so I can proceed with testing.
*****************************************************************
* *
* MODULE: XXSQI_SG_DEPEXP.SQX *
* AUTHOR: TONY DELIA. *
* DATE: 07/31/2016. *
* DESC: SQL*ENGINE SCRIPT - CREATE GRANTS/SYNONYMS. *
* *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
* PROGRAM VARIABLES *
*****************************************************************
@HDR_REF=DEPEXP_Testing
@HDR_DESCR=Assign Test Environment Synonyms/Grants
@SET=&&DBNAME=@DBNAME
@SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound
*****************************************************************
* Set OBJECT (TABLE NAME) *
*****************************************************************
@SET=&&OBJECT=PS_XX_DEPEXP_DATA
@SCRIPT=&&LIB/XXSQL_SG.LIB
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
This SQL*Engine script (XXSQI_SG_DEPEXP.SQX) will utilize a library routine XXSQL_SG.LIB I have set up. All the primary script needs to do is populate the &&OBJECT variable with the table name (in this case PS_XX_DEPEXP_DATA) & pass control to the library script via @SCRIPT=&&LIB/XXSQL_SG.LIB (in lieu of a hard-coded path, &&LIB must point to the proper location relative to the database).
*****************************************************************
* *
* MODULE: XXSQL_SG.LIB *
* AUTHOR: TONY DELIA. *
* DATE: 02/13/2004. *
* DESC: SQL*ENGINE SCRIPT - CREATE GRANTS/SYNONYMS. *
* *
*****************************************************************
* *
* Required Substitution Variables *
* -------------------------------------- *
* &&OBJECT = Table Object *
* *
*****************************************************************
*****************************************************************
* TEST IF SYNONYM EXISTS *
*****************************************************************
@STEP=COUNT_SYNONYM_&&OBJECT
@COUNT=ALL_SYNONYMS
WHERE OWNER = 'PUBLIC'
AND SYNONYM_NAME = '&&OBJECT'
/
*****************************************************************
* CREATE SYNONYM IF NOT EXISTS *
*****************************************************************
@STEP=SYNONYM_&&OBJECT
@COND=ZERO.COUNT_SYNONYM_&&OBJECT
CREATE PUBLIC SYNONYM &&OBJECT
FOR SYSADM.&&OBJECT
/
*****************************************************************
* GRANT - PSSELECT *
*****************************************************************
@STEP=GRANT_PSSELECT_&&OBJECT
@COND=NULL
GRANT SELECT
ON SYSADM.&&OBJECT
TO PSSELECT
/
*****************************************************************
* GRANT - PSUPDATE *
*****************************************************************
@STEP=GRANT_PSUPDATE_&&OBJECT
@COND=NULL
GRANT INSERT, UPDATE
ON SYSADM.&&OBJECT
TO PSUPDATE
/
*****************************************************************
* GRANT - PSDELETE *
*****************************************************************
@STEP=GRANT_PSDELETE_&&OBJECT
@COND=NULL
GRANT DELETE
ON SYSADM.&&OBJECT
TO PSDELETE
/
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
XXSQL_SG.LIB determines if the synonym exists & using the @COND= directive performs the CREATE SYNONYM if needed. Next three GRANT steps are executed for SELECT, INSERT & UPDATE and DELETE. There are 5 Steps within the library script with each assigned a unique step name based on the &&OBJECT parameter.
*****************************************************************
* Set OBJECT (TABLE NAME) *
*****************************************************************
@SET=&&OBJECT=PS_DISTRIB_LINE
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_PYMNT_VCHR_XREF
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_FRGH_CHRG
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_LINE_WTHD
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_LN_TX_DTL
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_REG_LC
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_SALETX_CHG
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_USETAX_CHG
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_VNDR_BANK
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_VNDR_INFO
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCH_DUP_INV_REG
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VENDOR_WTHD_JUR
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VOUCHER
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VOUCHER_LINE
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_PC_VCHR_PEND
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_PN_VCHR_XREF
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_VCHR_ACH
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_VNDR_CAR_DEB
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHRLN_MISC_CHG
@SCRIPT=&&LIB/XXSQL_SG.LIB
*****************************************************************
Add as many tables as needed by repetitively assigning them to the &&OBJECT variable & calling the script. SQL*Engine allows me to resolve this situation in minutes (writing or reusing a primary script and executing it). No technical administrative bureaucracy & forms. Just set the &&OBJECT variable & let it fly...
