How to execute a ananomyous pl/sql block using shell script

MIKELALA · #1 (**permalink**) 01-20-11, 15:34

Hello Friends ,

I have a pl/sql block that should be executed by autosys job scheduler .

For the scheduler to execute , the pl/sql block needs to be written as .sh script.

The following is the pl/sql block . Can any one let me know how to create the same in .sh file.

=========================

DECLARE
TYPE tbl_col_type is TABLE of VARCHAR2(50);
L_table_col tbl_col_type := tbl_col_type('X_REV_M02_RSTM');
l_sql_stmt VARCHAR2(32000);
BEGIN
FOR rec IN L_table_col.FIRST..L_table_col.LAST
LOOP
l_sql_stmt := 'UPDATE '||'KAW_OWNER.'||L_table_col(rec)||
' SET X_REVENUE_TYPE_CD = REPLACE(X_REVENUE_TYPE_CD, ''-'', ''_'')'||
' WHERE INSTR(X_REVENUE_TYPE_CD, ''-'') > 0' ;
EXECUTE IMMEDIATE l_sql_stmt ;
END LOOP;
END;
====================
The autosys job scheduler runs the .sh file

thanks/mike

shammat · #2 (**permalink**) 01-20-11, 15:52

Put the pl/sql into a SQL file, then call sqlplus from within your shell script passing the SQL file as an argument. More details are in the SQL*Plus manual

spacebar · #3 (**permalink**) 01-20-11, 17:17

This is just a simple shell script(run_update_sql.sh) example you can try/modify:

Code:

#!/bin/ksh
# run_update_sql.sh

ORACLE_ACCESS=user/pass\@host
LOG_FILE_PATH=$HOME/logs
LOG_FILE=/run_update_sql.log
SCHEMA="SCHEMA_NAME"

###########################################################################################
#    Main Shell Processing
###########################################################################################

# Run SQL
SQL=`sqlplus -S <<RUN_UPDATE_SQL 2>>${LOG_FILE_PATH}${LOG_FILE}
$ORACLE_ACCESS
set pagesize 0 termout off
whenever sqlerror exit 1
  declare
    type tbl_col_type is table of varchar2(   50 );
    l_table_col tbl_col_type := tbl_col_type('X_REV_M02_RSTM');
    l_sql_stmt                    varchar2( 2000 );
  begin
    for rec in l_table_col.first..l_table_col.last
    loop
      l_sql_stmt := 'update '||'kaw_owner.'||l_table_col(rec)||
                    ' set x_revenue_type_cd = replace(x_revenue_type_cd, ''-'', ''_'')'||
                    ' where instr(x_revenue_type_cd, ''-'') > 0';
      execute immediate l_sql_stmt;
    end loop;
  end;
exit 0
RUN_UPDATE_SQL`
if [[ $? = 1 ]] then
  print `date "+%Y-%m-%d-%H.%M.%S"` "**Error running update sql. review above messages." >> ${LOG_FILE_PATH}${LOG_FILE}
fi

Internet Services

Web Development

Digital Marketing

Consumer Tech