DET DEFINE OFF in SQL Developer

Database
Fan

DET DEFINE OFF in SQL Developer

Hi,

I am using Oracle SQL Developer to connect to Teradata via TeraJDBC 15.10.00.14.

I am trying to run a script that has & in it, and want to turn off variable definition to prevent TD to ask me for variable values. However, I seem not to be able to run the "SET DEFINE OFF" command that was suggested in this TD forum and other similar posts:

https://forums.teradata.com/forum/tools/sqlplus-spool-to-fastload-via-named-pipe-windows-is-barely-f...

Here are the errors that I get when I try different versions of the code:

>>> set define off;

Syntax error: expected something between the word 'define' and the 'off' keyword.

>>> SET DEFINE=OFF;

Syntax error: expected something between '=' and the 'OFF' keyword.

>>> SET SCAN OFF;

Syntax error: expected something between the word 'SCAN' and the 'OFF' keyword.

Please help. Thank you in advance!
3 REPLIES
Junior Supporter

Re: DET DEFINE OFF in SQL Developer

Hi.

Teradata DOES NOT ask you for the values. This (variable substitution) is a SQL Developer feature that happens BEFORE sending the request to Teradata.

SET DEFINE OFF works in SQL Developer just the same as it works in sql*plus.

You neglect to show your real code, no chances to help you...

Cheers.

Carlos.

Fan

Re: DET DEFINE OFF in SQL Developer

Thanks for your response, Carlos.

My original query looks like this:

SELECT a,b,c, FROM table_a

WHERE a = 'Abercombie & Fitch';

When I run the query, I get a dialog box asking for the value of "Fitch".

I understand that this happens at SQL Developer end, not in Teradata. But when I run "SET DEFINE OFF" in SQL Developer, I get the following error:

"SQL Error: [Teradata Database] [TeraJDBC 15.10.00.14] [Error 3706] [SQLState 42000] Syntax error: expected something between the word 'define' and the 'off' keyword."

This has made me thinking maybe SQL Developer sends this query to TD prematurely, which results in TD being confused about the query.

Has anybody used SQL Developer with Teradata and had similar experience? How did you turn the variable definition off in SQL Developer?

Thanks again for your help. And thank you for your patience as I learn how to effectively post my questions and to include my query details in my posts. I am a newbie to TD and this forum!

Amir


Junior Supporter

Re: DET DEFINE OFF in SQL Developer

Hi.

You must execute the script with an '@' preceding the .sql file or by using F5 (run script) if you are executing the commands in a SQL worksheet.

SET DEFINE OFF works for me either way in SQL Developer 4.0.2.15 connecting Teradata DB via jdbc.

Cheers.

Carlos.