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

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

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Something like this:

    PHP Code:
    #!/bin/ksh
    #
    export ORACLE_SID=orcl
    ORACLE_ENV_ASK
    =NO
    . /usr/local/bin/oraenv

    sqlplus 
    -{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 
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2011
    Posts
    1
    Awesome post!
    I am waiting for your future thoughts. Thanks for sharing.

    Thanks for sharing here!



    bookkeeping services

Posting Permissions

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