1 2 Previous Next 16 Replies Latest reply: May 2, 2013 8:54 AM by Kiran Pawar Go to original post RSS
      • 15. Re: pass variable from apex to shell script
        swesley_perth
        Time to start correcting syntax issues, did you carry across the typo in "argement"
        • 16. Re: pass variable from apex to shell script
          Kiran Pawar
          Hi Lion,

          >
          user10751206 wrote:

          I was asked not to write the variable to any table.
          Is there a way to pass the variable to oracle scheduler as argument?
          The following code does not work, and no idea how to correct it.

          DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
          (
          job_name => 'TEST_SHELL',
          argement_posistion => 1,
          argument_value => 'select cs_store_id from cs_stores where cs_store_id=:P20_STORE_ID'
          );
          >

          <ul><li>
          Have you checked the DBMS_SCHEDULER documentation for the [url http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#i1011390]SET_JOB_ARGUMENT_VALUE procedure and it's parameters?
          </li><li>
          There is typo error in the above call: argement_posistion should be argument_position.
          </li><li>
          The parameter argument_value expects a VARCHAR2 value which you have set to select cs_store_id from cs_stores where cs_store_id=:P20_STORE_ID
          <ul><li>
          What do you want to pass to argument_value ?
          </li><li>
          Do you want to pass value stored in P20_STORE_ID ?
          If Yes then straight-away you can write following PL/SQL block:
          BEGIN
          
            -- set the shell script argument-1
            DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
                job_name           => 'TEST_SHELL',
                argument_position  => 1,
                argument_value     => :P20_STORE_ID );
          
          END;
          For this you have to be sure that appropriate value is set for P20_STORE_ID in the session state.
          You can use [url http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/concept_ses.htm#BEIIEEDF]Session link on your Developer Tool bar.
          </li><li>
          Do you want to pass the value that is result of the SQL query?
          If Yes then you can write following PL/SQL block:
          DECLARE
          
            L_STORE_ID CS_STORES.CS_STORE_ID%TYPE;
          
          BEGIN
          
            SELECT CS_STORE_ID
              INTO L_STORE_ID
              FROM CS_STORES
             WHERE CS_STORE_ID = :P20_STORE_ID;
          
            -- set the shell script argument-1
            DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
                job_name           => 'TEST_SHELL',
                argument_position  => 1,
                argument_value     => L_STORE_ID );
          
          END;
          Two things: First - You have to handle the NO_DATA_FOUND exception while using INTO clause.
          Second - Your query
          SELECT CS_STORE_ID
            FROM CS_STORES
           WHERE CS_STORE_ID = :P20_STORE_ID;
          looks redundant as it returns the same value that is stored into the item P20_STORE_ID. So, the option provided of straight-away passing the value of P20_STORE_ID will suffice.
          </li></ul>
          </li></ul>

          In order to get accurate/appropriate response you should include at least following information with your question:
          <ul>
          <li>Full APEX version</li>
          <li>Full Database version</li>
          <li>APEX Web server architecture (EPG, OHS or APEX listener)</li>
          <li>Browser(s) and version(s) used</li>
          <li>Operating system with version, architecture(32/64)</li>
          </ul>
          The above information will help the forum members to ascertain the problem context and post most relevant response.

          Please read the [url https://wikis.oracle.com/display/Forums/Forums+FAQ]FAQ and [url https://forums.oracle.com/forums/ann.jspa?annID=1324]forum instructions for more information on using OTN forums effectively.

          Please update your forum profile with a real handle instead of user10751206 as the OTN Forums are quite friendly places and people like to know whom they're talking to!

          Hope it helps!
          Regards,
          Kiran
          1 2 Previous Next