SQL Task to run execute dbms_java.grant_permission

[tve <tv...@online.fr>]

Hi there,

I am trying to use ant SQL task to run the following "execute
dbms_java.grant_permission" in an Oracle 10g database but the task is
failing with the following error: ORA-00900: invalid SQL statement.

I guess that the execute dbms_java is not a standard sql statement but
before giving up, I would like to know if someone has been successful to
making such oracle statement work with sql task.

Here's what I have tried so far:

---8<--------------

<target name="grant-java-rights">
	<sql driver="oracle.jdbc.OracleDriver " 
		url="jdbc:oracle:thin:@${db.host}:${db.port}:${db.sid}" 
		userid="${db.user}" 
		password="${db.pwd}" 
		classpath="${oracle.classpath}"
		showheaders="false"
		showtrailers="false"
		print="true">
		<transaction>
			select '		==>> ' || v$instance.host_name || ':' ||
dba_directories.DIRECTORY_PATH 
			from dba_directories, v$instance 
			where dba_directories.directory_name = 'FA_RESOURCES';
		</transaction>
	</sql>

	<sql driver="oracle.jdbc.OracleDriver " 
		url="jdbc:oracle:thin:@${db.host}:${db.port}:${db.sid}" 
		userid="${db.user}" 
		password="${db.pwd}" 
		classpath="${oracle.classpath}"
		keepformat="true"
		delimiter="/"
		delimitertype="row"
		escapeprocessing="false"
		print="true">
		<transaction>
execute dbms_java.grant_permission( 'FA', 'SYS:java.io.FilePermission',
'C:/bea/user_projects/domains/Domain1/Server1/fa-xsd/schema.xsd', 'read' )
/
		</transaction>
	</sql>
</target>

---8<--------------

The first sql task runs fine and retrieves the correct value from the
database so I am assuming there is no issue about connectivity or whatever.

The second sql task is the one failing with the error message mentionned
above. I have tried several variants to this second sql task such as:

    1. escapeprocessing="true"
    2. escapeprocessing not specified in the attrs list
    3. keepformat not specified in the attrs list
    4. delimiter and delimitertype not specified in the attrs list

None of these has worked.

If someone has been able to run this oracle statement (or other exotic
oracle statement) via sql task (the statement is correctly executed via
sqlplus), please share your experience.

For info, I am getting the same issue with the following:
SQL> show error;


ps: I am using ant 1.7 on windows XP.
-- 
View this message in context: http://www.nabble.com/SQL-Task-to-run-execute-dbms_java.grant_permission-tp22202278p22202278.html
Sent from the Ant - Users mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org