is no_data_found needed for this proc (merged) [message #274220] |
Mon, 15 October 2007 02:20  |
soujanya_srk
Messages: 111 Registered: November 2006 Location: HYDERABAD
|
Senior Member |
|
|
i have just written a function for reporting purpose..
i am taking input parameters from java..and based on user input,
i am querying the tables, and returing the refcursor to the
java code
this works fine
create or replace FUNCTION Get_Metuserlist(
n_Org_id IN ECOR_Organization.ORG_ID%TYPE,
n_Interface_Id IN VENDOR_USER_DATA.INTERFACE_ID%TYPE,
n_Status IN VENDOR_USER_DATA.STATUS%TYPE,
o_RowCount OUT NUMBER,
o_RefCurUserList OUT Type_Def.t_RefCurTyp)
RETURN INTEGER
-- RefCursor definition for DAO generator
/*o_RefCurUserList
ECUS_USER_ID VENDOR_USER_DATA.ECUS_USER_ID%type
o_RefCurUserList*/
AS
l_Prg_Name VARCHAR2(30) := 'Get_Metuserlist';
l_SqlStmt VARCHAR2(32767);
l_Whr_Clause VARCHAR2(1000);
BEGIN
IF n_Org_id IS NULL OR n_Interface_Id IS NULL or n_Status IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, ' n_Org_id or n_Interface_Id or is n_Status NULL in '||l_Prg_Name);
RETURN 1;
END IF;
l_Whr_Clause:=' where ven.ECUS_USER_ID=ecus.USER_ID and ecus.ECOR_ORG_ID=ecor.ORG_ID';
IF n_Org_id IS NOT NULL THEN
l_Whr_Clause := l_Whr_Clause||' AND ecor.ORG_ID= '||n_Org_id;
END IF;
IF n_Interface_Id IS NOT NULL THEN
l_Whr_Clause := l_Whr_Clause||' AND ven.INTERFACE_ID= '||n_Interface_Id;
END IF;
IF n_Status IS NOT NULL THEN
l_Whr_Clause := l_Whr_Clause||' AND ven.STATUS= '||n_Status;
END IF;
l_SqlStmt:='SELECT count(1) FROM vendor_user_data ven,ecor_organization ecor,ecus_user ecus ';
l_SqlStmt:=l_SqlStmt||l_Whr_Clause;
dbms_output.put_line(l_SqlStmt);
EXECUTE IMMEDIATE l_SqlStmt INTO o_RowCount;
l_SqlStmt := 'SELECT ECUS_USER_ID FROM VENDOR_USER_DATA ven,ecor_organization ecor,ecus_user ecus ';
l_SqlStmt:=l_SqlStmt||l_Whr_Clause;
dbms_output.put_line('query: '||l_SqlStmt);
open o_RefCurUserList for l_SqlStmt;
RETURN 0;
--EXCEPTION
-- WHEN OTHERS THEN
--RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM||' IN '||l_Prg_Name);
-- RETURN 1;
END Get_Metuserlist;
/
now i try to include bind variables, to improve performance
..and changed the above proc as follows
create or replace FUNCTION Get_Metuserlist(
n_Org_id IN ECOR_Organization.ORG_ID%TYPE,
n_Interface_Id IN VENDOR_USER_DATA.INTERFACE_ID%TYPE,
n_Status IN VENDOR_USER_DATA.STATUS%TYPE,
o_RowCount OUT NUMBER,
o_RefCurUserList OUT Type_Def.t_RefCurTyp)
RETURN INTEGER
-- RefCursor definition for DAO generator
/*o_RefCurUserList
ECUS_USER_ID VENDOR_USER_DATA.ECUS_USER_ID%type
o_RefCurUserList*/
AS
l_Prg_Name VARCHAR2(30) := 'Get_Metuserlist';
l_SqlStmt VARCHAR2(32767);
l_Whr_Clause VARCHAR2(1000);
BEGIN
IF n_Org_id IS NULL OR n_Interface_Id IS NULL or n_Status IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, ' n_Org_id or n_Interface_Id or is n_Status NULL in '||l_Prg_Name);
RETURN 1;
END IF;
l_Whr_Clause:=' where ven.ECUS_USER_ID=ecus.USER_ID and ecus.ECOR_ORG_ID=ecor.ORG_ID';
IF n_Org_id IS NOT NULL THEN
l_Whr_Clause := l_Whr_Clause||' AND ecor.ORG_ID= :x' using n_Org_id;
END IF;
IF n_Interface_Id IS NOT NULL THEN
l_Whr_Clause := l_Whr_Clause||' AND ven.INTERFACE_ID= :y' using n_Interface_Id;
END IF;
IF n_Status IS NOT NULL THEN
l_Whr_Clause := l_Whr_Clause||' AND ven.STATUS= :z' using n_Status;
END IF;
l_SqlStmt:='SELECT count(1) FROM vendor_user_data ven,ecor_organization ecor,ecus_user ecus ';
l_SqlStmt:=l_SqlStmt||l_Whr_Clause;
dbms_output.put_line(l_SqlStmt);
EXECUTE IMMEDIATE l_SqlStmt INTO o_RowCount;
l_SqlStmt := 'SELECT ECUS_USER_ID FROM VENDOR_USER_DATA ven,ecor_organization ecor,ecus_user ecus ';
l_SqlStmt:=l_SqlStmt||l_Whr_Clause;
dbms_output.put_line('query: '||l_SqlStmt);
open o_RefCurUserList for l_SqlStmt;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM||' IN '||l_Prg_Name);
RETURN 1;
END Get_Metuserlist;
/
when i run it, i get this error
SQL> @get_metbind.sql;
Warning: Function created with compilation errors.
SQL> show err
Errors for FUNCTION GET_METUSERLIST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
26/64 PLS-00103: Encountered the symbol "USING" when expecting one of
the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMULTISET_
The symbol "* was inserted before "USING" to continue.
30/68 PLS-00103: Encountered the symbol "USING" when expecting one of
the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LINE/COL ERROR
-------- -----------------------------------------------------------------
LIKE4_ LIKEC_ between || member SUBMULTISET_
The symbol "* was inserted before "USING" to continue.
34/62 PLS-00103: Encountered the symbol "USING" when expecting one of
the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMULTISET_
The symbol "* was inserted before "USING" to continue.
whats wrong with the syntax? i have referred the link
http://www.akadia.com/services/ora_bind_variables.html
and followed how to user bind variables with dynamic sql
|
|
|
|
is no_data_found needed for this proc? [message #274258 is a reply to message #274220] |
Mon, 15 October 2007 04:31   |
soujanya_srk
Messages: 111 Registered: November 2006 Location: HYDERABAD
|
Senior Member |
|
|
I have proc
create or replace FUNCTION Get_Metuserlist(
n_Org_id IN ECOR_Organization.ORG_ID%TYPE,
n_Interface_Id IN VENDOR_USER_DATA.INTERFACE_ID%TYPE,
n_Status IN VENDOR_USER_DATA.STATUS%TYPE,
o_RowCount OUT NUMBER,
o_RefCurUserList OUT Type_Def.t_RefCurTyp)
RETURN INTEGER
-- RefCursor definition for DAO generator
/*o_RefCurUserList
ECUS_USER_ID VENDOR_USER_DATA.ECUS_USER_ID%type
o_RefCurUserList*/
AS
l_Prg_Name VARCHAR2(30) := 'Get_Metuserlist';
p_missing_parameters exception;
BEGIN
IF n_Org_id IS NULL OR n_Interface_Id IS NULL or n_Status IS NULL THEN
Raise p_missing_parameters ;
END IF;
open o_RefCurUserList for
select ven.ecus_user_id
from vendor_user_data ven,ecor_organization ecor,ecus_user ecus
where ven.ECUS_USER_ID=ecus.USER_ID
and ecus.ECOR_ORG_ID=ecor.ORG_ID
and ecor.ORG_ID = n_Org_id
and ven.INTERFACE_ID = n_Interface_Id
and ven.STATUS = n_Status;
select count(ven.ecus_user_id) into o_RowCount
from vendor_user_data ven,ecor_organization ecor,ecus_user ecus
where ven.ECUS_USER_ID=ecus.USER_ID
and ecus.ECOR_ORG_ID=ecor.ORG_ID
and ecor.ORG_ID = n_Org_id
and ven.INTERFACE_ID = n_Interface_Id
and ven.STATUS = n_Status;
RETURN 0;
EXCEPTION
WHEN p_missing_parameters THEN
RAISE_APPLICATION_ERROR(-20001, 'Missing essential parameters in '||l_Prg_Name);
WHEN NO_DATA_FOUND then
RAISE_APPLICATION_ERROR(-20002,'No data found for '||n_Org_id||' and '||n_Interface_Id||' and '||n_Status);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM||' IN '||l_Prg_Name);
RETURN 1;
END Get_Metuserlist;
/
i need to know whether " open o_RefCurUserList for "
and "select count(ven.ecus_user_id) into o_RowCount"
needs to be included in seperate begin ...exception...end
blocks to handle with no_data_found seperately?
i mean, if i open an empty refcursor, will it be an exception?
and if i fetch a count which is zero into a variable (o_RowCount)
as in above case, will it be an exception?
no_data_found has to be included ?
or do think the above code is fine ?
i have no executing when there are no rows in the
tables
[mod-edit] removed iiliterate IM speak
[Updated on: Mon, 15 October 2007 07:33] by Moderator Report message to a moderator
|
|
|
|
|
|
|