Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Help changing script to accept multiple parameters

    I have been working on the following script that will allow my user to delete some records based on whether or not a location has been approved for deletion by management. I now need to change this script so that I can either run it for a single location (which is already does), or ALL "approved" locations at the same time. There could possibly be 50 individual approved locations and I don't want the user to have to enter each one individually. Can anyone give me some pointers on how I could alter this script to run for ALL, but still retain the option to run individually?

    Code:
    set serveroutput on size 1000000
    set echo on;
    set time on
    set autotrace on
    
    WHENEVER SQLERROR CONTINUE NONE;
    
    WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
    
    Alter session enable parallel dml;
    Alter session enable parallel query;
    
    declare 
            v_i     int := 0; 
            v_b     int := 0; 
            v_c     number; 
            v_count number;
    begin 
    dbms_output.enable(1000000); 
    
    SELECT DECODE(a.process_flag,'Y',1,NULL,0,DECODE(a.ok_to_process,'N',1,0))
      INTO v_count
      FROM PAC.DELETEME_CONTROL a,
           (
            SELECT '&1' dealer 
            FROM   dual
           ) b
      WHERE a.dealer(+) = b.dealer;
    
    IF v_count > 0 THEN
    
       DBMS_OUTPUT.PUT_LINE ('Location '||'&1'||' has not been approved for deletion. Now exiting');
    	 
    ELSE
    
       DBMS_OUTPUT.PUT_LINE ('Location '||'&1'||' is OK for deletion');	 
       DBMS_OUTPUT.PUT_LINE ('Starting deleting DELETEME DFUs');	 
       
          < This is where I do all the deletes>
    
    end if;
    	   
    end;
    /
    
    exit;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >There could possibly be 50 individual approved locations and I don't want the user to have to enter each one individually.
    So how is the code supposed to "know" which are the "approved locations"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Where the OK_TO_PROCESS field on DELETEME_CONTROL = 'Y' and PROCESS_FLAG = 'N'

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    let me rephrase that. If the location does not exist on that table, it's approved. If it does exist, then the criteria I mentioned applies. That's what I have working in the first part of the script.

  5. #5
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I would create an SQL generating outer procedure...

    control.sql:
    PHP Code:
    SET ECHO OFF;
    SET HEADING OFF;
    SET FEEDBACK OFF;

    SPOOL run_me.sql

    SELECT 
    '@@your_script.sql ' || dealer || ';'
      
    FROM PAC.DELETEME_CONTROL
      WHERE 
    <condition_to_determine_OK_to_delete>;

    SPOOL OFF;
    @
    run_me.sql 
    What you will get is a sql file (run_me.sql) that has a bunch of calls to your script (ingeniously named your_script.sql) that will look something like this:

    run_me.sql:
    PHP Code:
    @@your_script COMPANY1;
    @@
    your_script COMPANY4;
    @@
    your_script COMPANY23
    Then, after this file is created, the control.sql file will execute @run_me.sql... for testing purposes you might want to comment it out to make sure it runs correctly.

    Hope this helps.
    JoeB
    save disk space, use smaller fonts

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    After doing some more head scratching, I think I don't need the functionality of being able to run it for individual locations. That is, the script should run for all approved locations. Would I still need to generate the outer procedure?

  7. #7
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    yes... so however you determine which locations are approved and which are approved for deletion, that is what you'll have to put in the "WHERE" clause in my example.
    JoeB
    save disk space, use smaller fonts

  8. #8
    Join Date
    Sep 2005
    Posts
    220
    Please correct me if I'm wrong here, but doesn't the first part of my current script make the decision on whether a location gets processed or not? The where clause in your example would not run for a location that does not exists in this table but that is one of the requirements.

  9. #9
    Join Date
    Sep 2005
    Posts
    220
    would it not be possible to put a loop withing my script and spin through all approved locations AND all locations that do not exist on this table?
    Last edited by ssmith001; 05-24-07 at 16:07.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    So you can't just do this all with one SQL stmt:

    Code:
    DELETE 
    FROM  some_other_table sot
    WHERE sot.dealer in (SELECT a.dealer 
                         FROM PAC.DELETEME_CONTROL a
                         WHERE DECODE(a.process_flag,'Y',1,NULL,0,DECODE(a.ok_to_process,'N',1,0)) = 0
       OR NOT EXISTS (SELECT *
                      FROM PAC.DELETEME_CONTROL b
                      WHERE b.dealer = sot.dealer);
    ---=cf

  11. #11
    Join Date
    Sep 2005
    Posts
    220
    I had tried that initially but found that it made the entire script run a very, very long time. That's why I was trying to loop through and only process the delete steps if the location is approved, or the location does not exist on the table.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You say it takes a long time. Do you have the process_flag and dealer columns indexed in the deleteme_control table? If you don't, the multiple full table scans for each row in some_other_table would take a long time.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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