How to create/call procedure in oracle 10g?
Solution 1
I think it's procedure calling mistake!! Calling Should be like below:
SQL>var var1 varchar2(50);
SQL>var var2 varchar2(50);
SQL> exec getuserinfo(:var1, :var2);
SQL> print var1, var2;
Have Fun!!
Solution 2
Did you actually execute your create procedure statement? Did you get a "Procedure created." message? The fact that Oracle does not know of your getuserinfo procedure indicates to me that this statement was not performed.
Solution 3
You need to actually create the procedure, which you haven't done. You need a semi-colon after end
and if you're creating it in SQL*Plus you need to add /
to inform SQL*Plus that the block is finished:
create or replace procedure getUserInfo
( p_username out Users.username%TYPE,
p_password out Users.password%TYPE ) IS
BEGIN
select username,password into p_username,p_password from users;
END;
/
show error
It's always wise to add show error
afterwards as well so that any errors are returned to the console in an understandable format.
Solution 4
You need to specify the out parameters when you call the procedure.
DECLARE
x Users.username%TYPE;
y Users.password%TYPE;
BEGIN
getuserinfo(x, y);
DBMS_OUTPUT.PUT_LINE('username: ' || x || ', password: ' || y);
END;
yogesh patel
Currently i am working in NPR Infotech as a software developer in c++, java and Android.
Updated on November 22, 2022Comments
-
yogesh patel 12 months
I created a procedure in Oracle but am getting an error when I attempt to execute it. Below are listed the steps I am taking to create this procedure:
SQL> ed getuserinfo create or replace procedure getUserInfo ( p_username out Users.username%TYPE, p_password out Users.password%TYPE ) IS BEGIN select username,password into p_username,p_password from users where username='yogi'; END; / SQL> exec getuserinfo BEGIN getuserinfo; END; * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00201: identifier 'GETUSERINFO' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
What is the problem and how can I solve it? Olease can anyone help me?