Home » SQL & PL/SQL » SQL & PL/SQL » calling function from sp
calling function from sp [message #35687] Fri, 05 October 2001 13:27 Go to next message
George
Messages: 68
Registered: April 2001
Member
My function is called from a sp using a select statement, it return error msg saying invalid column if I don't comment out the function call (worked if no function call). Could you help me to fix it? below is part of my code:
create or replace package facilitySummary AS
PRAGMA SERIALLY_REUSABLE;
TYPE curType IS REF CURSOR;
function subSum (
party_code IN VARCHAR2,
action_type IN VARCHAR2,
party_type IN VARCHAR2,
facility_code IN VARCHAR2)
RETURN NUMBER;
function facilitySum (
party_code IN VARCHAR2,
party_type IN VARCHAR2,
facility_code IN VARCHAR2)
RETURN curType;
END;
CREATE OR REPLACE PACKAGE BODY facilitySummary AS
PRAGMA SERIALLY_REUSABLE;
function subSum (
party_code IN VARCHAR2,
action_type IN VARCHAR2,
party_type IN VARCHAR2,
facility_code IN VARCHAR2 )
RETURN NUMBER IS
pdSum NUMBER(10);
BEGIN
**** worked fine *****
RETURN pdsum;
END subSum;
function facilitySum (
party_code IN VARCHAR2,
party_type IN VARCHAR2,
facility_code IN VARCHAR2)
RETURN curType IS
sum_cv curType;
err_num NUMBER;
err_msg VARCHAR2(200);
query_statement VARCHAR2(300);
from_statement VARCHAR2(300);
sql_statement VARCHAR2(2000);
where_statement VARCHAR2(600);
input_where VARCHAR2(200);
condition VARCHAR2(200);
BEGIN
sql_statement :=' SELECT fg.name as facilitygroupName,
f.facilitycode,
f.facilityname,
pf.partyname as FACILITYOPERATORNAME,
pf.partycode AS facilityoperatorcode,
rownum AS rn ,
/*facilitySummary.subSum(party_code, ''pendingdeliveries'', party_type, facility_code) as PENDINGDELIVERY, */
pc.partycode as CARRIERCODE
FROM facility f,
party pc,
party pf,
(select name,
facilityid,
facilitygroupid,
parentkey
from facilityGroup
CONNECT BY prior facilityGroupid = parentKey
START WITH facilityGroupid = (SELECT facilityGroupid from party where partycode = '''||party_code||''')) fg '
WHERE .......

OPEN sum_cv FOR sql_statement ;
END facilitySum;
END;

Thanks

----------------------------------------------------------------------
Re: calling function from sp [message #35696 is a reply to message #35687] Sun, 07 October 2001 06:30 Go to previous message
Rajarshi Dasgupta
Messages: 52
Registered: October 2001
Member
In your select statement..... use DUAL in the FROM part where U R declaring the tables. like as follows:

SELECT fg.name as facilitygroupName,
f.facilitycode,
f.facilityname,
pf.partyname as FACILITYOPERATORNAME,
pf.partycode AS facilityoperatorcode,
rownum AS rn ,
facilitySummary.subSum(party_code, ''pendingdeliveries'', party_type, facility_code) as PENDINGDELIVERY,
pc.partycode as CARRIERCODE
FROM DUAL, facility f,
party pc,
party pf,
(select name,
facilityid,
facilitygroupid,
parentkey
from facilityGroup
CONNECT BY prior facilityGroupid = parentKey
START WITH facilityGroupid = (SELECT facilityGroupid from party where partycode = '''||party_code||''')) fg '
WHERE .......

This is because in your query, its not getting the function name as any column.

----------------------------------------------------------------------
Previous Topic: formatting a output
Next Topic: Why can't u use dynamic sql?
Goto Forum:
  


Current Time: Fri Mar 29 01:05:10 CDT 2024