Toad World® Forums

HOW TO EXECUTE DELETE RECORD.

HOW TO EXECUTE DELETE RECORD.

SPEC

PROCEDURE sp_hua_algorithm_hor (fec DATE, hor NUMBER);

BODY

PROCEDURE sp_hua_algorithm_hor (fec IN DATE, hor IN NUMBER)

IS

BEGIN

DELETE FROM SMART3G.HUA_ALGORITHM_HOR

WHERE FECHA =fec AND HORA =hor;

COMMIT;

INSERT INTO HUA_ALGORITHM_HOR (FECHA,

HORA,

CELL,

RNC,

PERIOD_DURATION,

CELLID,

DCCC_D2C_SUCC)

SELECT FECHA,

HORA,

CELL,

RNC,

SUM (PERIOD_DURATION),

CELLID,

SUM (DCCC_D2C_SUCC)

FROM HUA_ALGORITHM_MIN

WHERE fecha = fec AND hora = hor

GROUP BY FECHA,

HORA,

CELL,

RNC,

CELLID;

COMMIT;

END;

Note: When execute delete record send this message

ORA-00904: “HOR”: identificador no válido

Hey Marvin;

ORA-00904: “HOR”: identificador no válido

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);

Roger S.

[ Attachment(s) from Marvin Rios included below]

Microsoft Windows [Versión 5.2.3790]

© Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\soporte>cd…

C:\Documents and Settings>cd…

C:>sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Vie Mar 9 08:17:18 2012

Copyright © 1982, 2007, Oracle. All rights reserved.

Introduzca el nombre de usuario: SMART3G

Introduzca la contrase±a:

Conectado a:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec AGREGA_ESTAD_PKG.sp_hua_algorithm_hor (‘07-NOV-11’,1);

Procedimiento PL/SQL terminado correctamente.

Try

SQL> exec AGREGA_ESTAD_PKG.sp_hua_algorithm_hor (to_date(‘07-NOV-11’,‘dd-mon-yy’),1);

Never depend on implicit conversion of dates.

Regards,

Mike McAllister
Principal Systems Engineer
Decypher
DSN: 487-3751
Commercial: (210) 652-3751
Cell: (512) 423-7447
Email: michael.mcallister.2.ctr.au@us.af.mil
smime.p7s (5.59 KB)

C:>sqlplus

Procedimiento PL/SQL terminado correctamente.

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?

Roger S.

[ Attachment(s) from Marvin Rios included below]

Yes in Toad send these errror ORA-00904: “HOR”: identificador no válido

Becouse?

Thank you

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.

Regards,

Mike McAllister

Principal Systems Engineer

Decypher

DSN: 487-3751

Commercial: (210) 652-3751

Cell: (512) 423-7447

Email:
michael.mcallister.2.ctr.au@us.af.mil
smime.p7s (5.59 KB)

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.

Roger S.

differences in NLS_DATE_FORMAT

my bet is you won’t have the problem anymore

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.”

Roger S.

[ Attachment(s) from Marvin Rios included below]

Perform several tests and wrote the code as follows and it worked in TOAD

DELETE FROM SMART3G.HUA_ALGORITHM_HOR

WHERE FECHA = : fec AND HORA = : hor;

add two points

view attach File.

[ Attachment(s) from Marvin Rios included below]

I’ m use tool SQL TRACKER and result is

Good Morning Marvin;

FECHA = :fec AND HORA = :hor

add two points

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.