#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2006
    Posts
    11
    Rep Power
    0

    Executing a Function in Oracle


    Hi,

    I have created a Package Body and Package Spec for a function to select a username and a password from a table and return the username.

    The code i have created is this:-
    CREATE OR REPLACE PACKAGE BODY USER_LOGIN
    AS

    FUNCTION user_select (USERNAME_IN VARCHAR2,
    PASSWORD_IN VARCHAR2)
    RETURN VARCHAR2 IS
    USERNAME_OUT VARCHAR2(12);
    BEGIN
    SELECT USERNAME
    INTO USERNAME_OUT
    FROM USERS
    WHERE USERNAME = USERNAME_IN
    AND PASSWORD = PASSWORD_IN;
    RETURN USERNAME_OUT;
    END user_select;

    END USER_LOGIN;
    /

    The package body and spec compiles successfully but i am having trouble when i execute this function. I am trying to do this :-

    VARIABLE RETVAL VARCHAR2(12)

    EXEC User_login.user_select('HELLO','HELLO',:RETVAL );

    but i am getting the following error

    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'USER_SELECT'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Can anyone please help me?
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2006
    Posts
    11
    Rep Power
    0
    Do i not need to worry about the return parameter when using the
    SELECT USER_LOGIN.user_select('PIPPO', 'PLUTO') FROM dual; statement?

    Also is the same code to be used in Oracle 10G?

    Thanks!
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    538
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    declare
    result varchar2(10);
    BEGIN
    result:= USER_LOGIN.user_select('hello','hello1:'); DBMS_OUTPUT.PUT_LINE(result);
    END;
    /
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0
    declare
    result varchar2(10);
    BEGIN
    result:= USER_LOGIN.user_select('hello','hello1:'); DBMS_OUTPUT.PUT_LINE(result);
    END;
    /

    or

    VARIABLE result varchar2(10)
    EXEC :result := USER_LOGIN.user_select('hello','hello1:');

    print result

    Comments on this post

    • debasisdas disagrees : This thread was started long before you have ever heard of Oracle. Please do not dig dead threads.

IMN logo majestic logo threadwatch logo seochat tools logo