Home » SQL & PL/SQL » Client Tools » Procedural Option...???
icon4.gif  Procedural Option...??? [message #270562] Thu, 27 September 2007 09:28 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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 #270571 is a reply to message #270562] Thu, 27 September 2007 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another guess: execute catproc.sql

Regards
Michel
Re: Procedural Option...??? [message #270580 is a reply to message #270562] Thu, 27 September 2007 10:56 Go to previous messageGo to next message
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
Member
what about catproc.sql script...??
Re: Procedural Option...??? [message #270585 is a reply to message #270580] Thu, 27 September 2007 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
$ORACLE_HOME/rdbms/admin/catproc.sql

Regards
Michel
Re: Procedural Option...??? [message #270586 is a reply to message #270570] Thu, 27 September 2007 11:01 Go to previous messageGo to next message
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
Member
The procedure is the ctx_ddl.sync_index
Re: Procedural Option...??? [message #270587 is a reply to message #270585] Thu, 27 September 2007 11:02 Go to previous messageGo to next message
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
Member
Sorry...I mean what is for...
Re: Procedural Option...??? [message #270602 is a reply to message #270570] Thu, 27 September 2007 11:27 Go to previous messageGo to next message
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 #270609 is a reply to message #270602] Thu, 27 September 2007 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
:= 'begin ctx_ddl.sync_index(''my_index'', ''10M''); end;';

Regards
Michel

Re: Procedural Option...??? [message #270644 is a reply to message #270609] Thu, 27 September 2007 13:26 Go to previous messageGo to next message
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 #270646 is a reply to message #270644] Thu, 27 September 2007 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My car is not working. Why?
(Sorry Ana, I took your line. Smile )

Regards
Michel
Re: Procedural Option...??? [message #270915 is a reply to message #270646] Fri, 28 September 2007 07:10 Go to previous messageGo to next message
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 #270986 is a reply to message #270915] Fri, 28 September 2007 11:08 Go to previous messageGo to next message
amazing
Messages: 46
Registered: September 2007
Location: Venezuela
Member
Hi. I resolve the problem by grant execute any procedure to my_user. Thanks for the collaboration.
Re: Procedural Option...??? [message #271046 is a reply to message #270986] Fri, 28 September 2007 15:59 Go to previous messageGo to next message
Frank
Messages: 7880
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 #271053 is a reply to message #271046] Fri, 28 September 2007 18:07 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
All of them? Laughing

(Sorry Couldn't resist)

[Updated on: Fri, 28 September 2007 18:07]

Report message to a moderator

Re: Procedural Option...??? [message #271313 is a reply to message #270986] Mon, 01 October 2007 04:31 Go to previous message
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.
Previous Topic: Generating Insert scripts for BLOB
Next Topic: Tora on open suse 10.2
Goto Forum:
  


Current Time: Sat Dec 10 20:47:55 CST 2016

Total time taken to generate the page: 0.05082 seconds