cant call and execute .sql script file in Oracle anonymous block

Question

I am trying to run the below anonymous block but getting an error ORA-00900: invalid SQL statement. I know its not possible to issue DDL as static SQL in a PL/SQL block in Oracle. As i have more than 50 sql scripts which needs to be executed in anonymous block its not possible to issue EXECUTE IMMEDIATE with each and every sql statements. So thats why i have created separate script as a sql file and trying to call from my current anonymous block.

  SET SERVEROUTPUT ON;
DECLARE MESSAGE VARCHAR2(100);
CHECK_VERSION VARCHAR2(100);
BEGIN
--- some code to check the version
select PROP_VAL into CHECK_VERSION from RATOR_MONITORING_CONFIGURATION.RM2_PROPERTIES WHERE PROP_NAME ='DB_VERSION';
  If CHECK_VERSION != 'V3.0' then
MESSAGE := 'Wrong Version';
-- IF(VERSION WRONG) THEN
-- MEESAGE := <<provide info for user here>>
else
@UpgradeFromV2.1ToV3.0.sql;
end if;
END;

score 2 · Accepted Answer · edited May 23 '17 at 10:27

2

else

@UpgradeFromV2.1ToV3.0.sql;

end if;

You cannot directly call a sql script inside a PL/SQL block. You need to externally call it.

I would suggest, put the entire content of the sql script in the PL/SQL block.

Do not confuse between PL/SQL and SQL*Plus.

PL/SQL is a server side language which includes Procedural Language(PL) and Structured Query Language(SQL). It is executed inside an Oracle server process.
SQL*Plus is a Command Line Interface(CLI) tool which allows you to submit SQL and PL/SQL code to the Oracle server for execution.

Update OP seems to have asked a similar question here. And this question is related to the previous question.

You could use DBMS_SCHEDULER and submit those sql scripts as respective jobs.

Calling a sql script depends on your OS.

For example, in Windows:

BEGIN  
  dbms_scheduler.create_job('MY_JOB',  
  job_action=>'C:\WINDOWS\SYSTEM32\CMD.EXE',  
  number_of_arguments=>3,  
  job_type=>'executable',  
  start_date => SYSTIMESTAMP,  
  repeat_interval => 'freq=hourly; byminute=0,30; bysecond=0;',  
  end_date => NULL,  
  enabled=> false);  
  dbms_scheduler.set_job_argument_value('MY_JOB',1,'/q');  
  dbms_scheduler.set_job_argument_value('MY_JOB',2,'/c');  
  dbms_scheduler.set_job_argument_value('MY_JOB',3,'D:\SCRIPTS\my_sql.bat');  
  dbms_scheduler.enable('MY_JOB');  
END;  
/

Now your my_sql.bat would look like:

sqlplus user@sid/password @D:\scripts\script.sql  
exit

edited May 23 '17 at 10:27

Community

1
1

answered Aug 07 '15 at 08:14

Lalit Kumar B

47,486
13
97
124

1

in my previous question i have put all of my sql script in PL/SQL block but it doesnt work as i have to use EXECUTE IMMEDIATE for every DDL statement.Its not possible to issue DDL as static SQL in a PL/SQL block in Oracle – Andrew Aug 07 '15 at 08:17
*in my previous question* Which question? You did not refer it in your current question. Post the details of the sql script. – Lalit Kumar B Aug 07 '15 at 08:19
1

the question has been marked as duplicate and it was closed but you can see here http://stackoverflow.com/questions/31872132/ora-00900-invalid-sql-statement-in-oracle-anonymous-block – Andrew Aug 07 '15 at 08:22
Ok lalit it looks complecated for me, i have to investigate more on this. But thanks i know now that its not possible to call such script file in pl sql block :) – Andrew Aug 07 '15 at 08:34
@Rahul You need to either put the script in the block or do some complicated steps as I shown above. It is not possible to read a OS file directly from PL/SQL. Good luck!. – Lalit Kumar B Aug 07 '15 at 08:38

cant call and execute .sql script file in Oracle anonymous block

1 Answers1