| Functions [message #37835] |
Fri, 01 March 2002 08:22  |
Panneer Selvam
Messages: 7
Registered: November 2001
|
Junior Member |
|
|
I created a following Function . When i tried to execute this function it gives an error called missing keyword . But if I remove the words "into || v_records ||' from the select statement it works fine.
CREATE OR REPLACE FUNCTION "GET_TOTAL_PENDING"
( P_LOCATION_ID IN NUMBER ,
P_RPT_ID IN NUMBER,
P_TABLE_NAME IN VARCHAR2 :='CRM_CORP_GOV' )
RETURN NUMBER
IS
V_RECORDS NUMBER ;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(DISTINCT RECORD_ID) INTO ' || v_records ||
' FROM ' || P_TABLE_NAME || ' WHERE RECORD_ID
NOT IN ( SELECT DISTINCT RECORD_ID
FROM CRM_STATUS_HISTORY
WHERE ((STATUS_ID <> 2 OR STATUS_ID <> 3 )
AND RPT_ID = ' || P_RPT_ID ||' AND OUT_DATE IS NULL))
AND LOCATION_ID = ' ||P_LOCATION_ID ;
return(v_records);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN (0) ;
END GET_TOTAL_PENDING;
/
|
|
|
|
| Re: Functions [message #37837 is a reply to message #37835] |
Fri, 01 March 2002 09:22  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The INTO clause should be outside the string and don't forget to use bind variables where you can (rpt_id and location_id):
execute immediate
'select count(distinct record_id)'
' from ' || p_table_name ||
' where record_id not in ' ||
' (select distinct record_id' ||
' from crm_status_history' ||
' where status_id not in (2,3)' ||
' and rpt_id = :rpt_id' ||
' and out_date is null)' ||
' and location_id = :location_id'
into v_records
using p_rpt_id, p_location_id; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
