Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: procedure input parameters

    Hi,

    I am new to oracle, have been working in sybase since long time. please help..

    In stored procedures, how can I use input parameter same as column names in the table. Example my table has user_name as a column, I would like use same as input parameter like "user_name IN VARCHAR2" and my procedure code is like this:

    UPDATE ADMIN_DATA set time = current_timestamp where user_name = username;

    In sybase, we can use this with @ symbol like user_name = @username;
    Is this possible in Oracle?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I can't tell for sure, but I think that you can't do that in Oracle.

    I, for example, use certain "coding conventions" and precede parameter names with "par_" (so, in your case, it would be "par_user_name"). This also helps avoiding confusion (what a nightmare, having a table, column and parameter names the same!. What is what?).

  3. #3
    Join Date
    Sep 2010
    Posts
    2
    Thanks for your reply.
    In my case, it is helpful to have this option in oracle too, as I am using spring framework and I pass the whole input values bean(converted to Map) to the procedures. i might have to redefine my beans or append "par_" to the keys while generating the map.

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I think you are asking about bind variables. The @ symbol used in sybase sets up a bind variable for the SQL. Oracle allows for the same thing, but it happens implicitly in PLSQL.
    For example:
    Code:
    create or replace procedure lasttouch(p_user_name in varchar2) as
    begin
    
       UPDATE ADMIN_DATA set time = sysdate where user_name = p_user_name;
    
    end;
    /
    
    exec lasttouch('dayneo');
    In the code above, the row in ADMIN_DATA that has the user_name of dayneo would be updated with the current system date and time. You will notice that I prefixed my parameter name using p_. This is so that oracle can distinguish between the column called user_name and the parameter. I could just as easily have used param_ as a prefix value, or any other value that I feel like.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    If you are not using a stored procedure to run the update statement, then you can specify a bind using a colon character.
    E.g.
    Code:
    UPDATE ADMIN_DATA set time = sysdate where user_name = :user_name;
    In the example above, the colon tells oracle that this is a bind variable. You would likely set the bind variable value the same way that you did with sybase. Note that using the bind variable in this way, you can use a variable name that is the same as the column name.

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Wink

    Here's one more example... If you are in fact using stored procedures, and you absolutely have to have parameters that are named the same as your column, then you could use:
    Code:
    CREATE OR REPLACE procedure lasttouch(user_name in testparameters.user_name%type) as
    begin
    execute immediate 'update testparameters set last_accessed=sysdate where user_name = :user_name' using user_name;
    end;
    /
    Note though that this is should be used as a last resort. The above code makes use of EXECUTE IMMEDIATE which compiles SQL on the fly and executes it. The USING statement tells it to use the parameter value and ensures that bind variables are being used through the use of the colon.

    Performance may become an issue here depending on your Oracle Server and Session parameters (I would advise the use of session cached cursors if you use the above method).
    Last edited by dayneo; 09-07-10 at 14:03. Reason: More accurate code sample

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by styl9099 View Post

    UPDATE ADMIN_DATA set time = current_timestamp where user_name = username;

    In sybase, we can use this with @ symbol like user_name = @username;
    Is this possible in Oracle?
    How about this:
    Code:
    UPDATE ADMIN_DATA a set time = current_timestamp where a.user_name = username;

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The question was: how to do something when a procedure parameter name is EQUAL to a column name.

    Therefore, (parameter) vs. (column)
    "user_name" <> "username" or
    "user_name" <> "param_user_name" or
    "user_name" <> "p_user_name" or ... - no problem.

    But, if "user_name" = "user_name", that's the problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •