Forum Stats

  • 3,757,497 Users
  • 2,251,236 Discussions
  • 7,869,845 Comments

Discussions

Getting error while calling procedure

Albert Chao
Albert Chao Member Posts: 60 Green Ribbon
 create table jira_upload(id_num number,filename varchar2(50));

insert into jira_upload values(1,'test');

create or replace procedure sp_jira(
id_req IN jira_upload.id_num%type,
err_msg OUT varchar2)
as
lv_count number;
begin

select id_num into lv_count from jira_upload where id_num = id_req;

if lv_count is null then
err_msg := 'ID missing';
else
err_msg :='Success';
end if;
end;

set serveroutput on;
exec sp_jira(id_req=> 1);

I am getting below error while calling a procedure

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'SP_JIRA'

 
Tagged:

Best Answers

  • mathguy
    mathguy Member Posts: 10,069 Blue Diamond
    Accepted Answer


    This is the third version of your question.

    First you didn't know why you got that error. (Passing wrong number of arguments.)

    Then you understood that, but didn't know how to pass an OUT argument to the procedure.

    Now you understand that too, but the procedure - while correct in itself - doesn't do what you want it to do (which is to throw an error when the ID is not found).

    Regardless of the answer to your latest question (which is trivial: when not found, the count returns 0, not NULL - so you should test if the count returned by the query is 0 rather than NULL ), do you understand now why it is so important to tell us **what problem you are trying to solve**, rather than just show us code that does not do what you want it to do?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond
    Accepted Answer

    Hi, @Albert Chao

    I want to check if id_num is not present then it should give me error like ID not present or ID missing

    One way is to change

    select id_num into lv_count from jira_upload where id_num = id_req;
    
    if lv_count is null then
    err_msg := 'ID missing';
    

    to

      SELECT COUNT (*)
      INTO   lv_count
      FROM   jira_upload
      WHERE  id_num = id_req;
    
      IF lv_count = 0 THEN
        err_msg := 'ID missing';
    


Answers

  • mathguy
    mathguy Member Posts: 10,069 Blue Diamond

    Your procedure has two parameters. You call it with just one argument. Even Oracle can count to one and to two, and see that the results are different. The error message begins with "wrong number (...) of arguments".

    This one should have been easy. (Note that I didn't read past your procedure declaration to see what it does and how it does it. If you fix this mistake, there is no guarantee there will be no others.)

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,724 Black Diamond
    edited Sep 23, 2021 2:13PM

    Your procedure has two parameters while you are passing only one. Second parameter is OUT so you can't omit it. In fact, you wouldn't be able to omit it even if it would be IN parameter unless you define it with default value. Also, your procedure would raise no_data_found if passed non-existing id_num. You should use:

    select count(*) into lv_count from jira_upload where id_num = id_req and rownum = 1;
    if lv_count = 0 then
    err_msg := 'ID missing';
    else
    err_msg :='Success';
    end if;
    

    SY.

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @Solomon Yakobson But the second parameter is OUT so how will I call the procedure with the OUT parameter?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,724 Black Diamond

    You declare it in calling code:

    set serveroutput on;
    declare
        v_err_msg varchar2(100);
    begin
        sp_jira(1,v_err_msg);
        dbms_output.put_line(v_err_msg);
    end;
    /
    Success
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    SY.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    Hi, @Albert Chao

    But the second parameter is OUT so how will I call the procedure with the OUT parameter?

    Inside the parentheses (where the arguments go) add a comma and the second argument. Using EXEC, you can do it this way:

    VARIABLE e  VARCHAR2 (100);
    
    EXEC sp_jira (id_req => 1, err_msg => :e);
    

    Using an anonymous block, you could do it this way:

    DECLARE
      em  VARCHAR2 (100);
    BEGIN
      sp_jira ((id_req => 1, err_msg => em);
    END;
    /
    

    Calling sp_jira from another procedure is very much like using an anoymous block.

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @Solomon Yakobson When I am giving 2 then also it is returning as success. I want to check if id_num is not present then it should give me error like ID not present or ID missing

  • mathguy
    mathguy Member Posts: 10,069 Blue Diamond
    Accepted Answer


    This is the third version of your question.

    First you didn't know why you got that error. (Passing wrong number of arguments.)

    Then you understood that, but didn't know how to pass an OUT argument to the procedure.

    Now you understand that too, but the procedure - while correct in itself - doesn't do what you want it to do (which is to throw an error when the ID is not found).

    Regardless of the answer to your latest question (which is trivial: when not found, the count returns 0, not NULL - so you should test if the count returned by the query is 0 rather than NULL ), do you understand now why it is so important to tell us **what problem you are trying to solve**, rather than just show us code that does not do what you want it to do?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond
    Accepted Answer

    Hi, @Albert Chao

    I want to check if id_num is not present then it should give me error like ID not present or ID missing

    One way is to change

    select id_num into lv_count from jira_upload where id_num = id_req;
    
    if lv_count is null then
    err_msg := 'ID missing';
    

    to

      SELECT COUNT (*)
      INTO   lv_count
      FROM   jira_upload
      WHERE  id_num = id_req;
    
      IF lv_count = 0 THEN
        err_msg := 'ID missing';