Home » SQL & PL/SQL » SQL & PL/SQL » ORA-20000, ORA-00933, why no NO DATA FOUND (merged)
ORA-20000, ORA-00933, why no NO DATA FOUND (merged) [message #255946] Thu, 02 August 2007 08:56 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i am executing the following function, can anyone point out
why i am getting this error?

CREATE TABLE AuditedUsers (
    User_ID                    NUMBER(16)     
    User_Ident                 Varchar2(30)   
    User_First_Name            Varchar2(32),
    User_Last_Name             Varchar2(32),
    User_Org_Type              NUMBER(16)  ,
    Org_ID                     NUMBER(16)   ,
    Org_Short_name             Varchar2(20) ,
    Org_name                   Varchar2(32),
    Provider_Org_ID            NUMBER(16),
    Provider_Org_Short_name    Varchar2(20),
    Provider_Org_name          Varchar2(32),
    DeletedDate                Date
)

insert into auditedusers values(12,'gautam','gautam','vish',1,2,'INTFI73','INTFI73',1,'HOST','HOST',NULL);

create or replace FUNCTION Get_UserList(
    n_Org_type       IN     ECOR_Organization.ecot_org_id%TYPE,
    n_Org_Sname      IN     ECOR_Organization.short_name%TYPE,
    n_User_Ident     IN     ECUS_USER.user_ident%TYPE,
    n_Last_Name      IN     ECUS_User.Last_Name%TYPE,
    n_First_Name     IN     ECUS_User.First_Name%TYPE,
    n_CurrentPage    IN OUT NUMBER,
    n_RowsPerPage    IN OUT NUMBER,
    o_RowCount       OUT    NUMBER,
    o_RefCurUserList OUT    Type_Def.t_RefCurTyp)
RETURN INTEGER
-- RefCursor definition for DAO generator
/*o_RefCurUserList
User_ID       ECUS_User.User_ID%TYPE,
User_Name     VARCHAR2,
Org_ShortName ECOR_Organization.Short_Name%TYPE,
User_OrgType  ecot_org_t.DESCR%TYPE,
User_Ident    ECUS_User.User_Ident%TYPE
o_RefCurUserList*/
AS
    l_Prg_Name      VARCHAR2(30) := 'Get_UserList';
    l_SqlStmt       VARCHAR2(32767);
    l_Whr_Clause    VARCHAR2(1000);
BEGIN

    IF n_Org_type IS NULL OR n_Org_SName IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, ' n_Org_type or n_Org_SName is NULL in '||l_Prg_Name);
        RETURN 1;
    END IF;
    -- default where clause
     l_Whr_Clause := 'where user_org_type =n_Org_type and  Org_Short_Name = UPPER(n_Org_SName)';

   -- IF n_Org_type IS NOT NULL THEN
    --    l_Whr_Clause := l_Whr_Clause||' AND user_org_type LIKE '||quoted_string(n_Org_type);
  --  END IF;


     -- IF n_Org_Sname IS NOT NULL THEN
       -- l_Whr_Clause := l_Whr_Clause||' AND Org_Short_Name LIKE '||quoted_string(n_Org_Sname);
   -- END IF;

    l_SqlStmt:='SELECT count(1) FROM AuditedUsers';

    IF n_User_Ident IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' AND User_Ident LIKE '||quoted_string(n_User_Ident);
    END IF;

    l_SqlStmt:=l_SqlStmt||l_Whr_Clause;

   -- insert into phldrtmpdata values(testseq1.nextval,8,l_SqlStmt);
   -- commit;

     EXECUTE IMMEDIATE l_SqlStmt INTO o_RowCount;

    IF n_Last_Name IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' AND User_Last_Name LIKE '||quoted_string(n_Last_Name);
    END IF;

    IF n_First_Name IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' AND User_First_Name LIKE '||quoted_string(n_First_Name);
    END IF;


    l_SqlStmt := 'SELECT User_ID, 
user_last_name||'',''||user_first_name user_name,org_short_name,user_org_type,user_ident 
FROM AuditedUsers';
    

    l_SqlStmt:=l_SqlStmt||l_Whr_Clause;

     --insert into phldrtmpdata values(testseq1.nextval,8,l_SqlStmt);
     --commit;

    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_UserList;



variable g_RowCount number;
variable y number;
variable g_RefCurUserList REFCURSOR;
variable g_CurrentPage number;
variable g_RowsPerPage number;

begin
:g_CurrentPage:=1;
end;

begin
:g_RowsPerPage:=6;
end;

exec :y:=Get_UserList(1,'INTFI73',NULL,NULL,NULL,:g_CurrentPage,:g_RowsPerPage,:g_RowCount,:g_RefCurUserList);

PRINT g_RefCurUserList


ERROR at line 1:
ORA-20000: DB Error: ORA-00933: SQL command not properly ended IN Get_UserList
ORA-06512: at "WCMCPP2.GET_USERLIST", line 81
ORA-06512: at line 1





[Updated on: Thu, 02 August 2007 09:16] by Moderator

Report message to a moderator

Re: ORA-20000: DB Error: ORA-00933 while executing function [message #255949 is a reply to message #255946] Thu, 02 August 2007 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Verify the queries you generate.
Remove your exception block then you can know from where the exception comes.

Regards
Michel
Re: ORA-20000: DB Error: ORA-00933 while executing function [message #255955 is a reply to message #255946] Thu, 02 August 2007 09:47 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
seems to be a problem with the ' in first l_where
Re: ORA-20000: DB Error: ORA-00933 while executing function [message #256045 is a reply to message #255946] Thu, 02 August 2007 18:14 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
IMO, it is a Good Thing if you ALWAYS print out the actual SQL right before issuing the EXECUTE IMMEDIATE.
If/when it has a problem you can then CUT & PASTE it into SQL*Plus to see exactly where/how/why it failed.

You should format the source code to include LINE NUMBERS!
Re: ORA-20000: DB Error: ORA-00933 while executing function [message #256064 is a reply to message #255946] Thu, 02 August 2007 21:28 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
whats wrong with this?

i am unable to get intfi73 in single quote, and hence the
invalid identifier error


declare
l_Whr_Clause varchar2(31000);
n_Org_type number:=1;
n_Org_SName varchar2(20):='INTFI73';
l_SqlStmt       VARCHAR2(32767);

 n_User_Ident     ECUS_USER.user_ident%TYPE;

begin
l_Whr_Clause := ' where USER_ORG_TYPE  ='||n_Org_type||' and  ORG_SHORT_NAME  = UPPER(''||(n_Org_SName)||'')';
       

l_SqlStmt:='SELECT count(1) FROM AuditedUsers ';

dbms_output.put_line(l_Whr_Clause);

l_Whr_Clause := l_Whr_Clause||' AND User_Ident LIKE '||(n_User_Ident);

l_SqlStmt:=l_SqlStmt||l_Whr_Clause;

dbms_output.put_line('final query: '||l_SqlStmt);

end;





SQL> declare
  2  l_Whr_Clause varchar2(31000);
  3  n_Org_type number:=1;
  4  n_Org_SName varchar2(20):='INTFI73';
  5  l_SqlStmt       VARCHAR2(32767);
  6  
  7   n_User_Ident     ECUS_USER.user_ident%TYPE;
  8  
  9  begin
 10  l_Whr_Clause := ' where USER_ORG_TYPE  ='||n_Org_type||' and  ORG_SHORT_NAME  = UPPER(''||(n_Org_SName)||'')';
 11         
 12  
 13  l_SqlStmt:='SELECT count(1) FROM AuditedUsers ';
 14  
 15  dbms_output.put_line(l_Whr_Clause);
 16  
 17  l_Whr_Clause := l_Whr_Clause||' AND User_Ident LIKE '||(n_User_Ident);
 18  
 19  l_SqlStmt:=l_SqlStmt||l_Whr_Clause;
 20  
 21  dbms_output.put_line('final query: '||l_SqlStmt);
 22  
 23  end;
 24  /
where USER_ORG_TYPE  =1 and  ORG_SHORT_NAME  = UPPER('||(n_Org_SName)||')
final query: SELECT count(1) FROM AuditedUsers  where USER_ORG_TYPE  =1 and
ORG_SHORT_NAME  = UPPER('||(n_Org_SName)||') AND User_Ident LIKE

PL/SQL procedure successfully completed.


Re: ORA-20000: DB Error: ORA-00933 while executing function [message #256074 is a reply to message #255946] Thu, 02 August 2007 21:55 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Oracle Database SQL Reference (a part of documentation books)
Chapter 2 Basic Elements of Oracle SQL
Literals
Text Literals
Quote:
A single quotation mark (') within the literal must be preceded by an escape character. To represent one single quotation mark within a literal, enter two single quotation marks.
...
Here are some valid text literals:
...
'Jackie''s raincoat'
why this function doesnt throw a no data found? [message #256172 is a reply to message #255946] Fri, 03 August 2007 04:53 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i have developed a function today...its all dynamic sql
and its for a report

now i have developed this function looking and taking help from
an already developed function..its working fine, alright

now if there is no data in any of the selects..(l_sqlstmt)
why this function doesnt throw a no_data_found?

or is it that its getting handled by when others?

shall i put

when no_data_found then
null

or not?

below is the proc

create or replace FUNCTION Get_UserList(
    n_Org_type       IN     ECOR_Organization.ecot_org_id%TYPE,
    n_Org_Sname      IN     ECOR_Organization.short_name%TYPE,
    n_User_Ident     IN     ECUS_USER.user_ident%TYPE,
    n_Last_Name      IN     ECUS_User.Last_Name%TYPE,
    n_First_Name     IN     ECUS_User.First_Name%TYPE,
    n_CurrentPage    IN OUT NUMBER,
    n_RowsPerPage    IN OUT NUMBER,
    o_RowCount       OUT    NUMBER,
    o_RefCurUserList OUT    Type_Def.t_RefCurTyp)
RETURN INTEGER
-- RefCursor definition for DAO generator
/*o_RefCurUserList
User_ID       ECUS_User.User_ID%TYPE,
User_Name     VARCHAR2,
Org_ShortName ECOR_Organization.Short_Name%TYPE,
User_OrgType  ecot_org_t.DESCR%TYPE,
User_Ident    ECUS_User.User_Ident%TYPE
o_RefCurUserList*/
AS
    l_Prg_Name      VARCHAR2(30) := 'Get_UserList';
    l_SqlStmt       VARCHAR2(32767);
    l_Whr_Clause    VARCHAR2(1000);
BEGIN
    dbms_output.put_line('started');

    IF n_Org_type IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, ' n_Org_type is NULL in '||l_Prg_Name);
        RETURN 1;
    END IF;

    l_SqlStmt:='SELECT count(1) FROM AuditedUsers ';          
       
       
    IF n_Org_type IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' WHERE USER_ORG_TYPE ='||(n_Org_type);
    END IF;


    IF n_Org_Sname IS NOT NULL THEN
            l_Whr_Clause := l_Whr_Clause||' AND UPPER(ORG_SHORT_NAME) LIKE '||quoted_string(UPPER(n_Org_Sname));
    END IF;

    

    IF n_User_Ident IS NOT NULL THEN
         l_Whr_Clause := l_Whr_Clause||' AND upper(USER_IDENT)  LIKE '||quoted_string(upper(n_User_Ident));
    END IF;

             
   


    IF n_Last_Name IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' AND upper(USER_LAST_NAME)  LIKE '||quoted_string(upper(n_Last_Name));
    END IF;

    IF n_First_Name IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' AND upper(USER_FIRST_NAME)  LIKE '||quoted_string(upper(n_First_Name));
    END IF;

    l_SqlStmt:=l_SqlStmt||l_Whr_Clause;
    dbms_output.put_line(l_SqlStmt);
    EXECUTE IMMEDIATE l_SqlStmt INTO o_RowCount;
    dbms_output.put_line(o_RowCount);

    l_SqlStmt := 'SELECT User_ID, user_last_name||'',''||user_first_name user_name,org_short_name,user_org_type,user_ident FROM AuditedUsers';
    

    l_SqlStmt:=l_SqlStmt||l_Whr_Clause;

     dbms_output.put_line(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_UserList;





                    



    




Re: why this function doesnt throw a no data found? [message #256174 is a reply to message #256172] Fri, 03 August 2007 04:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You opened a ref cursor. You didn't fetch, and even if you would fetch you still wouldn't get the NO_DATA_FOUND. You can check with <yourcursor>%NOTFOUND.

MHE
Re: why this function doesnt throw a no data found? [message #256177 is a reply to message #256174] Fri, 03 August 2007 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just a question - what purpose do you think the "RETURN 1" after the Rasie_Application_error serves?

You'r code has a HUGE flaw in it - if n_Org_type is null then you will not generate a valid Where clase, as you'll be missing the WHERE part of it.
Replace this code:
   IF n_Org_type IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' WHERE USER_ORG_TYPE ='||(n_Org_type);
    END IF;
with something like this:
   
    l_whr_clause := ' WHERE 1=1 ';
    IF n_Org_type IS NOT NULL THEN
        l_Whr_Clause := l_Whr_Clause||' AND USER_ORG_TYPE ='||(n_Org_type);
    END IF;
Re: why this function doesnt throw a no data found? [message #256222 is a reply to message #256177] Fri, 03 August 2007 08:07 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
sorry senior member
using in out and out parameters in a function is a poor approach and function assumes to return one value most of the time , when you use additional out parameters some times result is not as expected
Re: why this function doesnt throw a no data found? [message #256226 is a reply to message #256222] Fri, 03 August 2007 08:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You miss my point.

The RETURN is after the Raise_Application_Error, and so will never be executed.
Why write code that can never be executed?
Re: ORA-20000, ORA-00933, why no NO DATA FOUND (merged) [message #256508 is a reply to message #255946] Sun, 05 August 2007 06:54 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
thanks rowbottom,

the idea is that even if it becomes null, there should not be
any problem..

what is 1 in
'where 1=1' ?

this i would try it out and see..


Re: ORA-20000, ORA-00933, why no NO DATA FOUND (merged) [message #256513 is a reply to message #256508] Sun, 05 August 2007 08:26 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what is 1 in 'where 1=1' ?

Nothing more than a start of a where clause.
Then all clauses that you can start will begin with "and ..." you don't have to know if you already added something or not.

Regards
michel
Previous Topic: Globalization support
Next Topic: How to create a duplicate table in oracle without using select,create,insert etc. commands
Goto Forum:
  


Current Time: Tue Dec 06 16:01:51 CST 2016

Total time taken to generate the page: 0.11661 seconds