Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Red face Unanswered: How to execute a ananomyous pl/sql block using shell script

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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
    Last edited by shammat; 01-20-11 at 19:13.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •