1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL script calling a stored procedure with a dynamic parameter

Discussion in 'SQL PL/SQL' started by bdpaulick, May 8, 2013.

  1. bdpaulick

    bdpaulick Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I am kind of new to Oracle and SQL but I am trying to execute a FOR LOOP to loop through a selected number of values used to call the same stroed procedure with the particular value. The code I am trying to execute is below and I am trying to execute it through a script file via sqlplus in Oracle 11g. however when this code is executed I get a

    BEGIN
    FOR R IN (SELECT NAME from ML_SCRIPT_VERSION) LOOP
    EXECUTE IMMEDIATE 'call ml_add_missing_dnld_scripts('''||R.Name||''');';
    END LOOP;
    END;
    /
    END;

    However I get the 'ORA-00911: invalid character' error when I execute it. If I run it individually:
    exec ml_add_missing_dnld_scripts('getAccount'); Results -> ml_add_missing_upld_scripts 'GETACCOUNT') succeeded.
    or
    call ml_add_missing_dnld_scripts('getAccount'); Results -> anonymous block completed

    with one of the returned values it processes with no problem, it is only when I try to run it through with this code in the LOOP.

    What do I need to do to correct this?


    thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're going about this the wrong way, and I understand why you went in the direction you did, however wrong it may be. When you want to execute a procedure from within a PL/SQL block you simply use the procedure name with the required parameters, as shown below:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE ml_add_missing_dnld_scripts(p_name IN varchar2)
      2  IS
      3  BEGIN
      4          dbms_output.put_line(p_name||' yodels');
      5  END;
      6  /
     
    PROCEDURE created.
     
    SQL>
    SQL> CREATE TABLE ml_script_version(
      2          name    varchar2(40),
      3          stuff   varchar2(40),
      4          id      NUMBER,
      5          dt      DATE
      6  );
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO ml_script_version
      4                  VALUES('Bombo'||i, 'Blorp yazmeenee', i, sysdate+i);
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> BEGIN
      2  FOR R IN (SELECT NAME FROM ML_SCRIPT_VERSION) LOOP
      3  EXECUTE IMMEDIATE 'call ml_add_missing_dnld_scripts('''||R.Name||''');';
      4  END LOOP;
      5  END;
      6  /
    BEGIN
    *
    ERROR at line 1:
    ORA-00911: invalid CHARACTER
    ORA-06512: at line 3

    SQL>
    SQL> BEGIN
      2  FOR R IN (SELECT NAME FROM ML_SCRIPT_VERSION) LOOP
      3       ml_add_missing_dnld_scripts(R.Name);
      4  END LOOP;
      5  END;
      6  /

    Bombo1 yodels
    Bombo2 yodels
    Bombo3 yodels
    Bombo4 yodels
    Bombo5 yodels
    Bombo6 yodels
    Bombo7 yodels
    Bombo8 yodels
    Bombo9 yodels
    Bombo10 yodels
    Bombo11 yodels
    Bombo12 yodels
    Bombo13 yodels
    Bombo14 yodels
    Bombo15 yodels
    Bombo16 yodels
    Bombo17 yodels
    Bombo18 yodels
    Bombo19 yodels
    Bombo20 yodels
    Bombo21 yodels
    Bombo22 yodels
    Bombo23 yodels
    Bombo24 yodels
    Bombo25 yodels
    Bombo26 yodels
    Bombo27 yodels
    Bombo28 yodels
    Bombo29 yodels
    Bombo30 yodels
    Bombo31 yodels
    Bombo32 yodels
    Bombo33 yodels
    Bombo34 yodels
    Bombo35 yodels
    Bombo36 yodels
    Bombo37 yodels
    Bombo38 yodels
    Bombo39 yodels
    Bombo40 yodels
    Bombo41 yodels
    Bombo42 yodels
    Bombo43 yodels
    Bombo44 yodels
    Bombo45 yodels
    Bombo46 yodels
    Bombo47 yodels
    Bombo48 yodels
    Bombo49 yodels
    Bombo50 yodels
    Bombo51 yodels
    Bombo52 yodels
    Bombo53 yodels
    Bombo54 yodels
    Bombo55 yodels
    Bombo56 yodels
    Bombo57 yodels
    Bombo58 yodels
    Bombo59 yodels
    Bombo60 yodels
    Bombo61 yodels
    Bombo62 yodels
    Bombo63 yodels
    Bombo64 yodels
    Bombo65 yodels
    Bombo66 yodels
    Bombo67 yodels
    Bombo68 yodels
    Bombo69 yodels
    Bombo70 yodels
    Bombo71 yodels
    Bombo72 yodels
    Bombo73 yodels
    Bombo74 yodels
    Bombo75 yodels
    Bombo76 yodels
    Bombo77 yodels
    Bombo78 yodels
    Bombo79 yodels
    Bombo80 yodels
    Bombo81 yodels
    Bombo82 yodels
    Bombo83 yodels
    Bombo84 yodels
    Bombo85 yodels
    Bombo86 yodels
    Bombo87 yodels
    Bombo88 yodels
    Bombo89 yodels
    Bombo90 yodels
    Bombo91 yodels
    Bombo92 yodels
    Bombo93 yodels
    Bombo94 yodels
    Bombo95 yodels
    Bombo96 yodels
    Bombo97 yodels
    Bombo98 yodels
    Bombo99 yodels
    Bombo100 yodels
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
    Notice the difference between your code and the PL/SQL block that works. No EXECUTE IMMEDIATE (that's intended to execute dynamic SQL statements, not call procedures) and no long list of single quotation marks.

    PL/SQL isn't the easiest language to grasp at first; I remember doing similar things and wondering why they didn't work. Unfortunately back then I did not have a forum to visit to ask such questions.
     
  3. bdpaulick

    bdpaulick Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thank you I finally have it working with your help.