Home » SQL & PL/SQL » SQL & PL/SQL » Procedure created by PL/SQL won't compile (Oracle 10g)
Procedure created by PL/SQL won't compile [message #323766] Thu, 29 May 2008 07:11 Go to next message
phuksi
Messages: 4
Registered: May 2008
Junior Member

Hi,

Because of a long story, I was forced to create a procedure that creates a long string statement that can be used to create another procedure (to create procedures dynamically). When I execute the statement using execute immediate v_statement; (to create a new procedure) it does execute successfully but the new procedure won't compile.

If I then try to compile it by using
alter procedure [procedure_name] compile; it does nothing.

If I then use toad and look at the procedure script in the database, copy it to clipboard and paste to execute, it goes in just fine.

So in theory there are no syntax errors. But there has to be a difference how the compilation is done when the statement is executed from plsql or when execulted manually.

Any ideas how to get it compile properly when using execute immediate v_statement; ?

Thanks, p
Re: Procedure created by PL/SQL won't compile [message #323770 is a reply to message #323766] Thu, 29 May 2008 07:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean when you say 'Won't compile'

Does it error?
Does it produce an invalid procedure?
Do small pink mice fly out of your Db server and circle the lights singing 'Allelulia' (thats probably one for Metalink realistically. Or Rentokill)

Can you show us a very short example of the Pl/Sql that will create a procedure in TOAD, but not from Execute Immediate?

What do you mean when you say that 'Alet Procedure..' does nothing.
What does it return?
What is the status of the procedure from USER_OBJECTS before and after the ALTER PROCEDURE command.

Realistically, if you're getting the code for the procedure from TOAD, it sounds like it has ben created successfully - otherwise, how can TOAD see the source code?

[Updated on: Thu, 29 May 2008 07:21]

Report message to a moderator

Re: Procedure created by PL/SQL won't compile [message #323774 is a reply to message #323766] Thu, 29 May 2008 07:29 Go to previous messageGo to next message
phuksi
Messages: 4
Registered: May 2008
Junior Member
When the plsql code is executed it errors saying 'Procedure created successfully with compilation errors.' So id does create an invalid procedure. The procedure is there with state INVALID.

When I execute alter procedure... it returns a window in Toad with title TOAD ERROR but no error message, just a blank form...

The state before alter statement is INVALID.

When I compare the procedure initially and after I copy paste and execute it manually the only difference is AUTHID. When the procedure is created the AUTHID has value of CURRENT_USER. After I recreate the procedure by copypaste the AUTHID is DEFINER.

Would that explain it?

- p
Re: Procedure created by PL/SQL won't compile [message #323777 is a reply to message #323774] Thu, 29 May 2008 07:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you get at the point where you've first created it, when you execute this:
SELECT * FROM USER_ERRORS WHERE name = <whatever your procedure is called>


What happends when we get the GUI out of the way, and you issue the 'ALTER PROCEDURE ... COMPILE' from SQL*Plus
Re: Procedure created by PL/SQL won't compile [message #323778 is a reply to message #323774] Thu, 29 May 2008 07:36 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Forget TOAD and do this all in SQL*Plus and see what results you get.
Re: Procedure created by PL/SQL won't compile [message #323781 is a reply to message #323777] Thu, 29 May 2008 07:47 Go to previous messageGo to next message
phuksi
Messages: 4
Registered: May 2008
Junior Member

Below is what is in the user_errors after running the procedure.


-p

----

NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
TEST_PROCEDURE PROCEDURE 1 1 53 PLS-00103: Encountered the symbol "" when expecting one of the following:

begin function package pragma procedure subtype type use
<tunniste> <erotinta käyttävä tunniste lainausmerkeissä>
form current cursor external language
ERROR 103

Re: Procedure created by PL/SQL won't compile [message #323789 is a reply to message #323781] Thu, 29 May 2008 08:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, so there's a syntax error in your code somewhere.

Run this:
select text from user_source where name = <procedure name> order by line;
and cut and paste the results.
Previous Topic: table redefinition
Next Topic: Need a clarification regarding value sets
Goto Forum:
  


Current Time: Sat Dec 10 14:45:07 CST 2016

Total time taken to generate the page: 0.13530 seconds