Link to home
Start Free TrialLog in
Avatar of srikotesh
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
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

missing COMMIT
Avatar of srikotesh
srikotesh

ASKER

added commit as well,

tried with @@

i am using oracle sql developer
Can you please post the contents of the files?!
Does it look like this?!

@C:\script\xxx\aa.sql;
@C:\script\xxx\bb.sql;
commit;

Open in new window

aa.sql file contains:

Insert into TASK_MASTER (APPLNID,SERVICEID,TASKID,TASKDESC) values ('pdda','pdda','schedule11',null);

commit;
All content please ;-)
bb.sql  file also have the same content
with different id
master.sql is missing AND how you call it (exactly)
I am executing directly in Oracle Sql developer

@C:\script\xxx\aa.sql; @C:\script\xxx\bb.sql; commit;
SOLUTION
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No errors
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?!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?
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
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
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)
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?
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
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?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
what is above code? is it something like master.bat? or supermaster.sql?
hi ,

this my master sql file,i am not using  .bat file
which contains the above code
master.sql:
==========
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
in set ???

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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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/
due to some session/connection expired script not executed earlier,later it got executed.