Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle store procedure dies not work

Posted on 2011-10-14
4
Medium Priority
?
332 Views
Last Modified: 2012-05-12
I have a strange problem..

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

The body of the procedure has this UPDATE statement:

    UPDATE MyTab
       SET myid = CoreID
     WHERE
..................;

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

But If I change the above to:
CREATE OR REPLACE MyPkg Is
Procedure MyProd(Core_ID         In Varchar2);
And The body :

    UPDATE MyTab
       SET myid = Core_ID
     WHERE
.............;

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.?
Thanks.

0
Comment
Question by:toooki
  • 2
  • 2
4 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1900 total points
ID: 36971678
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?
0
 

Author Comment

by:toooki
ID: 36971742
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).?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36971751
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.
0
 

Author Comment

by:toooki
ID: 36971759
Thank you. I think what you suggested in the right way -- so will do.
Thanks again.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

569 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