marceloNYC
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_INI T.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.Star tMailGatew ayOutbound (
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('Unab le 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/sitetm p <--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
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_INI
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.Star
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*
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('Unab
'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/sitetm
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
>> 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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sf_mail_gateway_plsql.Star tMailGatew ayOutbound (
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);
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);
ASKER
ohh I just saw above; so I need to enter:
BEGIN
DBMS_OUTPUT.put_line('func tionid ' || '&function_id');
Once I run it will ask me for the correct value?
BEGIN
DBMS_OUTPUT.put_line('func
Once I run it will ask me for the correct value?
ASKER
Yes!!! This did it!
Thank you!!!
Thank you!!!
>>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