Oracle store procedure dies not work

Posted on 2011-10-14
Last Modified: 2012-05-12
I have a strange problem..

On Oracle 11gR2 database I have a stored procedure (inside a package):
Procedure MyProd(CoreID         In Varchar2);

The body of the procedure has this UPDATE statement:

    UPDATE MyTab
       SET myid = CoreID

The procedure (in package) compiles and runs -- no errors. But it does not Update the table.

But If I change the above to:
Procedure MyProd(Core_ID         In Varchar2);
And The body :

    UPDATE MyTab
       SET myid = Core_ID

It Works.

I tried is numerous times and it is working this strange.
"CoreID" does not seem to be a reserved work in Oracle. It's strange why it is happening.
I need to keep the input parameter as Core_ID as it is called that way in the front-end code and I cannot change that easily..
Is there any other reason that could cause the issue.?

Question by:toooki
    LVL 76

    Accepted Solution

    Does the table maybe have a column called coreid?

    If not, there may be some other object with that name that is confusing things.  Maybe another function in the package?

    Author Comment

    Thank you...
    Yes the table on which UPDATE is made has a column named "coreid" ... That is the reason then!! Can I still use that parameter name (same as one of the table column names).?
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Off the top of my head I cannot remember if I have ever seen a way around this but that is the reason.

    It is a bad coding practice to mix and match variable and object names.  You should have a naming convention of some type.

    For example 'variables' start or end with v_ or _v.

    Author Comment

    Thank you. I think what you suggested in the right way -- so will do.
    Thanks again.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    This video shows how to recover a database from a user managed backup
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now