This discussion is archived
1 2 Previous Next 16 Replies Latest reply: May 2, 2013 6:54 AM by Kiran Go to original post RSS
  • 15. Re: pass variable from apex to shell script
    scott.wesley Guru
    Currently Being Moderated
    Time to start correcting syntax issues, did you carry across the typo in "argement"
  • 16. Re: pass variable from apex to shell script
    Kiran Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points