How do I pass my variable value into my stored procedure

Question

I created a variable className and I assigned values to it. I have another procedure in oracle that sends emails to me. How do I pass this value into header and body of my email?

VARIABLE className varchar2(30)
    :classname := 0;
    BEGIN
     FOR i IN 
     (
        SELECT CLASS_INSTANCE_COUNT , CLASS_NAME
        FROM MODEL_CLASS_COUNTS 
        WHERE TRUNC(COUNT_DATETIME) = TRUNC(SYSDATE)
     )
        LOOP    
          IF i.CLASS_INSTANCE_COUNT = 0 
          THEN 
              :className := i.CLASS_NAME;
              EMAIL('[email protected]',  'email header: &className is 0', 'body: count for &className is 0');
          END IF;
        END LOOP;
    END;
    /

I'm not sure I understand what you want to happen. You've got a SQLPlus variable :classname and a SQLPlus substitution variable &className. Is there a reason that you're using either rather than using local PL/SQL variables in your anonymous PL/SQL block (assuming you even want to bother with a local variable)? Do you want SQL*Plus to prompt the user for a value (in which case you'd want the substitution variable)? Or do you just want the value to come from the loop? — Justin Cave
– Justin Cave, Commented Dec 17, 2013 at 17:47

Justin Cave · Accepted Answer · 2013-12-17 17:49:32Z

1

My guess is that you don't want to have either a SQL*Plus variable or a substitution variable. I'm guessing that you just want

BEGIN
 FOR i IN 
   (
      SELECT CLASS_INSTANCE_COUNT , CLASS_NAME
        FROM MODEL_CLASS_COUNTS 
       WHERE TRUNC(COUNT_DATETIME) = TRUNC(SYSDATE)
   )
 LOOP    
   IF i.CLASS_INSTANCE_COUNT = 0 
   THEN 
     EMAIL('[email protected]',  
           'email header: ' || i.class_name || ' is 0', 
           'body: count for ' || i.class_name || ' is 0');
   END IF;
 END LOOP;
END;

answered Dec 17, 2013 at 17:49

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Angelina Over a year ago

yes. I just came back from a walk and realized that I could do this :) This is exactly what I need. Thank you @Justin cave

Collectives™ on Stack Overflow

How do I pass my variable value into my stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related