- Joined
- Oct 8, 2008
- Messages
- 2
- Reaction score
- 0
Please help me urgently on this, i am getting error "invalid statement" while executing dp.sql from sql task of ant, please help, ant script is:
<target name="dp-sql-execution" description="Executes SQL Scripts required for installation.">
<sql driver="oracle.jdbc.driver.OracleDriver" url="jdbcracle:thin172.18.37.134:1521:${env.DB_SCHEMA}" userid="${env.REP_USER}" password="${env.REP_PWD}" src="${env.INSTALL}/${patch.name}/AP/scripts/dropObjects.sql" print="yes" output="${env.INSTALL}/${patch.name}/AP/dist/logs/dropObjects-OutputFile.txt">
and dp.sql file is:
create or replace procedure dropObjects(objectName in varchar2, objectType in varchar2, returnValue out number)
as
value number;
finalStr varchar2(300);
begin
returnValue := 0;
select count(*) into value from user_objects where object_name = objectName;
if value > 0 then
dbms_output.put_line('value ' || value);
finalStr := 'drop ' || objectType || ' ' || objectName;
execute immediate finalStr;
dbms_output.put_line(finalStr || ' - executed ' );
else
dbms_output.put_line(objectName || ' ' || objectType || 'does not exist');
end if;
exception
when others then
returnValue := 1;
dbms_output.put_line('Exception while executing the command - ' || finalStr );
end;
/
Please help me, it thwos the error:
[sql] Failed to execute: variable returnValue number
<target name="dp-sql-execution" description="Executes SQL Scripts required for installation.">
<sql driver="oracle.jdbc.driver.OracleDriver" url="jdbcracle:thin172.18.37.134:1521:${env.DB_SCHEMA}" userid="${env.REP_USER}" password="${env.REP_PWD}" src="${env.INSTALL}/${patch.name}/AP/scripts/dropObjects.sql" print="yes" output="${env.INSTALL}/${patch.name}/AP/dist/logs/dropObjects-OutputFile.txt">
and dp.sql file is:
create or replace procedure dropObjects(objectName in varchar2, objectType in varchar2, returnValue out number)
as
value number;
finalStr varchar2(300);
begin
returnValue := 0;
select count(*) into value from user_objects where object_name = objectName;
if value > 0 then
dbms_output.put_line('value ' || value);
finalStr := 'drop ' || objectType || ' ' || objectName;
execute immediate finalStr;
dbms_output.put_line(finalStr || ' - executed ' );
else
dbms_output.put_line(objectName || ' ' || objectType || 'does not exist');
end if;
exception
when others then
returnValue := 1;
dbms_output.put_line('Exception while executing the command - ' || finalStr );
end;
/
Please help me, it thwos the error:
[sql] Failed to execute: variable returnValue number