Cant call and execute .sql script file in Oracle anonymous block
I am trying to run the anonymous block below, but I am getting an error ORA-00900: invalid SQL statement
. I know it is not possible to issue DDL as static SQL in a PL / SQL block in Oracle. Since I have over 50 sql scripts to be executed in an anonymous block, it is impossible to issue EXECUTE IMMEDIATE
with every SQL statement. So I created a separate script as sql file and am trying to call from the 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;
source to share
else
@ UpgradeFromV2.1ToV3.0.sql;
end if;
You cannot directly call sql script inside PL / SQL block . You have to call it from the outside.
I would suggest putting all the sql script content in a PL / SQL block.
Don't be confused between PL / SQL and SQL * Plus .
-
PL / SQL is a server-side language that includes P rocedural L anguage (PL) and S tructured Q uery L anguage (SQL). It runs inside the Oracle server process.
-
* Plus is SQL - a tool the C ommand of L ine I of nterface (the CLI), which allows you to send SQL and PL / SQL-code on 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 can use DBMS_SCHEDULER and submit these sql scripts as appropriate jobs .
The sql script call depends on your OS .
For example, on 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 will look like this:
sqlplus user@sid/password @D:\scripts\script.sql
exit
source to share