CODEWITHSUNDEEP
stored-proceduresoracle11g
Angelina
Angelina

Reputation: 2265

How do I pass my variable value into my stored procedure

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;
    /

Upvotes: 1

Views: 75

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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;

Upvotes: 1

Related Questions