Home » SQL & PL/SQL » SQL & PL/SQL » call an function in a package with select statement? (10g)
call an function in a package with select statement? [message #330426] Mon, 30 June 2008 03:02 Go to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
Hello, I'm beginning to learn oracle pl/sql programming and I'm trying to call an function in an package with an select statement but it isn't successful.
For example:
select student_id, student_package.student_function(student_id) from student_table

Please show me the way to solve this problem!
Thanks!

Re: call an function in a package with select statement? [message #330430 is a reply to message #330426] Mon, 30 June 2008 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
'But it isn't sucessful' doesn't give us a lot to work with.
Your syntax looks ok.

Can you post the actual error message that you're getting?
Re: call an function in a package with select statement? [message #330439 is a reply to message #330426] Mon, 30 June 2008 03:17 Go to previous messageGo to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
I call my function in sqlplus, the result like that:
SQL> set serveroutput on;
SQL> declare 
  2  tc number;
  3  begin
  4  tc:=qldiem.get_diemtrungbinh('1020880','DIEM_20071');
  5  dbms_output.put_line('Diem trung binh: '||tc);
  6  end;
  7  .
SQL> /
Tong so tin chi:21
Tong so diem: 204
9.71
Diem trung binh: 9.71

PL/SQL procedure successfully completed.

But with a select statement, th result like that:
SQL> select f_masv, qldiem.get_diemtrungbinh(f_masv,'DIEM_20071') as dtb
  2  from DIEM_20071
  3  where f_masv like '%1020880%';
select f_masv, qldiem.get_diemtrungbinh(f_masv,'DIEM_20071') as dtb
               *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "HQLDT.QLDIEM", line 101

[Updated on: Mon, 30 June 2008 03:26]

Report message to a moderator

Re: call an function in a package with select statement? [message #330442 is a reply to message #330439] Mon, 30 June 2008 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-06503: PL/SQL: Function returned without value
 *Cause: A call to PL/SQL function completed, but no RETURN statement was
         executed.
 *Action: Rewrite PL/SQL function, making sure that it always returns
          a value of a proper type.

Regards
Michel

Re: call an function in a package with select statement? [message #330451 is a reply to message #330442] Mon, 30 June 2008 03:40 Go to previous messageGo to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
In my function, I have an return statement.
SQL> function get_diemtrungbinh(
  2    v_masv stdsv.f_masv%type,
  3    v_diem_nhhk user_tables.table_name%type
  4   ) return number
  5   is
  6    v_retval number;
  7    v_total_diem integer;
  8    v_total_tinchi_tdtb integer;
  9    stmt varchar2(250);
 10    v_err_code number;
 11    v_err_msg varchar2(200);
 12   begin
 13    v_total_tinchi_tdtb := get_tongso_tinchi(v_masv,v_diem_nhhk)-get_tongso_tinchi_dieukien(v_mas
v,v_diem_nhhk);
 14    stmt:='select sum(ctdmh.f_dvht*'||v_diem_nhhk||'.f_diem2) from ctdmh,'||v_diem_nhhk||
 15          ' where trim('||v_diem_nhhk||'.f_masv)='''||v_masv||''' and upper(trim(ctdmh.f_mamh))=u
pper(trim('||v_diem_nhhk||'.f_mamh))'||
 16          ' and ctdmh.f_khgdtb is null';
 17    execute immediate stmt into v_total_diem;
 18    v_total_diem:=coalesce(v_total_diem,0);
 19    dbms_output.put_line('Tong so tin chi:'||v_total_tinchi_tdtb);
 20    dbms_output.put_line('Tong so diem: '||v_total_diem);
 21    v_retval:=round(v_total_diem/v_total_tinchi_tdtb,2);
 22    dbms_output.put_line(v_retval);
 23    return v_retval;
 24    exception
 25     when others then
 26      begin
 27       v_err_code:=SQLCODE;
 28       v_err_msg:=substr(sqlerrm,1,200);
 29       dbms_output.put_line('Error code: '||v_err_code);
 30       dbms_output.put_line('Error message: '||v_err_msg);
 31      end; 
 32   end get_diemtrungbinh;
 33  .
SQL> /

[Updated on: Mon, 30 June 2008 03:43]

Report message to a moderator

Re: call an function in a package with select statement? [message #330454 is a reply to message #330451] Mon, 30 June 2008 03:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Remove the exception handler
If one of your statements fails, flow will continue in the exception handler. From there, no value is returned.
Re: call an function in a package with select statement? [message #330459 is a reply to message #330454] Mon, 30 June 2008 04:02 Go to previous messageGo to next message
vlinh
Messages: 11
Registered: June 2008
Junior Member
I have removed the exception handler, and the result of statement like that:
SQL> select f_masv, qldiem.get_diemtrungbinh(f_masv,'DIEM_20071') as dtb
  2  from DIEM_20071
  3  where f_masv='1020880';
select f_masv, qldiem.get_diemtrungbinh(f_masv,'DIEM_20071') as dtb
               *
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "HQLDT.QLDIEM", line 92

This error may be caused by two statements following:
18    v_total_diem:=coalesce(v_total_diem,0);
21    v_retval:=round(v_total_diem/v_total_tinchi_tdtb,2);

Is it true?
Thanks & Regards,
vlinh
Re: call an function in a package with select statement? [message #330463 is a reply to message #330459] Mon, 30 June 2008 04:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Now it's back to you.
You know what causes the error; you were able to create that procedure. You certainly don't need us to confirm if that is the line where you get your error.
Have faith in you own judgment.
Try to fix it, and come back when/if you get stuck.
If you make it a habit to ask questions here, for which you DO know the answers, it will be bad for your confidence.
Re: call an function in a package with select statement? [message #330470 is a reply to message #330463] Mon, 30 June 2008 04:24 Go to previous message
vlinh
Messages: 11
Registered: June 2008
Junior Member
Thanks & Regards,
vlinh
Previous Topic: Merge - performance issue
Next Topic: Problem:Update target table (merged)
Goto Forum:
  


Current Time: Wed Dec 07 12:37:16 CST 2016

Total time taken to generate the page: 0.06887 seconds