3 Replies Latest reply: Aug 4, 2010 5:25 AM by userHH0815 RSS

    run an oracle package

    789210
      Hi,

      the script below, i would like to use the select statement result so i can pass this into the oracle package. How would I do this. See shell script below :

      #!/bin/sh

      DB_INSTANCE="FXFG1"
      ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1"
      SCRIPTS_DIR="/home/oracle/fxfg_database/1.0/sql/schema"
      LOGS_DIR="/home/oracle/fxfg_database/1.0/sql/logs"

      sqlplus -s FXFG/FXFG@$DB_INSTANCE <<EOF
      set pages 0
      select to_char(sysdate-301,'WW') from dual;

      call PKG_FXFG_TCM_QUERY.DELETE_WEEKLY_DATA(the value from the select statement to be used within these brackets);
      exit
      EOF
        • 1. Re: run an oracle package
          Kamran Agayev A.
          user10874563 wrote:
          Hi,

          the script below, i would like to use the select statement result so i can pass this into the oracle package. How would I do this. See shell script below :

          #!/bin/sh

          DB_INSTANCE="FXFG1"
          ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1"
          SCRIPTS_DIR="/home/oracle/fxfg_database/1.0/sql/schema"
          LOGS_DIR="/home/oracle/fxfg_database/1.0/sql/logs"

          sqlplus -s FXFG/FXFG@$DB_INSTANCE <<EOF
          set pages 0
          select to_char(sysdate-301,'WW') from dual;

          call PKG_FXFG_TCM_QUERY.DELETE_WEEKLY_DATA(the value from the select statement to be used within these brackets);
          exit
          EOF
          Check my following article, I've shown it there:
          http://kamranagayev.wordpress.com/2009/02/23/using-oracle-utl_file-utl_smtp-packages-and-linux-shell-scripting-and-cron-utility-together-2/
          • 2. Re: run an oracle package
            Nicolas.Gasparotto
            Like that ?
            sqlplus -s FXFG/FXFG@$DB_INSTANCE <<EOF
            set pages 0
            declare
            myvar number;
            begin
            select to_char(sysdate-301,'WW') into myvar from dual;
            call PKG_FXFG_TCM_QUERY.DELETE_WEEKLY_DATA(myvar);
            end;
            exit
            EOF
            or much simpler :
            sqlplus -s FXFG/FXFG@$DB_INSTANCE <<EOF
            set pages 0
            call PKG_FXFG_TCM_QUERY.DELETE_WEEKLY_DATA(to_char(sysdate-301,'WW'));
            exit
            EOF
            Nicolas.
            • 3. Re: run an oracle package
              userHH0815
              Hi,

              you can use a variable like:

              #!/bin/sh

              DB_INSTANCE="FXFG1"
              ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1"
              SCRIPTS_DIR="/home/oracle/fxfg_database/1.0/sql/schema"
              LOGS_DIR="/home/oracle/fxfg_database/1.0/sql/logs"

              sqlplus -s FXFG/FXFG@$DB_INSTANCE <<EOF
              set pages 0
              column var_day new_value var_day
              select to_char(sysdate-301,'WW') var_day from dual;

              call PKG_FXFG_TCM_QUERY.DELETE_WEEKLY_DATA(&var_day);
              exit
              EOF

              Regards
              Heike

              Edited by: userHH0815 on 04.08.2010 12:24

              Edited by: userHH0815 on 04.08.2010 12:24