Interesting puzzle. The error message would indicate the column HOR is invalid.
The error seems quite misleading given HOR is a parameter for the procedure.
I created a table called hua_algorithm_hor using the columns identified and used
it as both tables in the query, the procedure compiles and executes cleanly.
The tables listed are:
Smart3g.hua_algorithm_hor
Hua_algorithm_hor (if different from the schema specified above)
Hua_algorithm_min
Perhaps describing the tables might give some clue as to what the issue is.
In the alternative, a test might be to change the input parameter hor to
something else that couldn’t possibly normally exist like zzzzxxyyyy and
see if the error repeats. Perhaps hor is some kind of reserved word.
It might be helpful to also identify the exact command you are running that
generates the error.
My test was a simple: exec sp_hua_algorithm_hor(sysdate, 1);
It looks like it worked properly. As Michael indicated, a best practice is to
explicitly identify your date conversion… but if it had failed on that, it
would be a different error then originally identified.
Marvin: does the proper execution of the procedure mean you can no longer
duplicate the issue? Or did you change the variable name hor to something else
and it worked?
Or is there something we’re missing that you haven’t quite told us like: It
works in sqlplus but gives the error in a Toad editor?
It’s probably differences in NLS_DATE_FORMAT between the SQL*Plus session and
the TOAD session. That wouldn’t be a problem except for, as noted in my
earlier email, you are passing a string into a date parameter, and depending on
Oracle to convert the datatype for you. So modify your call to your procedure to
do explicit conversion on the date, run that version of the call in TOAD, and my
bet is you won’t have the problem anymore.
My best guess is that due to the language settings of the Oracle database (the
completion message was provided in Spanish) and possibly your desktop/Toad
language settings, hor is considered a reserved word.
Well… as best I can get from web translations:
Fecha = date (a reserved word)
Hora = hour (I don’t think this is a reserved word, but could be wrong)
Perhaps Toad is having difficulties parsing – the Toad devs now need to
reproduce the issue and see what’s what.
I’d actually be surprised if that fixed the particular issue identified.
The error wasn’t along the lines of “ORA-01843: not a valid month” which
is the type of error one would expect during a bad run-time conversion on a date
value.
It was “ORA-00904: “HOR”: invalid identifier”. In the action of that
error there is the phrase “It may not be a reserved word.”
Ok… after reviewing the photos, I’m now under the impression I completely misunderstood the original question.
I thought the error you were receiving was directly related to executing the stored procedure. As it turns out, now I understand what you wanted to do was extract the DELETE statement out of the stored procedure and run it stand alone as-is.
Now the error totally makes sense as well as the solution.
To answer your situation: yes, when you attempt to use parameters at the command line you need to tell the Oracle client (whether Toad, SQLPlus, SQL Developer, etc) that they are parameters. The ":" character is usually used to denote bind variables. You could also use the "&" character (this depends on if your "define" value is set to default or has been changed).
Roger S.
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.