Re: Running plain SQL statements???

Hi

There are 2 ways to do this in SQL*Plus.

If you want to run an anonymous block in SQL*Plus without creating a stored procedure,
all you need is

BEGIN -- at the beginning of your script and

END;     -- at the end of the script 
.            -- The period signifies end of anonymous block 
/            -- The slash runs the anonymous block

If you want to create a stored procedure then

create or replace proc1 is
BEGIN
    Statements ....
END; Then in SQL*Plus type:

execute proc1

I hope I have not missed anything.

Good luck !!!

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Chris Drew <cdrew_at_maine.rr.com> wrote in article <iKFw2.195$6d6.701_at_newsr2.maine.rr.com>...
> Can this SQL statement be stored and run as a stored procedure? Can it
be
> done without re-writing using cursors, etc...?? All help is
appreciated..
>
> /* OLD - shortens certificate number length to 9 on non-bme business */
> /* UPDATE REPOSIT.LTR_EXTRACT_TEMP SET FIELD_13 = SUBSTR(FIELD_13,1,9) */
> /* WHERE FIELD_105 <> 'BME' */
> /* */
> /* COMMIT */
> /* */
> /* appends entire daily WinUCS letter extract to history table */
> INSERT INTO REPOSIT.LTR_EXTRACT_INFO
> SELECT * FROM REPOSIT.LTR_EXTRACT_TEMP
> /
> COMMIT
> /
> /* removes all records from table before next letter selection run */
> DELETE FROM REPOSIT.LTR_MAIL_TO_INFO
> /
> COMMIT
> /
> /* appends 'attending' mailing data records to table for certain letter
> types */
> INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
> MAIL_TO_TYPE,
> MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
> MAIL_TO_STATE,
> MAIL_TO_ZIP_CD )
> SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
> LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
> UPPER(FIELD_29), UPPER(FIELD_30), UPPER(FIELD_31), UPPER(FIELD_32),
> UPPER(FIELD_33), FIELD_34
> FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
> REPOSIT.LTR_LETTER_RECIPIENT_INFO
> WHERE RECIPIENT_CD = 1
> AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
> AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
> /
> /* appends 'facility' mailing data records to table for certain letter
types
> */
> INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
> MAIL_TO_TYPE,
> MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
> MAIL_TO_STATE,
> MAIL_TO_ZIP_CD )
> SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
> LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
> UPPER(FIELD_23), UPPER(FIELD_24), UPPER(FIELD_25), UPPER(FIELD_26),
> UPPER(FIELD_27), FIELD_28
> FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
> REPOSIT.LTR_LETTER_RECIPIENT_INFO
> WHERE RECIPIENT_CD = 2
> AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
> AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
> /
> /* OLD - appends 'insurer' mailing data records to table for certain
letter
> types and */
> /* where client code does not equal 'bme', this prevents printing letters
to
> BCBSME */
> /* INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
> MAIL_TO_TYPE, */
> /* MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
> MAIL_TO_STATE, */
> /* MAIL_TO_ZIP_CD ) */
> /* SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
> LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD, */
> /* UPPER(FIELD_200), UPPER(FIELD_213), UPPER(FIELD_214),
UPPER(FIELD_215),
> */
> /* UPPER(FIELD_216), FIELD_217 */
> /* FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE, */
> /* REPOSIT.LTR_LETTER_RECIPIENT_INFO */
> /* WHERE RECIPIENT_CD = 3 */
> /* AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR */
> /* AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
*/
> /* AND FIELD_105 != 'BME' */
> /* */
> /* appends 'patient' mailing data records to table for certain letter
types
> */
> INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
> MAIL_TO_TYPE,
> MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
> MAIL_TO_STATE,
> MAIL_TO_ZIP_CD )
> SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
> LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
> UPPER(FIELD_9||' '||FIELD_11), UPPER(FIELD_14), UPPER(FIELD_15),
> UPPER(FIELD_16), UPPER(FIELD_17), FIELD_18
> FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
> REPOSIT.LTR_LETTER_RECIPIENT_INFO
> WHERE RECIPIENT_CD = 4
> AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
> AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
> /
> /* OLD - appends 'patient' mailing data records to table when patient
name
> and address */
> /* data does not match subscriber's */
> /* INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
> MAIL_TO_TYPE, */
> /* MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
> MAIL_TO_STATE, */
> /* MAIL_TO_ZIP_CD ) */
> /* SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
> LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD, */
> /* UPPER(FIELD_9||' '||FIELD_11), UPPER(FIELD_14), UPPER(FIELD_15), */
> /* UPPER(FIELD_16), UPPER(FIELD_17), FIELD_18 */
> /* FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE, */
> /* REPOSIT.LTR_LETTER_RECIPIENT_INFO */
> /* WHERE RECIPIENT_CD = 5 */
> /* AND ((FIELD_9||FIELD_10||FIELD_11 != REPLACE(FIELD_126,'
')||FIELD_127)
> */
> /* AND (FIELD_14||FIELD_16||FIELD_17||FIELD_18 !=
> FIELD_129||FIELD_130||FIELD_131||FIELD_132)) */
> /* AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR */
> /* AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
*/
> /* */
> /* appends 'physician' mailing data records to table for certain letter
> types */
> INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
> MAIL_TO_TYPE,
> MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
> MAIL_TO_STATE,
> MAIL_TO_ZIP_CD )
> SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
> LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
> UPPER(FIELD_29), UPPER(FIELD_30), UPPER(FIELD_31), UPPER(FIELD_32),
> UPPER(FIELD_33), FIELD_34
> FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
> REPOSIT.LTR_LETTER_RECIPIENT_INFO
> WHERE RECIPIENT_CD = 6
> AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
> AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
> /
> /* appends 'subscriber' mailing data records to table for certain letter
> types */
> INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
> MAIL_TO_TYPE,
> MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_CITY, MAIL_TO_STATE,
> MAIL_TO_ZIP_CD )
> SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
> LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
> UPPER(FIELD_126)||' '||UPPER(FIELD_127), UPPER(FIELD_129),
> UPPER(FIELD_130),
> FIELD_131, FIELD_132
> FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
> REPOSIT.LTR_LETTER_RECIPIENT_INFO
> WHERE RECIPIENT_CD = 7
> AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
> AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
> /
> COMMIT
> /
> /* sets exception flag to 'n' where name, address, city, state or zip_cd
> data is missing */
> UPDATE REPOSIT.LTR_MAIL_TO_INFO
> SET MAIL_TO_EXCEPTION = 'M'
> WHERE EXTRACT_ROW_NB IN ( SELECT EXTRACT_ROW_NB
> FROM REPOSIT.LTR_MAIL_TO_INFO
> WHERE MAIL_TO_NAME IS NULL
> OR MAIL_TO_ADDRESS1 IS NULL
> OR MAIL_TO_CITY IS NULL
> OR MAIL_TO_STATE IS NULL
> OR MAIL_TO_ZIP_CD IS NULL)
> /
> COMMIT
> /
> /* sets exception flag to 'n' where plan code value is missing in
> ltr_logo_info table */
> UPDATE REPOSIT.LTR_MAIL_TO_INFO
> SET MAIL_TO_EXCEPTION = 'P'
> WHERE EXTRACT_ROW_NB IN ( SELECT EXTRACT_ROW_NB
> FROM ( SELECT EXTRACT_ROW_NB, PLAN_CD
> FROM REPOSIT.LTR_EXTRACT_TEMP, LTR_LOGO_INFO
> WHERE FIELD_189 = PLAN_CD (+)
> AND PLAN_CD IS NULL))
> /
> COMMIT
> /
>
>
>
>
Received on Wed Feb 17 1999 - 12:07:33 CST