Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help running a SQL script in Toad

Posted on 2014-07-15
7
Medium Priority
?
831 Views
Last Modified: 2014-07-15
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
0
Comment
Question by:marceloNYC
  • 4
  • 2
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40197944
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40197977
>> 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
 

Author Comment

by:marceloNYC
ID: 40197980
: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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40197982
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
 

Author Comment

by:marceloNYC
ID: 40198002
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
 

Author Comment

by:marceloNYC
ID: 40198005
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
 

Author Closing Comment

by:marceloNYC
ID: 40198051
Yes!!! This did it!


Thank you!!!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question