I'm attempting to read an Oracle PL/SQL procedure out of a text file, execute it in a Sql Connection to create the procedure in Oracle and then run it. When I open SQL Developer (it's a free Oracle Client) to view the procedure it has a red x over the procedure name indicating that it was not compiled correctly and if I try to run it I get the message that it was not compiled correctly. I can then compile it from SQL Developer and it works, but if I run the sql statement "alter procedure <procedure name> compile;" it does nothing.
Is there anything special I should know about creating a procedure from a Groovy Sql connection in an oracle db?
here is some sample code from what I'm trying to do:
sql = Sql.newInstance("jdbc:oracle:thin:@${host}:${port}:${instance}",
User, Password, 'oracle.jdbc.driver.OracleDriver')
File entryFile = new File(entry) //text file with the PL/SQL code in it
sql.execute(entryFile.txt) //Executing the text of the PL/SQL code
I greatly simplified the contents of the Oracle procedure to this:
create or replace PROCEDURE CHECK_AND_CREATE(TABLE_NAME_TO_CREATE IN VARCHAR2) AS
V_COUNT NUMBER;
BEGIN
SELECT * FROM DUAL;
END;
and it's still not working.
