srikotesh
asked on
how to execute multiple scripts using oracle
hi Experts,
created master.sql file added below sql files:
@C:\script\xxx\aa.sql(file having one insert query)
@C:\script\xxx\bb.sql(file having one insert query)
when i execute the master.sql no insertions happened
some one suggest me how to do
created master.sql file added below sql files:
@C:\script\xxx\aa.sql(file
@C:\script\xxx\bb.sql(file
when i execute the master.sql no insertions happened
some one suggest me how to do
missing COMMIT
ASKER
added commit as well,
tried with @@
i am using oracle sql developer
tried with @@
i am using oracle sql developer
Can you please post the contents of the files?!
Does it look like this?!
Does it look like this?!
@C:\script\xxx\aa.sql;
@C:\script\xxx\bb.sql;
commit;
ASKER
aa.sql file contains:
Insert into TASK_MASTER (APPLNID,SERVICEID,TASKID, TASKDESC) values ('pdda','pdda','schedule11 ',null);
commit;
Insert into TASK_MASTER (APPLNID,SERVICEID,TASKID,
commit;
All content please ;-)
ASKER
bb.sql file also have the same content
with different id
with different id
master.sql is missing AND how you call it (exactly)
ASKER
I am executing directly in Oracle Sql developer
@C:\script\xxx\aa.sql; @C:\script\xxx\bb.sql; commit;
@C:\script\xxx\aa.sql; @C:\script\xxx\bb.sql; commit;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No errors
script finished successfully
but no records inserted
script finished successfully
but no records inserted
And the table TASK_MASTER is truly a table or is it a view?!
And you're sure, that you're inserting and queryiing the very same table on the very same database?!
And you're sure, that you're inserting and queryiing the very same table on the very same database?!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
shouldn't you focus on trying to get this to work in 1 script first ?
and then try 2 scripts ?
why do people want to learn flying when they haven't mastered walking yet ?
and then try 2 scripts ?
why do people want to learn flying when they haven't mastered walking yet ?
ASKER
hi Experts,
aa.sql file(my first file) :
Insert into TASK_MASTER (APPLNID,SERVICEID,TASKID, TASKDESC) values ('pdda','pdda','schedule11 ',null);
commit;
when i try to run as runscript(F5) nothing is inserted.
then i tried with execute statement(F9) it is inserted.
is there any difference
aa.sql file(my first file) :
Insert into TASK_MASTER (APPLNID,SERVICEID,TASKID,
commit;
when i try to run as runscript(F5) nothing is inserted.
then i tried with execute statement(F9) it is inserted.
is there any difference
you should ask yourself this question first:
what will i be using to execute these scripts unattended ?
SQLDeveloper ?
or is there something which can execute scripts ?
well, yes ... sqlplus !
it comes with multiple flavors of the free oracle client
why are you putting insert comments in scripts ?
that strikes me as very odd
what will i be using to execute these scripts unattended ?
SQLDeveloper ?
or is there something which can execute scripts ?
well, yes ... sqlplus !
it comes with multiple flavors of the free oracle client
why are you putting insert comments in scripts ?
that strikes me as very odd
try reversing the files
@C:\script\xxx\bb.sql
@C:\script\xxx\aa.sql
and see what will happen this time :)
also what is the content of bb.sql (never mentioned here)
@C:\script\xxx\bb.sql
@C:\script\xxx\aa.sql
and see what will happen this time :)
also what is the content of bb.sql (never mentioned here)
ASKER
hi hainkurt,
tried with reverse order as well
no records are inserted,no errors,
hi Geert,
what will i be using to execute these scripts unattended ?
i am using oracle sql developer
why are you putting insert comments in scripts ?
i have multiple static insert statements in different sql files
what i am trying is
i am keeping all sql files of the static insert scripts in one file as master file
whenever i want to execute simply i can execute a single file
no need to search for all the files
this master i will execute in different environments like dev,test,uat..
DO I need to use sql plus for this requirement?
not possible with sql developer?
tried with reverse order as well
no records are inserted,no errors,
hi Geert,
what will i be using to execute these scripts unattended ?
i am using oracle sql developer
why are you putting insert comments in scripts ?
i have multiple static insert statements in different sql files
what i am trying is
i am keeping all sql files of the static insert scripts in one file as master file
whenever i want to execute simply i can execute a single file
no need to search for all the files
this master i will execute in different environments like dev,test,uat..
DO I need to use sql plus for this requirement?
not possible with sql developer?
now, we are getting somewhere
yer givin us info ... :)
sqldeveloper is ok for editing and creating the scripts
but sqlplus will be a lot faster to run them.
not sure, but i think sqldeveloper actually uses sqlplus to execute scripts and then return the output
you can even schedule it to do that via windows scheduler (or a cron job)
i know Toad can do that with the F5
so it's somewhat odd to use an overhead system to just execute scripts
what you describe looks like either change management or building some test cases or both
yer givin us info ... :)
sqldeveloper is ok for editing and creating the scripts
but sqlplus will be a lot faster to run them.
not sure, but i think sqldeveloper actually uses sqlplus to execute scripts and then return the output
you can even schedule it to do that via windows scheduler (or a cron job)
i know Toad can do that with the F5
so it's somewhat odd to use an overhead system to just execute scripts
what you describe looks like either change management or building some test cases or both
one more try
@C:\script\xxx\cc.sql
@C:\script\xxx\aa.sql
@C:\script\xxx\bb.sql
looks like you have issues with bb.sql...
how do you run them, from command line? put ">out.txt" at the end to see the messages...
* what is content of bb.sql?
@C:\script\xxx\cc.sql
@C:\script\xxx\aa.sql
@C:\script\xxx\bb.sql
looks like you have issues with bb.sql...
how do you run them, from command line? put ">out.txt" at the end to see the messages...
* what is content of bb.sql?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what is above code? is it something like master.bat? or supermaster.sql?
ASKER
hi ,
this my master sql file,i am not using .bat file
which contains the above code
this my master sql file,i am not using .bat file
which contains the above code
ASKER
master.sql:
==========
SET define OFF;
SET echo ON;
@C:\script\xxx\aa.sql
@C:\script\xxx\bb.sql
SET echo OFF;
set define on;
==========
SET define OFF;
SET echo ON;
@C:\script\xxx\aa.sql
@C:\script\xxx\bb.sql
SET echo OFF;
set define on;
>>when i try to run as runscript(F5) nothing is inserted. then i tried with execute statement(F9) it is inserted.
What version of SQL Developer are you using? I just tried F5 and F9 using 4.1.2 and both executed my script.
What version of SQL Developer are you using? I just tried F5 and F9 using 4.1.2 and both executed my script.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in set ???
hainkurt, do you ever test any of your comments ?
none of the lines in that script needs a ;
hainkurt, do you ever test any of your comments ?
none of the lines in that script needs a ;
From the OP's own post, it also doesn't a script problem. It appears is a SQL Developer issue with F5 and F9.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI slightwv (䄆 Netminder),
WHEN I EXECUTED INDIVIDUAL script--> i have faced the issue Runscript(F5)
Don't know exactly may be connection expired or something later i reconnected it is working fine.
now i am able to execute individual file
and master file as well
WHEN I EXECUTED INDIVIDUAL script--> i have faced the issue Runscript(F5)
Don't know exactly may be connection expired or something later i reconnected it is working fine.
now i am able to execute individual file
and master file as well
As an addon to the F5/F9 (exec statement vs run as script) "issue":
Obviously there are some differences when using F5 or F9 (Toad AND SQL Developer, maybe even other) ;-)
https://stackoverflow.com/questions/38656833/why-is-a-run-statement-faster-than-run-script-in-oracle-sql-developer
https://outofmymemory.wordpress.com/2015/04/21/execute-statement-or-run-script/
Obviously there are some differences when using F5 or F9 (Toad AND SQL Developer, maybe even other) ;-)
https://stackoverflow.com/questions/38656833/why-is-a-run-statement-faster-than-run-script-in-oracle-sql-developer
https://outofmymemory.wordpress.com/2015/04/21/execute-statement-or-run-script/
ASKER
due to some session/connection expired script not executed earlier,later it got executed.