Link to home
Start Free TrialLog in
Avatar of marceloNYC
marceloNYCFlag for United States of America

asked on

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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
>> 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
Avatar of marceloNYC

ASKER

: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.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

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
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);
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?
Yes!!! This did it!


Thank you!!!