Home » SQL & PL/SQL » SQL & PL/SQL » is no_data_found needed for this proc (merged)
is no_data_found needed for this proc (merged) [message #274220] Mon, 15 October 2007 02:20 Go to next message
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
Re: getting problem when trying to intoduce bind variables in plsql code [message #274228 is a reply to message #274220] Mon, 15 October 2007 02:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I think you have misinterpreted the page you're referring to. Binding occurs when the switch to the SQL engine is made. Not in PL/SQL. So you can use the "using" clause, but you can only use it when you make your switch to SQL, at the EXECUTE IMMEDIATE statement. Before that, there's no point in binding.

Have a look at this example.

MHE
is no_data_found needed for this proc? [message #274258 is a reply to message #274220] Mon, 15 October 2007 04:31 Go to previous messageGo to next message
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

Re: is no_data_found needed for this proc? [message #274262 is a reply to message #274258] Mon, 15 October 2007 04:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I don't think it's fine. I think it's a bad idea to return the ROWCOUNT. If you fetch the number of rows, why not return a PL/SQL table of ven.ecus_user_id? Then you can use plsql_table.COUNT instead of o_RowCount.

The select count... INTO will not generate an error.
The open of the ref cursor will not generate an error. You've only opened the ref cursor, you haven't fetched from it yet. The NO_DATA_FOUND is not necessary if I see it correctly. But I have some problems with you using the same query twice.

MHE
Re: is no_data_found needed for this proc? [message #274269 is a reply to message #274262] Mon, 15 October 2007 05:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Furthermore, the return 1 will never be reached, so returning an resultcode is useless.
Re: getting problem when trying to intoduce bind variables in plsql code [message #274271 is a reply to message #274228] Mon, 15 October 2007 05:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Take a look at using sys_context. This can greatly help when using an unknown number of bind-variables.
Re: is no_data_found needed for this proc? [message #274277 is a reply to message #274269] Mon, 15 October 2007 05:31 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ah! Where would we be without "Hawkeye" Frank? Very Happy

MHE
Previous Topic: index-table dependency
Next Topic: projected value calculation
Goto Forum:
  


Current Time: Wed Dec 07 14:24:44 CST 2016

Total time taken to generate the page: 0.07531 seconds