Most of the software firewall are designed to provide you a kind of moderate level of security from unwanted intrusions, however at certain occasions, this could jeopardise certain legitimate connection requests…including Quest Toad TNS connections.
Prior breaking your head against the walls, give it another try by disabling the firewall if you receive the error “ORA-12546 TNS:permission denied”, and all other client side oracle tools successfully connect to Oracle database without any issues.
If the firewall was blocking the connections, create exceptions for Toad.exe and enjoy.
You may need to pass multiple parameters pragmatically prior executing a query with a block many times while users are provided with multiple parameter choices, here is an example explaining how deal with different kind of parameters(strings, numeric values & date)
if :ctrl.nulls_only = 'N' then
SET_BLOCK_PROPERTY('LINES',DEFAULT_WHERE, 'ACCOUNTING_DATE BETWEEN NVL('||''''||:CTRL.START_DATE||''''||',ACCOUNTING_DATE) AND NVL('||''''||:CTRL.END_DATE||''''||',ACCOUNTING_DATE)' ||' AND ATTRIBUTE1 IS NOT NULL AND ATTRIBUTE1=NVL('||''''||:CTRL.BEN_WHO||''''||',ATTRIBUTE1) AND MAIN_ACCOUNT='||:CTRL.MAIN_ACCOUNT);
/* where string and date parameters are passed in as strings and numeric parameters are passed in as numeric values*/
elsif :ctrl.nulls_only = 'Y' then
SET_BLOCK_PROPERTY('LINES',DEFAULT_WHERE, 'ACCOUNTING_DATE BETWEEN NVL('||''''||:CTRL.START_DATE||''''||',ACCOUNTING_DATE) AND NVL('||''''||:CTRL.END_DATE||''''||',ACCOUNTING_DATE)' ||'AND ATTRIBUTE1 IS NULL');
Passing parameters to DEFAULT_WHERE could become complex with additional scenarios when an in-line query should be passed in. Give it a try, and we hope the above example gives a budding programmer a good starting.