469,342 Members | 5,554 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

how to execute this PL/SQL procedure?

20
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,
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE
  2.   PROCEDURE PRO_GET_SYSTEM_IsValidUser(
  3.                                        p_loginID in varchar2,
  4.                                        p_password in varchar2,
  5.                                        p_exists in out number
  6.                                       )
  7.     IS
  8.     BEGIN
  9.         SELECT  COUNT(*)
  10.           INTO  p_exists
  11.           FROM  employees
  12.           WHERE emailid = p_loginid
  13.             AND password = p_password
  14.             AND enabled = 'TRUE'
  15.             AND ROWNUM=1;
  16. END;
Thanks in advance.
Jun 30 '12 #1
17 6603
bharthi
20
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,
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE
  2. PROCEDURE PRO_GET_SYSTEM_IsValidUser(
  3. p_loginID in varchar2,
  4. p_password in varchar2,
  5. p_exists in out number
  6. )
  7. IS
  8. BEGIN
  9. SELECT COUNT(*)
  10. INTO p_exists
  11. FROM employees
  12. WHERE emailid = p_loginid
  13. AND password = p_password
  14. AND enabled = 'TRUE'
  15. AND ROWNUM=1;
  16. END;
Thanks in advance.
Jul 2 '12 #2
EXEC PRO_GET_SYSTEM_IsValidUser('emailid','password')
because password also charcter type so u should declare that within single codes...
just try this bharthi..............
Jul 2 '12 #3
bharthi
20
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.
Jul 2 '12 #4
Rabbit
12,516 Expert Mod 8TB
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.
Jul 2 '12 #5
bharthi
20
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.
Jul 3 '12 #6
Rabbit
12,516 Expert Mod 8TB
You may be storing 1 to output, but you are not inputting anything. There is no need to declare it as an input.
Jul 3 '12 #7
bharthi
20
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.
Jul 3 '12 #8
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.
Jul 3 '12 #9
Try executing like this:

execute pro_name('emailid','password',null);
Jul 3 '12 #10
bharthi
20
Hi, no its not executing, i getting error only.
Jul 3 '12 #11
can u post that error
Jul 3 '12 #12
bharthi
20
Error starting at line 21 in command:
execute PRO_GET_SYSTEM_IsValidUser ('velu@gmail.com','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:
Jul 3 '12 #13
have you declared the p_Exists as out number
Jul 3 '12 #14
bharthi
20
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.
Jul 3 '12 #15
rski
700 Expert 512MB
use something like that (you have to enable output)
Expand|Select|Wrap|Line Numbers
  1. declare 
  2. v_p number
  3. begin
  4. pro_name('emailid',password,v_p);
  5. dbms_output.put_line(v_p);
  6. end;
  7.  
Jul 3 '12 #16
Rabbit
12,516 Expert Mod 8TB
Please post your new stored procedure code and the command you use to call it.
Jul 3 '12 #17
bharthi
20
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.
Jul 4 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Ye Liu | last post: by
3 posts views Thread by marcmc | last post: by
1 post views Thread by chloe.crowder | last post: by
4 posts views Thread by benscribe | last post: by
5 posts views Thread by Chris Cowles | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.