Find and Replace Error

Toad Data Point
1

When doing Find and Replace I sometimes get the following error…

3/28/2018 1:28:57 PM: Starting to Find and replace files.

3/28/2018 1:28:57 PM: Found all occurences of “{#}” and replaced with “#”

3/28/2018 1:28:57 PM: Find_Replace_5 - Access to the path ‘H:\TOAD Data Point\AA.sql’ is denied.

3/28/2018 1:28:57 PM: This file was not available at runtime. The location and user authentication may be different at runtime.

The SOURCE file and the SAVE file are the same (H:\TOAD Data Point\AA.sql).

Like I said this does not always happen only accasionally and the file AA.sql is gone.

Is there anything I can do to stop this from happening?

Thanks,

Andrew

2

I assume this is occurring in a scheduled automation script. What is the ID that the script is run under? It should be your domain ID and not SYSTEM or some other id. It looks like this is mapped network drive and at the time of running a logid is needed or privilege is blocked.

You could try adding a copy file local and if successful, execute the find and replace locally and then copy back to network. You can check if the copy activity was successful by testing if ‘#_ACTIVITY_RESULT#’ == ‘Succeeded’.

Also, why are you needing to make this replacement? We use the # sign to mean literal replacement. if you are needing to bind a sql value at runtime you can do this using a database bind and not literal replacement. This would take away the need to edit a SQL Script.

3

Hi,

As I mentioned this error does not always happen. If I run and it fails and then rerun it will pass 99% of the time.

I have to do a replace for a couple of reasons…

  1. The variable_name is a variable.

  2. TDP does not handle “&&var.” the same way that TOAD does.

Thanks

4

&&var is specific to SQL*Plus. it is there symbol for literal replacement. Ours is #var# which is seems like you are using.

select * from table where id = &&var

select * from table where id = #var#

what I am suggesting is that you use this Oracle bind syntax

select * from table where id = :id

You would define id as a variable in the automation script and set the value at run time and then run the script with the :id syntax. Automation will bind the variable value at the Oracle server.

This way you never need to do any replace to the script.

See this video for examples of variables in automation.

https://www.youtube.com/watch?v=_PTUYsKiCPw

5

Hi,

I noticed that TDP almost seems to work for &&VAR as it does in SQL*Plus, however, it does not work when it is used as below…

select * from &&VAR…T_EMPLOYEE;

The reason why I am doing Find and Replace is because the variable name is a variable as well as the variable itself. The varname and the var are in a table that is in a loop… this is why I use the Find and Replace. I look for varname in the script and update it with var (eventually there may be hundreds of scripts/records).

TABLE example…

SQL_Name VARNAME VAR

sript1.sql P_DATE 31-12-2018

script2.sql P_NAME David

etc…

Thanks