 Procedural Option...??? [message #270562] |
Thu, 27 September 2007 09:28  |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
|
Hi. I'm dealing with an ORA-00900 error. And I read that the solution is to install the Procedural Option. How can I do it...?
|
|
|
|
| Re: Procedural Option...??? [message #270563 is a reply to message #270562] |
Thu, 27 September 2007 09:32  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>I'm dealing with an ORA-00900 error. And I read that the solution is to install the Procedural Option
1) EXACTLY what were you doing which caused the ORA-00900?
2) Where did you read about "Procedural Option"; what ever that may be?
00900, 00000, "invalid SQL statement"
[Updated on: Thu, 27 September 2007 09:32] by Moderator Report message to a moderator |
|
|
|
| Re: Procedural Option...??? [message #270567 is a reply to message #270563] |
Thu, 27 September 2007 09:45 |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
I want to execute a stored prcedure inside a trigger. But return an PLS-00201 identifier 'my procedure' must be decalred error. So I made a string and used the execute immediate statment and return an ORA-00900:invalis sql statment.
Then I search the ORA-00900 error on the web http://ora-00900.ora-code.com/:
ORA-00900: invalid SQL statement
Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.
Action: Correct the syntax or install the Procedural Option.
|
|
|
|
| Re: Procedural Option...??? [message #270569 is a reply to message #270562] |
Thu, 27 September 2007 09:50 |
|
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>I want to execute a stored prcedure inside a trigger. But return an PLS-00201 identifier 'my procedure' must be decalred error.
Correct your programming error.
Have you manually successfully tested your procedure?
[Updated on: Thu, 27 September 2007 09:51] by Moderator Report message to a moderator |
|
|
|
| Re: Procedural Option...??? [message #270570 is a reply to message #270567] |
Thu, 27 September 2007 09:53 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
So (now remeber that since you have supplied next to no info, this is a guess) you have put in
EXECUTE IMMEDIATE 'my_proc_name(parameter_list)';
Well, the error that you get
"The statement is not recognized as a valid SQL statement. "
is because
my_proc_name(parameter_list)
is ... well.. not a valid sql statement.
Now maybe you can help me. My car doesn't work it makes a funny noise when I change speed. What should I do to fix it?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Procedural Option...??? [message #270602 is a reply to message #270570] |
Thu, 27 September 2007 11:27 |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
This is the source:
create or replace trigger my_trigger
after update or delete on my_table
for each row
declare
synchronizaton_command string(100) := 'ctx_ddl.sync_index(' || '''my_index''' || ', ' || '''10M''' || ');';
begin
execute immediate synchronizaton_command;
end TRG_DCOMPANNIA_A_INSTR;
when I perform DML on my_table return the ORA-00900 error.
|
|
|
|
|
|
| Re: Procedural Option...??? [message #270644 is a reply to message #270609] |
Thu, 27 September 2007 13:26 |
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
|
Member |
|
|
I executed catproc.sql script, execute the execute immediate command like
:= 'begin ctx_ddl.sync_index(''my_index'', ''10M''); end;';
and is still not working...
what happen here...???
|
|
|
|
|
|
| Re: Procedural Option...??? [message #270915 is a reply to message #270646] |
Fri, 28 September 2007 07:10 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
|
Can you cut and paste the whole error stack that you're getting, and can you also cut and paste the Banner that displays in SQL*Plus when you start it up.
|
|
|
|
|
|
| Re: Procedural Option...??? [message #271046 is a reply to message #270986] |
Fri, 28 September 2007 15:59 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Resolved this problem and introduced a zillion possible new ones.
Do you have an idea of exactly how many and which procedures are included in the 'execute ANY procedure' privilege???
|
|
|
|
|
|
| Re: Procedural Option...??? [message #271313 is a reply to message #270986] |
Mon, 01 October 2007 04:31 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That really isn't a good idea.
You have opened up soooooo many security loopholes with that Grant that it's really scary.
Your user is now one Google from being Dba on your database.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)