CODEWITHSUNDEEP
phporacle-databaseplsqloracle10g
Awlad Liton
Awlad Liton

Reputation: 9351

OCI_NO_DATA oci8 oracle error

I've got a problem in an Oracle function execution. I've a table loginfo(username,password,type) and I've a function like this:

CREATE OR REPLACE FUNCTION login(name in varchar2,pass in varchar2 )
    RETURN integer
    IS 
    v_type integer;
    BEGIN 
    Select loginfo.type 
    INTO v_type from loginfo
    where loginfo.username=name 
    and loginfo.password=pass;
    RETURN (v_type); 
    END;

I've two types of loginfo; type 1 and 2. If I call this function from php with a parameter that should return type 1 it does. When I call this function with a parameter that should return type 2 it returns the error OCI_NO_DATA in the PHP page. However, when I call function from Oracle it returns 2.

It should return type 2 when my parameters are username = '8801716560946' and password = '123456'.

Upvotes: 2

Views: 5454

Answers (2)

Seb
Seb

Reputation: 11

You should add some exception handler in your code.

CREATE OR REPLACE FUNCTION login(name in varchar2,pass in varchar2 ) RETURN integer IS 
  v_type integer;
BEGIN      
Select loginfo.type 
INTO v_type from loginfo
where loginfo.username=name 
and loginfo.password=pass;
RETURN (v_type); 
 exception when no_data_found then 
    return null;
END;

This way you are guarantee to have a value returned by the function. In a production environment, you also want to use packages as there many advantages to them.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

OCI_NO_DATA implies that there are no rows that match the parameters you've passed in in the table. I'll wager that you're passing different parameters when you call the function from PHP. Perhaps you're passing in strings with some additional spaces in one case and not the other, for example.

Upvotes: 2

Related Questions