Home » Developer & Programmer » Forms » ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 (Froms 11gR2, Windows7)
ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635865] Fri, 10 April 2015 04:58 Go to next message
sohailnawazsabir
Messages: 22
Registered: April 2007
Location: Saudi Arabia
Junior Member

The idea is I want to save multiple documents (PDFs) against the specified PO.

I am using the below procedure; its giving me the subject error message "OP" is PO Type, please see attached screen shot and fmb. Thanks

PROCEDURE UPLOAD_DB
IS
l_success boolean;
BEGIN

l_success := webutil_file_transfer.Client_To_DB_with_progress
(clientFile => :SH_PO_DETAILS_ATTA.file_name
,tableName => 'SH_PO_DETAILS_ATTA'
,columnName => 'SH_PO_DETAILS_ATTA.PDF_BLOB'
,whereClause => 'SH_PO_DETAILS_ATTA.ID= '||'121112-VAI-OP-1'
,progressTitle => 'Upload to Database in progress'
,progressSubTitle => 'Please wait'
,asynchronous => false
,callbackTrigger => null
);

if l_success then
:System.Message_Level := '15';
commit;
:System.Message_Level := '0';
message('File uploaded successfully into the Database');
go_item('SH_PO_DETAILS_ATTA.PUSH_BUTTON82');
else
message('File upload to Database failed');
end if;
exception when others then
message('File upload failed: '||sqlerrm);

END;

Dynamic Where clause

,whereClause => 'SH_PO_DETAILS_ATTA.ID='||:TETRA_PO_HEADERS.PO_NUMBER||' and '
||'SH_PO_DETAILS_ATTA.PO_TYPE='||:TETRA_PO_HEADERS.PO_TYPE||' and '
||'SH_PO_DETAILS_ATTA.COMP='||:TETRA_PO_HEADERS.COMP||' and '
||'SH_PO_DETAILS_ATTA.ATT_SEQ = '||:SH_PO_DETAILS_ATTA.ATT_SEQ

Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635866 is a reply to message #635865] Fri, 10 April 2015 05:04 Go to previous messageGo to next message
Littlefoot
Messages: 21623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The only OP is here:
,whereClause => 'SH_PO_DETAILS_ATTA.ID= '||'121112-VAI-OP-1' 

Try to enclose the right side of the = sign into single quotes, i.e.
,whereClause => 'SH_PO_DETAILS_ATTA.ID= '||''121112-VAI-OP-1'' 
Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635868 is a reply to message #635866] Fri, 10 April 2015 05:19 Go to previous messageGo to next message
sohailnawazsabir
Messages: 22
Registered: April 2007
Location: Saudi Arabia
Junior Member

whereClause => 'SH_PO_DETAILS_ATTA.ID= '||''121112-VAI-OP-1''

The above code does't work.

Please see attached my expedite.fmb form. Kindly reciew the Upload_DB and Download_DB procedures.
  • Attachment: expedite.fmb
    (Size: 436.00KB, Downloaded 300 times)
Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635870 is a reply to message #635868] Fri, 10 April 2015 05:23 Go to previous messageGo to next message
Littlefoot
Messages: 21623
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
sohailnawazsabir wrote on Fri, 10 April 2015 12:19
whereClause => 'SH_PO_DETAILS_ATTA.ID= '||''121112-VAI-OP-1''

The above code does't work.

What "doesn't work" means? I guess you got another error message. If so, which one?

I can't review your form, it wouldn't work anyway on my system (lack of tables, procedures, whatever); I suppose you posted relevant code here, right?
Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635871 is a reply to message #635870] Fri, 10 April 2015 05:34 Go to previous messageGo to next message
sohailnawazsabir
Messages: 22
Registered: April 2007
Location: Saudi Arabia
Junior Member

PROCEDURE UPLOAD_DB
IS
l_success boolean;
BEGIN
l_success := webutil_file_transfer.Client_To_DB_with_progress
(clientFile => :SH_PO_DETAILS_ATTA.file_name
,tableName => 'SH_PO_DETAILS_ATTA'
,columnName => 'SH_PO_DETAILS_ATTA.PDF_BLOB'
,whereClause => 'SH_PO_DETAILS_ATTA.ID='||:TETRA_PO_HEADERS.PO_NUMBER||' and '
||'SH_PO_DETAILS_ATTA.PO_TYPE='||:TETRA_PO_HEADERS.PO_TYPE||' and '
||'SH_PO_DETAILS_ATTA.COMP='||:TETRA_PO_HEADERS.COMP||' and '
||'SH_PO_DETAILS_ATTA.ATT_SEQ = '||:SH_PO_DETAILS_ATTA.ATT_SEQ
,progressTitle => 'Upload to Database in progress'
,progressSubTitle => 'Please wait'
,asynchronous => false
,callbackTrigger => null
);
if l_success then
:System.Message_Level := '15';
commit;
:System.Message_Level := '0';
message('File uploaded successfully into the Database');
go_item('SH_PO_DETAILS_ATTA.PUSH_BUTTON82');
else
message('File upload to Database failed');
end if;

exception when others then
message('File upload failed: '||sqlerrm);
END;
Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635878 is a reply to message #635871] Fri, 10 April 2015 07:25 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

Looks like you're still missing some quotes. All varchar values in the where clause need to be wrapped in quotes the same way LF showed you above.
Assign the string you're sending for the whereclause parameter to a variable, display it on screen and check that it makes sense.
Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635881 is a reply to message #635878] Fri, 10 April 2015 08:28 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
...
-- Your PO_NUMBER is a string, but you don't have enough Quotes!!
 ,whereClause => 'SH_PO_DETAILS_ATTA.ID='''||:TETRA_PO_HEADERS.PO_NUMBER||''' and ' 
-- I'm going to guess that your PO_TYPE is also a String - again, not enough Quotes!!
 ||'SH_PO_DETAILS_ATTA.PO_TYPE='''||:TETRA_PO_HEADERS.PO_TYPE||''' and ' 
...

You're getting an ORA-06512 - Numeric or Value Error. This is almost always a value conversion error. In your case, because of the lack of Quotes, your String is being treated as a NUMERIC value and the "Alpha" characters in your PO Number can't be converted to a number - hence the ORA-06512 error.

Craig...
Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635885 is a reply to message #635881] Fri, 10 April 2015 09:21 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Numeric or value error is 6502.
6512 is a backtrace indicating the line at which the original error - 904 - got caught.
OP is getting 904 because anything in a SQL statement that isn't either numeric or quoted is assumed to either be a keyword or the name of an object/column/variable.
Re: ORA-00904: "OP" invalid identifier ORA-06512: at :WEBUTIL.WEBUTIL_DB", line 50 [message #635946 is a reply to message #635885] Mon, 13 April 2015 09:45 Go to previous message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Doh! I hate it when my memory fails...CM you are correct, the ORA-06502 is the value error. The ORA-00904 still appears to be caused by the comparison values not being "Quoted" properly so, as you stated, the comparison values are being treated as objects instead of strings.

Craig...
Previous Topic: Unable to update datablock with row fetched from LOV
Next Topic: Oracle forms impact due to OS upgrade
Goto Forum:
  


Current Time: Wed Nov 25 22:10:41 CST 2020