Need help running a SQL script in Toad

I need to make this work in Toad for Oracle while I get my SQLPlus working again.

This is the script that I am trying to run in Toad and is giving me a a little window with a field to enter the values I need but is not working:

REM
REM      File:      SFMAILGxxxxxxxxxxxxxxx_INIT.SQL
REM
REM      Description:      SQL*Flow Mail Gateway for PL/SQL Initialization
REM
REM    
REM
set echo off
set verify off
 
set define off
set define &

ACCEPT function_id PROMPT 'Application Function ID: ' NUMBER DEFAULT 0
ACCEPT action_id PROMPT 'Action ID: ' NUMBER DEFAULT 0
ACCEPT sf_user_id PROMPT 'SQL*Flow User: '
ACCEPT smtp_server_name PROMPT 'SMTP Server: '
ACCEPT smtp_server_port PROMPT 'SMTP Port (Default=25): ' NUMBER DEFAULT 25
ACCEPT utc_offset_hours PROMPT 'UTC Offset Hours (Default=-5): ' NUMBER DEFAULT -5
ACCEPT use_file_logging PROMPT 'Use File Logging (TRUE | [FALSE]): ' DEFAULT FALSE
ACCEPT log_normal_messages PROMPT 'Log Normal Messages (TRUE | [FALSE]): ' DEFAULT FALSE
ACCEPT log_directory PROMPT 'Log Directory (e.g. ''/logs''): ' DEFAULT null
ACCEPT log_filename PROMPT 'Log Filename (e.g. ''xxmailgw.log''): ' DEFAULT null
ACCEPT polling_interval PROMPT 'Polling Interval ([60] seconds): ' NUMBER DEFAULT 60

declare
      job_number      binary_integer;
begin
      sf_mail_gateway_plsql.StartMailGatewayOutbound(
      Job                                          =>      job_number,
FunctionID                              =>      &function_id,  <---- here I need to enter whats below the script
            CompletedActionID                  =>      &action_id, <---- here I need to enter whats below the script
            UserID                                    =>      '&sf_user_id', <---- here I need to enter whats below the script
            SMTPServerName                        =>      '&smtp_server_name', <---- here I need to enter whats below the script
            SMTPServerPort                        =>      &smtp_server_port, <---- here I need to enter whats below the script
            UTCOffsetHours                        =>      &utc_offset_hours, <---- here I need to enter whats below the script
            UseFileLogging                        =>      &use_file_logging, <---- here I need to enter whats below the script
            LogNormalMessages                  =>      &log_normal_messages, <---- here I need to enter whats below the script
            LogDirectory                        =>      '&log_directory', <---- here I need to enter whats below the script
            LogFilename                              =>      '&log_filename', <---- here I need to enter whats below the script
            PollingInterval                  =>      &polling_interval, <---- here I need to enter whats below the script
            StartTime                              =>      sysdate, <---- here I need to enter whats below the script
            CommitWork                              =>      TRUE); <---- here I need to enter whats below the script
      dbms_output.put_line('SQL*Flow Mail Gateway for PL/SQL (Outbound) job has been initialized.');
      dbms_output.put_line('Job Number: ' || to_char(job_number));
exception
      when others then
            if sqlcode = 1 then
                  dbms_output.put_line('An error occurred while attempting to initialize SQL*Flow Mail Gateway for PL/SQL (Outbound).');
                  dbms_output.put_line('Unable to open log file.  ' ||
                                    'Verify that the directory exists, is writable by Oracle and that ' ||
                                    'the UTL_FILE_DIR initialization variable references it.');
            else
                  raise;
            end if;
end;
/

***************************************************************************
This part of the script:  SMTPServerName                        =>      '&smtp_server_name',

'&smtp_server_name', this needs to be  whatever the server name is....

The parameters that need to go in there are:

Enter the following parameter values:
Application Function ID            : <Value from Query 1 above>        [261]
Action ID                        : <Value from Query 2 above>      [101]
SQL*Flow User                  : SQLFLOW_MAILxxxx
SMTP Server                  : whatever.whatever
SMTP Port (Default=25)            : <ENTER>
UTC Offset Hours (Default=-5)            : <ENTER>
Use File Logging (TRUE | [FALSE])      : TRUE
Log Normal Messages (TRUE | [FALSE])      : TRUE
Log Directory  (on DB/CM Tier)            : /site025/app/common/sitetmp <--whatever example
Log Filename (e.g. 'sfmailgw.log')            : xxmailgw.log
Polling Interval ([60] seconds)            : <ENTER>

That is how it used to work so well in SQLPlus all I had to do was enter the values.

  I am sure this is not very clear so ask me for what ever is missing for you to understand me better :).

Thank you!

M
marceloNYCMiddle-Tier AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can't help with Toad.

>>while I get my SQLPlus working again.

Instant Client.

Two downloads:
 Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications  
*Instant Client Package - SQL*Plus: Additional libraries and executable for running SQL*Plus with Instant Client

Unzip both to same folder.

Manually create a network/admin folder.
Copy your tnsnames.ora and sqlnet.ora files into it.

That's it.

Download from here:
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
0
sdstuberCommented:
>> but is not working:

can you be more specific?

do you get an error? - if so, what error?
do you get wrong results? if so, what results?
do you get nothing? if so, does it finish and do nothing or hang?
does your keyboard catch fire? if so, the problem is more serious than you think
0
marceloNYCMiddle-Tier AdministratorAuthor Commented:
:D No it takes the script in Toad. If I don't try to edit the values I need. So the script runs no problems. No errors. If I edit the values I need it tries to ask me for what I need to enter. I don't know how to do that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
I modified your script to simply dump the values since I don't have your procedure

SET ECHO OFF
SET VERIFY OFF

SET DEFINE OFF
SET DEFINE &

ACCEPT function_id PROMPT 'Application Function ID: ' NUMBER DEFAULT 0
ACCEPT action_id PROMPT 'Action ID: ' NUMBER DEFAULT 0
ACCEPT sf_user_id PROMPT 'SQL*Flow User: '
ACCEPT smtp_server_name PROMPT 'SMTP Server: '
ACCEPT smtp_server_port PROMPT 'SMTP Port (Default=25): ' NUMBER DEFAULT 25
ACCEPT utc_offset_hours PROMPT 'UTC Offset Hours (Default=-5): ' NUMBER DEFAULT -5
ACCEPT use_file_logging PROMPT 'Use File Logging (TRUE | [FALSE]): ' DEFAULT FALSE
ACCEPT log_normal_messages PROMPT 'Log Normal Messages (TRUE | [FALSE]): ' DEFAULT FALSE
ACCEPT log_directory PROMPT 'Log Directory (e.g. ''/logs''): ' DEFAULT NULL
ACCEPT log_filename PROMPT 'Log Filename (e.g. ''xxmailgw.log''): ' DEFAULT NULL
ACCEPT polling_interval PROMPT 'Polling Interval ([60] seconds): ' NUMBER DEFAULT 60

BEGIN
    DBMS_OUTPUT.put_line('functionid          ' || '&function_id');
    DBMS_OUTPUT.put_line('completedactionid   ' || '&action_id');
    DBMS_OUTPUT.put_line('UserID              ' || '&sf_user_id');
    DBMS_OUTPUT.put_line('SMTPServerName      ' || '&smtp_server_name');
    DBMS_OUTPUT.put_line('SMTPServerPort      ' || '&smtp_server_port');
    DBMS_OUTPUT.put_line('UTCOffsetHours      ' || '&utc_offset_hours');
    DBMS_OUTPUT.put_line('UseFileLogging      ' || '&use_file_logging');
    DBMS_OUTPUT.put_line('LogNormalMessages   ' || '&log_normal_messages');
    DBMS_OUTPUT.put_line('LogDirectory        ' || '&log_directory');
    DBMS_OUTPUT.put_line('LogFilename         ' || '&log_filename');
    DBMS_OUTPUT.put_line('PollingInterval     ' || '&polling_interval');
END;
/

Open in new window



This worked for me.  I was prompted for the values, I entered some, took the defaults on others and the output corresponded to what I had entered.

I'm used Toad version 12.5.0.99 for my test with the F5 "Run as Script"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marceloNYCMiddle-Tier AdministratorAuthor Commented:
sf_mail_gateway_plsql.StartMailGatewayOutbound(
        Job                            =>    job_number,
        FunctionID                    =>    &function_id, <--- needs to be a number not what it looks
        CompletedActionID            =>    &action_id,
        UserID                        =>    '&sf_user_id',
        SMTPServerName                =>    '&smtp_server_name',
        SMTPServerPort                =>    &smtp_server_port,
        UTCOffsetHours                =>    &utc_offset_hours,
        UseFileLogging                =>    &use_file_logging,
        LogNormalMessages            =>    &log_normal_messages,
        LogDirectory                =>    '&log_directory',
        LogFilename                    =>    '&log_filename',
        PollingInterval            =>    &polling_interval,
        StartTime                    =>    sysdate,
        CommitWork                    =>    TRUE);
0
marceloNYCMiddle-Tier AdministratorAuthor Commented:
ohh I just saw above; so I need to enter:

BEGIN
    DBMS_OUTPUT.put_line('functionid          ' || '&function_id');

Once I run it will ask me for the correct value?
0
marceloNYCMiddle-Tier AdministratorAuthor Commented:
Yes!!! This did it!


Thank you!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.