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

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

THis is the procdure that needs to be execute on DB using .sh file

DECLARE
TYPE tbl_col_type is TABLE of VARCHAR2(50);
L_table_col tbl_col_type := tbl_col_type('X_ACC_REV_MNTH_AGG');

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

LKBrwn_DBA · #2 (**permalink**) 01-25-11, 16:07

Something like this:

PHP Code:


			
#!/bin/ksh

#

export ORACLE_SID=orcl

ORACLE_ENV_ASK=NO

. /usr/local/bin/oraenv



sqlplus -s {uid}/{pw} <<EOSQL

set echo on

DECLARE

TYPE tbl_col_type is TABLE of VARCHAR2(50);

L_table_col tbl_col_type := tbl_col_type('X_ACC_REV_MNTH_AGG');

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;

/

exit

EOSQL

keviavuz78 · #3 (**permalink**) 03-24-11, 03:24

Awesome post!
I am waiting for your future thoughts. Thanks for sharing.

Thanks for sharing here!

bookkeeping services

Internet Services

Web Development

Digital Marketing

Consumer Tech