Invalid procedures and functions created in oracle

[GoogleCodeExporter]

What steps will reproduce the problem?
1. execute a change script that creates an oracle function or procedure
2. note that the function is created
3. however executing the function produces an error message that the procedure 
is in an invalid 
state (we then have to open it and recompile it)

This is executing dbdeploy on Windows.

See 
http://groups.google.com/group/db-deploy-users/browse_thread/thread/d342fea8a23d
ae28 
for more information.

Original issue reported on code.google.com by gtack...@googlemail.com on 10 May 2010 at 8:37

[GoogleCodeExporter]

desgrange says:

We found out that it's link to newline characters, we are executing db 
deploy from windows computers so the call 
"currentSql.appendNewLine();" in 
com.dbdeploy.database.QueryStatementSplitter, add a windows newline (\r 
\n). If we change that to "currentSql.append("\n");" it works (store 
procedure is correctly compiled, no problem). 

Original comment by gtack...@googlemail.com on 10 May 2010 at 8:37

[GoogleCodeExporter]

When will fix this be available in a new release?
If it won't be for any day soon, maybe you can give me instructions on how to 
compile 
the source code?

Thanks in advance.

Original comment by Provoos...@gmail.com on 10 May 2010 at 7:19

[GoogleCodeExporter]

Hi,

I've had this issue with line endings when executing anonymous blocks in Oracle 
on Windows. I received a PLS-00103 error (which is apparently a known Oracle 
issue you can get with windows style line endings).

 - I saw in the discussion http://groups.google.com/group/db-deploy-users/browse_thread/thread/d342fea8a23dae28 

"I'm happy to apply the change you suggest Laurent, though I dread to
think what other databases will make of it :(  I've raised
http://code.google.com/p/dbdeploy/issues/detail?id=43 to track. "

At the moment the line ending is picked up from the Java property 
"line.separator".

Do you think it might be an idea to add an option to the ant task to specify 
the line ending to use. So we could specify "LF", "LFCR", "CR", otherwise it 
defaults to the system setting?

I patched our version of dbdeploy to just set the system property as "LF" and 
it fixed the issue. I can submit a patch if you think its a good idea.

Original comment by trevor.u...@gmail.com on 4 Mar 2011 at 9:45

[GoogleCodeExporter]

Implemented as suggested by trevor.u... in r151.

Dbdeploy now supports a lineending parameter, which is only used when applying 
directly to the database. This can be set to "platform" - same as current 
behaviour, uses platform default line ending - or cr, crlf or lf.

Expected use case is only to workaround this oracle driver bug.

Needs doc update.

Original comment by gtack...@googlemail.com on 14 Mar 2011 at 5:03

• Changed state: Started
• Added labels: Component-Docs

[GoogleCodeExporter]

Thats great.

I just built it and applied it to our project and its working well.

Thanks very much,
Trevor

Original comment by trevor.u...@gmail.com on 15 Mar 2011 at 9:50

[GoogleCodeExporter]

Great thanks Trevor!

I'm just preparing a proper 3.0M3 release - including sync to maven central - 
which should hopefully be available within the next few days.

Thanks for the suggestion :)

Original comment by gtack...@googlemail.com on 15 Mar 2011 at 11:26

[GoogleCodeExporter]

Docs updated

Original comment by gtack...@googlemail.com on 15 Mar 2011 at 4:24

• Changed state: Done
• Added labels: Milestone-Release3.0M3
• Removed labels: Milestone-Release3.0

[GoogleCodeExporter]

I run my dbdeploy script to install the following Oracle function,

CREATE OR REPLACE FUNCTION TIME_DIFF
 (P_WHAT IN VARCHAR2, P_D1 IN DATE, P_D2 IN DATE)
 RETURN NUMBER AS L_RESULT NUMBER; 
BEGIN
  SELECT (P_D2-P_D1) * DECODE(UPPER(P_WHAT), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL);
  RETURN L_RESULT;
END;

and got this error,

[sql] Executing resource: \dbdeploy-3.0M3\mydeployment\all-deltas.sql
[sql] Failed to execute:  BEGIN SELECT (P_D2-P_D1) * DECODE(UPPER(P_WHAT), 
'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL)

BUILD FAILED
java.sql.SQLException: ORA-06550: line 1, column 94:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 1, column 93:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the 
following: ;

I was using Milestone-Release3.0M3 in MS Windows.

I've found that whether I specified the "lineending" parameter as "lf", "cr", 
"crlf" or "platform,  the generated delta scripts were delimited by a newline.

Original comment by raphael...@gmail.com on 12 Dec 2011 at 8:57

[GoogleCodeExporter]

Hi,

I still have the issue and we use dbdeploy-3.0M3 with Oracle. Has this been 
corrected

Original comment by mahendra...@gmail.com on 9 Sep 2013 at 3:09