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?
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?).
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.
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.
create or replace procedure lasttouch(p_user_name in varchar2) as
UPDATE ADMIN_DATA set time = sysdate where user_name = p_user_name;
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.
If you are not using a stored procedure to run the update statement, then you can specify a bind using a colon character.
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.
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:
CREATE OR REPLACE procedure lasttouch(user_name in testparameters.user_name%type) as
execute immediate 'update testparameters set last_accessed=sysdate where user_name = :user_name' using user_name;
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