How to create/call procedure in oracle 10g?

17,398

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;
Share:
17,398
yogesh patel
Author by

yogesh patel

Currently i am working in NPR Infotech as a software developer in c++, java and Android.

Updated on November 22, 2022

Comments

  • yogesh patel
    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?