CODEWITHSUNDEEP
oracle-databasestored-procedures
yogesh patel
yogesh patel

Reputation: 375

How to create/call procedure in oracle 10g?

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?

Upvotes: 1

Views: 17520

Answers (4)

user3056758
user3056758

Reputation: 21

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!!

Upvotes: 2

DCookie
DCookie

Reputation: 43523

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.

Upvotes: 2

Sebas
Sebas

Reputation: 21532

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;

Upvotes: 1

Ben
Ben

Reputation: 52863

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.

Upvotes: 2

Related Questions