Returning from an external SQL in SQLPLUS 1

[vinczej]

Hi all!

I have an SQLPLUS script. From that SQL-script I can call an external SQL like next: @sqlname.sql . It's work correctly.

My problem is the next: If I embed this external script into middle of my main SQL script, then I can't return from the external script to the main script. The rest part of the main script doesn't work. And is similar to problem, if I want to call an second or more external scripts from the main SQL script.

Example:
"select ..... ;
update .... ;

@sqlscript1.sql
-- From here does nothing the main script, it is lost for me
@sqlscript2.sql

select ... ;
rollback;"

Thanks forward!

 

[Thargy]

Can you post the calling script and the called script, so we can see them.
It's possible for apparently trivial things to stop a script from running.

Regards

T

 

[vinczej]

Thanks for the reply!

Your reply gave me an idea, and I've tried it. I removed the "spool off" lines from the called external script. So I could do the both script correctly, but this way the spool is a "jam", hardly readable. It's any way to separate the spools into individual LOGs?

Example (very simple):
xmain.sql:
---------------------------------------
spool xmain.log
select 'XMAIN1' from dual ;
@xexternal1.sql
@xexternal2.sql
--here should return to the xmain LOG
select 'XMAIN2' from dual ;
spool off ;
======================================
xexternal1.sql
--------------------------------------
spool xexternal1.log
select 'XEXTERNAL1' from dual ;
--spool off;
======================================
xexternal2.sql
--------------------------------------
spool xexternal2.log
select 'XEXTERNAL2' from dual ;
--spool off;
======================================

The result is, that the xexternal2.log contains the 'XMAIN2' word, not the xmain.log (as should be right).
I see, that it's a problem of spools. Any idea, to correctly write all LOGs? (XMAIN)

 

[Thargy]

Yes,

you should watch your spool on and spool off statements.
In the first script, which invokes xexternal1.sql and xexternal2.sql, when xexternal2.sql finishes, it leaves the script spooling to xexternal2.log
You need to uncomment the spool off, and then in the main, precede the statement "select 'XMAIN2' from dual ;" with "SPOOL XMAIN.LOG APPEND" and you will (I believe) find what you want.

Regards

T

 

[vinczej]

Great, works!

--here should return to the xmain LOG
SPOOL XAMIN.LOG APPEND

Indeed, this was my problem!

Thank you very much!

 

[SantaMufasa]

Vinczej,

Be sure to reward Thargy for the time he spent solving your problem by your clicking on [Thank Thargy and star this post!].

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.