Getting error while calling procedure

Albert Chao

 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'

mathguy

https://community.oracle.com/tech/developers/discussion/comment/16810083#Comment_16810083

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

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

mathguy

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

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

https://community.oracle.com/tech/developers/discussion/comment/16810051#Comment_16810051

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

Solomon Yakobson

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

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

@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

https://community.oracle.com/tech/developers/discussion/comment/16810083#Comment_16810083

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

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