CODEWITHSUNDEEP
oracle-databaseplsql
LoveMeow
LoveMeow

Reputation: 4038

PL/SQL execution completed with warning?

I typed the following code in separate pages, it said invalid procedure name. When typed together in the same work-space in SQL Developer it says execution completed with warning. There seems to be no output at all.

It's a college exercise,to get several values from several tables using one value entered by the user - in this case ono.

Here we should get the title of book, quantity ordered and customername using order number (ono).

This is my code:

create or replace procedure proc2 ( ono in number
                    , cname out varchar, book out varchar, qty out number) is
   temp number;
begin    
   select custid into temp from ordr where orderno = ono;
   select custname into cname from customer where custid = temp;
   select isbn,qtystock into temp,qty from order_list where orderno = ono;
   select title into book from books where isbn = temp;
end proc2;

and my main calling function:

set serveroutput on
declare
   cname varchar(7);
   book varchar(7);
   ono number;
   qty number;
begin
   ono := &ono; -- I even tried explicitly giving a value didn't work
   proc2(ono, cname, book, qty);
   dbms_output.put_line('customer name: '||cname||'book title :'||book||'quantity ordered   :'||qty);
end;

The procedure has executed successfully, but I can't seem to find the mistake in the main function. What do you think is wrong?

Even when I executed the following I get function executed successfully and when i execute main function i get executed with warning,i don't understand why!

function:

create or replace function totfun(ip in number) return number is
t number(5);
t1 number(5);
t2 number(5);
begin
select isbn,qtystock into t,t1 from order_list where orderno=ip;
select price into t2 from books where isbn=t;
return t1*t2;
end totfun;

main function:

set serveroutput on;
declare
ip number;
begin
ip:=&orderno;
dbms_output.put_line(totfun(ip));
end;

Upvotes: 3

Views: 9688

Answers (1)

zep
zep

Reputation: 1151

From the manual:

Before exiting a subprogram, assign values to all OUT formal parameters. Otherwise,the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Do you have assigned values to your output parameters?

What are your output parameters before to exit from proc2?

Could it be useful to trace your code inside the procedure proc2?

What are your warnings?

Warnings: useful to read(link)

Upvotes: 1

Related Questions