CODEWITHSUNDEEP
oracle-databaseplsql
Pravin
Pravin

Reputation: 697

Procedures in PLSQL using ORACLE DB

CREATE OR REPLACE PROCEDURE ABC ( REG_NO   IN  CO_ENROLMENT.S_REGNO%TYPE,
                                  TERM     IN  COURSEOFFERING.CO_TERMNUMBER%TYPE,
                                  YEAR     IN  COURSEOFFERING.CO_YEAR%TYPE,
                                  CO_TITLE IN  COURSE.C_TITLE%TYPE,
                                  EN_DATE  OUT CO_ENROLMENT.COE_ENROLDATE%TYPE,
                                  COM_ST   OUT CO_ENROLMENT.COE_COMPLETIONSTATUS%TYPE)

AS 
BEGIN
   SELECT M.COE_COMPLETIONSTATUS, M.COE_ENROLDATE 
     INTO COM_ST, EN_DATE
     FROM COURSEOFFERING O
    INNER JOIN COURSE C
            ON C.C_ID = O.C_ID
    INNER JOIN CO_ENROLMENT M
            ON M.CO_ID = O.CO_ID
    WHERE M.S_REGNO LIKE REG_NO AND
          O.CO_TERMNUMBER LIKE TERM AND
          O.CO_YEAR LIKE YEAR AND
          C.C_TITLE LIKE CO_TITLE;
END ABC;

I have written down above procedure. Below is PL/SQL block to call above procedure,

DECLARE
  COMPL_STATUS   CO_ENROLMENT.COE_COMPLETIONSTATUS%TYPE;
  ENROL_DATE     CO_ENROLMENT.COE_ENROLDATE%TYPE;
BEGIN
  ABC (44444444, 2009, 2, 'PLSQL Programming', 
       EN_DATE => enrol_date, COM_ST =>compl_status);
  DBMS_OUTPUT.PUT_LINE ('STUDENT COMPLETION STATUS AND ENROLMENT DATE IS ' 
                         || ENROL_DATE
                         || '    ' 
                         || compl_status );
END;

It returns me error as no data found, but when I run query separately I get the output. I couldn't figure out what is wrong. Did I wrote procedure block correctly and Parameters passed in PL/SQL block are correct?

Upvotes: 0

Views: 1289

Answers (4)

Colin 't Hart
Colin 't Hart

Reputation: 7729

You're calling your stored procedure incorrectly. You have to specify parameters in the correct order, or use named parameter notation, ie

ABC (44444444, 2, 2009, 'PLSQL Programming', enrol_date, compl_status);

or

ABC (REG_NO => 44444444, YEAR => 2009, TERM => 2,
   CO_TITLE => 'PLSQL Programming', 
   EN_DATE => enrol_date, COM_ST =>compl_status);

You can see that if you use named parameter notation you can specify parameters in a different order (or not at all if the parameters have DEFAULT values).

Upvotes: 0

Kirill Leontev
Kirill Leontev

Reputation: 10931

You probably lack wildcards in your procedure query.

16:02:06 SYSTEM@dwh-prod> select * from dual where 'abc' like 'ab'; 

no rows selected                                                    


16:02:18 SYSTEM@dwh-prod> select * from dual where 'abc' like 'ab%';

D                                                                   
-                                                                   
X

Without them, C.C_TITLE LIKE CO_TITLE is equal to C.C_TITLE = CO_TITLE, which is not neccessarily what you want.

Try C.C_TITLE LIKE '%'||CO_TITLE||'%' etc.

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

When select statement in your ABC procedure returns no rows the NO_DATA_FOUND exception will be raised and execution of your stored-procedure will be halted. To avoid such behavior you need to add EXCEPTION section in the stored procedure to catch the exception and react appropriately. To that end the execution section of your stored procedure might look:

BEGIN
   SELECT M.COE_COMPLETIONSTATUS, M.COE_ENROLDATE 
     INTO COM_ST, EN_DATE
     FROM COURSEOFFERING O
    INNER JOIN COURSE C
            ON C.C_ID = O.C_ID
    INNER JOIN CO_ENROLMENT M
            ON M.CO_ID = O.CO_ID
    WHERE M.S_REGNO LIKE REG_NO AND
          O.CO_TERMNUMBER LIKE TERM AND
          O.CO_YEAR LIKE YEAR AND
          C.C_TITLE LIKE CO_TITLE;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN DBMS_OUTPUT.PUT_LINE('No data found') -- for example
END ABC

Or if you want to proceed even if select statement raises exception you might enclose your select statement with nested BEGIN .. END block. 

BEGIN
   -- some code before
   BEGIN    
     SELECT M.COE_COMPLETIONSTATUS, M.COE_ENROLDATE 
       INTO COM_ST, EN_DATE
       FROM COURSEOFFERING O
      INNER JOIN COURSE C
              ON C.C_ID = O.C_ID
      INNER JOIN CO_ENROLMENT M
              ON M.CO_ID = O.CO_ID
      WHERE M.S_REGNO LIKE REG_NO AND
            O.CO_TERMNUMBER LIKE TERM AND
            O.CO_YEAR LIKE YEAR AND
            C.C_TITLE LIKE CO_TITLE;
   EXCEPTION
     WHEN NO_DATA_FOUND
     THEN DBMS_OUTPUT.PUT_LINE('No data found') -- for example
  END;
  -- some code after
END ABC

Upvotes: 1

elrado
elrado

Reputation: 5272

In proc you have year on third place buth you call it on second place. And how are types (COURSEOFFERING.CO_TERMNUMBER%TYPE, COURSEOFFERING.CO_TERMNUMBER%TYPE) declared?

Upvotes: 1

Related Questions