Running db scripts from command prompt

During development, we usually put all the sql scripts on a separate .sql file and run them at once, when you move your code on to other servers for testing purposes.

Here is how you run the scripts from the command prompt.


1) Open a command prompt at the folder where you have your sql scripts
2) execute sqlplus userid/pwd@srgtdev
3) above command will connect you to the oracle and give u a sql>
4) run the following
5) start filename.sql


woo huu all sqls in the batch file will be run successfully and you are good to go. here is an sample batch sql file

set echo on
spool 7_tpa_srgdbo_datascripts.sql.log

--set escape on

delete xyz where template_id in (-7);
INSERT INTO XYZ (TEMPLATE_ID, TEMPLATE_NAME, UPDATE_DATE, STATUS, TYPE, ASSESSMENT_TYPE_ID) VALUES (-7, 'FBI - Test7', SYSDATE, 'InActive', 'FBI', 1);

delete from XYZ_template where template_id = 999;

INSERT INTO XYZ_TEMPLATE (TEMPLATE_ID, TEMPLATE_NAME, UPDATE_DATE, STATUS, TYPE, ASSESSMENT_TYPE_ID, HANDLER_CLASS, HANDLER_JSP, HANDLER_PDF, HANDLER_TEMPLATE_TYPE) VALUES (999, 'Notch Template', SYSDATE, 'InActive', 'NOTCH', 1, 'com.xyz.say.service.Sample', '', '', '');

/* delete temporary data */
delete xyz_template where template_id in (-7);

----------- notching data script starts

delete from xyz_notching_template_qestn where template_id = 20001;

delete from xyz_assessment_type_value where assessment_value_id > 20000 and assessment_value_id < 20034;

delete from xyz_template where template_id = 20001;

delete from xyz_ASSESSMENT_TYPE where ASSESSMENT_TYPE_ID > 20000 and ASSESSMENT_TYPE_ID < 20016;



COMMIT;



spool off
set echo off


-Happy Coding Sonu