×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Returning from an external SQL in SQLPLUS

Returning from an external SQL in SQLPLUS

Returning from an external SQL in SQLPLUS

(OP)
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!

RE: Returning from an external SQL in SQLPLUS

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

RE: Returning from an external SQL in SQLPLUS

(OP)
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)



RE: Returning from an external SQL in SQLPLUS

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

RE: Returning from an external SQL in SQLPLUS

(OP)
Great, works!

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

Indeed, this was my problem!

Thank you very much!

RE: Returning from an external SQL in SQLPLUS

Vinczej,

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close