Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00907 missing right parenthesis
ORA-00907 missing right parenthesis [message #279619] Fri, 09 November 2007 07:11 Go to next message
colesg
Messages: 11
Registered: October 2007
Location: London
Junior Member

Hi

I have a SP that removes records from a table and updates an aggregate table after the records have been deleted from the underlying data table.

I have a user that has execute grant on the SP. My issue is that sometimes when the user runs the SP, he gets the above error.

There are 3 parameters passed.

1) reconcile_date_lcl
2) lcunit_id
3) message_id - this can be a list of message_ids seperated by a comma.

When I run the same SP with the same parameters, it works fine.

I believe the error message is a red herring and it may be something to do with permissions, but this seems strange as it works sometimes for the user and works everytime for me. I have searched on the web for the error and have found that double quotes may cause this, but I never use them.

Can anyone suggest any solutions or ideas why this would happen? I can attach the SP if required.

Cheers
Gary
Re: ORA-00907 missing right parenthesis [message #279624 is a reply to message #279619] Fri, 09 November 2007 07:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It will be more useful if you could copy, paste the error and the inputs supplied to the stored proc which causes this error. Without that i really doubt anybody can help you.

Regards

Raj
Re: ORA-00907 missing right parenthesis [message #279627 is a reply to message #279619] Fri, 09 November 2007 07:39 Go to previous messageGo to next message
colesg
Messages: 11
Registered: October 2007
Location: London
Junior Member

Hi

If you look at the message title you will see the error message. As for the parameters passed, not sure they will mean anything to you but here they are.

p_reconcile_date_lcl := timestamp '2007-11-03 00:00:00';
p_lcunit_id := 55481;
p_message_id := '114d69a1-2782-4088-b1f4-26300a70552d
,cb793e87-d24a-42fc-b79a-705fee73e2f4
,de20657c-6517-462f-98ae-25b5125cb0b2
,1dbe06bf-46f4-4b79-8594-caa7079158ed
,584bacc9-0e13-4d5c-8c79-7193a2404326
,b7f3c51a-ac3b-4cb1-9367-60ef31f3558a
,b18d0c3d-f7a2-439f-aa7e-14643f4559db
,49c92bcd-ea0a-4414-971f-7f3cbe69dabe';



I have attached the package as well. SP to look at is sp_manual_cash_fact_deletions.

Cheers
Gary
Re: ORA-00907 missing right parenthesis [message #279634 is a reply to message #279627] Fri, 09 November 2007 07:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A guess : could the line-break in p_message_id generate problems when the statement for the "execute immediate"s is created?

Maybe a user supplies something into those "execute immediate"s that breaks them?

To look further we need the complete error message, which should have stated in which line the error occurred.

Also, include the SP as an

select * from user_source where name ='PKG_MANUAL_CORRECTIONS'

with the line numbers.

[Updated on: Fri, 09 November 2007 08:01]

Report message to a moderator

Re: ORA-00907 missing right parenthesis [message #279641 is a reply to message #279619] Fri, 09 November 2007 08:27 Go to previous messageGo to next message
colesg
Messages: 11
Registered: October 2007
Location: London
Junior Member

Hi

The title is the full error message from SQLERRM, so I dont know the line number. Giving the line numbers for each line of the PKG is of no use without the error line number.

The line breaks in the p_message_id were put in by me to make it easier to read. Sorry should of mentioned that.

I am currently trying to replicate the error on my machine using the users login details, but unfortunately it is a production box and the parameters I have run for have worked ok and have now run out of true parameters to test. This also does not happen on our DEV and UAT machines which have the same user grants and roles, so cannot test on their.

Thanks for all the help so far. I will come back to this on Monday when I have production data to use.

Cheers
Gary
Re: ORA-00907 missing right parenthesis [message #279653 is a reply to message #279641] Fri, 09 November 2007 09:08 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One thing you could try Monday :

Wrap all the "execute immediate" into begin/end blocks with exception handlers, and include the executed SQL string in the raised exception before the exception is raised.

That's what I do when I can't follow Rule 1 - 3 for execute immediate.

Rule : 1. Don't use it.
Rule : 2. Don't use it.
Rule : 3. Don't use it.
Rule : 4. If you HAVE to use it, at least have a way to find out which SQL statement was executed when it errors out. Wink

Previous Topic: FMT File copied into program at Compile Time
Next Topic: Stick the contents of a cursor into a temp table
Goto Forum:
  


Current Time: Wed Dec 07 22:07:18 CST 2016

Total time taken to generate the page: 0.07640 seconds