how to execute this PL/SQL procedure?

[bharthi]

Hi all,
I need your help here,

i have pl/sql procedure , this query returns 1 as out put, but to see that result?, when i execute this procedure,like this,

execute pro_name('emailid',password);
it shows only "anonymous block completed",
how to see its output that retrieves, Is there any possibility?
my PLSQL query is,

CREATE OR REPLACE
  PROCEDURE PRO_GET_SYSTEM_IsValidUser(
                                       p_loginID in varchar2,
                                       p_password in varchar2,
                                       p_exists in out number
                                      )
    IS
    BEGIN
        SELECT  COUNT(*)
          INTO  p_exists
          FROM  employees
          WHERE emailid = p_loginid
            AND password = p_password
            AND enabled = 'TRUE'
            AND ROWNUM=1;
END;

Thanks in advance.

[bharthi]

Hi all,
I need your help here,

i have pl/sql procedure , this query returns 1 as out put, but how to see that result?. when i execute this procedure,like this,

execute pro_name('emailid',password);
it shows
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'PRO_GET_SYSTEM_ISVALIDUSER'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored.

how to see its output that retrieves, Is there any possibility?
my PLSQL query is,

CREATE OR REPLACE
PROCEDURE PRO_GET_SYSTEM_IsValidUser(
p_loginID in varchar2,
p_password in varchar2,
p_exists in out number
)
IS
BEGIN
SELECT COUNT(*)
INTO p_exists
FROM employees
WHERE emailid = p_loginid
AND password = p_password
AND enabled = 'TRUE'
AND ROWNUM=1;
END;

Thanks in advance.

[raghurocks]

EXEC PRO_GET_SYSTEM_IsValidUser('emailid','password')
because password also charcter type so u should declare that within single codes...
just try this bharthi..............

[bharthi]

Hi raghurocks, I did like that only, but i posted wrongly in my post, eventhough i executing with two parameters, how it will work, im sure that this query needs three parameter, thats y i don know how to pass third one, please provide solution if you know.
Thanks for your reply.

[Rabbit]

Please use code tags when posting code.

Your stored procedure requires 3 input parameters. The last of which is also an output parameter. I see no reason for it to be an input parameter as well.

[bharthi]

HI Rabbit,
I executed that procedure like this,
execute pro_name ('emailid','password'), it returns an error, so i need third one , how to declare third one when i executing this procedure? , i storing 1 in p_exist so it should be in out parameter. please help if you know this.
Thanks for reply.

[Rabbit]

You may be storing 1 to output, but you are not inputting anything. There is no need to declare it as an input.

[bharthi]

oh I see! ...Then Ok , so i need to declare that "P_exist" as OUT . am i right sir?. if i executed after i declared as "OUT" for that third argument, i got error,
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PRO_GET_SYSTEM_ISVALIDUSER'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

please reply , what i have to do ,how to execute this procedure, or else , in which way i can rewrite this procedure that can return value 1.
Thanking You.

[raghurocks]

Try declaring the p_exists as out rather than "in out"..bcoz An OUT parameter is initially NULL. The program assigns the parameter a value and that value is returned to the calling program.

[raghurocks]

Try executing like this:

execute pro_name('emailid','password',null);

[bharthi]

Hi, no its not executing, i getting error only.

[raghurocks]

can u post that error

[bharthi]

Error starting at line 21 in command:
execute PRO_GET_SYSTEM_IsValidUser ('[e-mail]','demo',null)
Error report:
ORA-06550: line 1, column 60:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

[raghurocks]

have you declared the p_Exists as out number

[bharthi]

yes i declared as out number ,but its not working , what to do i don know, actually i have one login page ,for that they coded in .CS page inthat page they used four stored procedure in T-SQL, for that i have to convert into pl/SQL, but searched a lot but no founded a solution.

[rski]

use something like that (you have to enable output)

declare 
v_p number
begin
pro_name('emailid',password,v_p);
dbms_output.put_line(v_p);
end;
 

[Rabbit]

Please post your new stored procedure code and the command you use to call it.

[bharthi]

Hi all , Thanks for your reply to my doubt.
but i got the answer for my procedure .Answer is ,
variable io_pro;
execute pro_name ('emailid','password',:io_pro);
print io_pro;

Thanks to every one again.