Home » Other » Client Tools » set command
set command [message #416218] Fri, 31 July 2009 04:39 Go to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member
Hi,
I am trying to run an insert command from sqlplus and looks like it can not handle line breaks. Also the symbol $ in the value clause is causing oracle to prompt for input value.
INSERT INTO template_et
         ( email_template_id, 
           email_subject_tx, 
           email_body_tx)
  VALUES ( 3, 
          'Email Update for User',
          'Dear ${USER_NAME},

Welcome to SysTGain!

As per your request, your email address has been changed. 
Going forward, please use your new email address as your TGAIN username. 

Please log into system via the link provided below.

SysTGain site address: ${SYSTEM_LINK}

Password: ${SYSTEM_PASSWORD}


Sincerely,
Support Team

');

Question : How to handle both the problem. I know set scan off which suppress & but what for $?

Thanks
Navkrish

[Updated on: Fri, 31 July 2009 05:30] by Moderator

Report message to a moderator

Re: set command [message #416245 is a reply to message #416218] Fri, 31 July 2009 06:12 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
What is the exact error you got?

If your column is not wide enough to store the entire mail body, append line termination character chr(10) for every line.

I think ${USER_NAME} stuff from SQL Server key word. I have not seen any such keywords in Oracle


-Techno

Report message to a moderator

Re: set command [message #416246 is a reply to message #416218] Fri, 31 July 2009 06:17 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can concatenate the line breaks with chr(10) to account for the newlines. As for the $, what does sho define list ?

Report message to a moderator

Re: set command [message #416261 is a reply to message #416246] Fri, 31 July 2009 07:15 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member
okay, I think oracle took it as something like reserved word -

error for $
Changing password for :
New password:
Retype new password:


error for line break 
SP2-0734: unknown command beginning "Welcome to..." - rest of line ignored.
SP2-0734: unknown command beginning "As per you..." - rest of line ignored.
SP2-0734: unknown command beginning "Please log..." - rest of line ignored.

[Updated on: Fri, 31 July 2009 07:17]

Report message to a moderator

Re: set command [message #416266 is a reply to message #416261] Fri, 31 July 2009 07:20 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
navkrish wrote on Fri, 31 July 2009 08:15
okay, I think oracle took it as something like reserved word -

Don't know what you mean by that. Inserting ${NEW_NAME} should not pose any problems.


navkrish wrote on Fri, 31 July 2009 08:15

error for $
Changing password for :
New password:
Retype new password:


error for line break 
SP2-0734: unknown command beginning "Welcome to..." - rest of line ignored.
SP2-0734: unknown command beginning "As per you..." - rest of line ignored.
SP2-0734: unknown command beginning "Please log..." - rest of line ignored.


Don't know what you are trying to tell us by that?? An actual cut & paste of what you entered in SQL*Plus will help.

[Updated on: Fri, 31 July 2009 07:22]

Report message to a moderator

Re: set command [message #416268 is a reply to message #416266] Fri, 31 July 2009 07:29 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member
Here is the error when I execute the script -
SQL>a.sql
SP2-0734: unknown command beginning "Welcome to..." - rest of line ignored.
SP2-0734: unknown command beginning "As per you..." - rest of line ignored.
SP2-0734: unknown command beginning "Going forw..." - rest of line ignored.
SP2-0734: unknown command beginning "Please log..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "SysTGain s..." - rest of line ignored.
Changing password for :
New password:


Cursor prompts for the input next to New password:

I keyed ENTER and here is the output after

SQL>
SP2-0734: unknown command beginning "Welcome to..." - rest of line ignored.
SP2-0734: unknown command beginning "As per you..." - rest of line ignored.
SP2-0734: unknown command beginning "Going forw..." - rest of line ignored.
SP2-0734: unknown command beginning "Please log..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "SysTGain s..." - rest of line ignored.
Changing password for :
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)


Password unchanged
SP2-0042: unknown command "Sincerely," - rest of line ignored.
SP2-0734: unknown command beginning "Support Te..." - rest of line ignored.
SP2-0042: unknown command "'" - rest of line ignored.
SP2-0734: unknown command beginning ", 'The inf..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "If you are..." - rest of line ignored.
SP2-0734: unknown command beginning "Please not..." - rest of line ignored.
SP2-0734: unknown command beginning "The sender..." - rest of line ignored.
SP2-0734: unknown command beginning "E-mail tra..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "By reading..." - rest of line ignored.
SP2-0734: unknown command beginning "Neither Ma..." - rest of line ignored.



however it is working in TOAD.

[Updated on: Fri, 31 July 2009 07:31]

Report message to a moderator

Re: set command [message #416275 is a reply to message #416268] Fri, 31 July 2009 07:38 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
How about a look at the actually script you are running??

And you didn't mention what sho define shows in SQL*Plus ?

Report message to a moderator

Re: set command [message #416281 is a reply to message #416275] Fri, 31 July 2009 08:03 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The problem seems to be the somewhat usual "SQL*Plus doesn't like blank lines" variety, though.

SQL> CREATE TABLE test_tab (n NUMBER(10), c VARCHAR(4000));

Table created.

SQL>
SQL>
SQL> INSERT INTO test_tab VALUES (1,'This
  2  ist a really long
  3
SQL> text with a lot of $yabbering
SP2-0734: unknown command beginning "text with ..." - rest of line ignored.
SQL>
SQL> going on');
SP2-0042: unknown command "going on')" - rest of line ignored.
SQL>
SQL>
SQL>
SQL> INSERT INTO test_tab VALUES (1,'This
  2  ist a really long
  3  text with a lot of $yabbering
  4  going on');

1 row created.

SQL>
SQL>
SQL> INSERT INTO test_tab VALUES (1,'This
  2  ist a really long
  3  _
  4  text with a lot of $yabbering
  5  _
  6  going on');

1 row created.

SQL>

Report message to a moderator

Re: set command [message #416284 is a reply to message #416281] Fri, 31 July 2009 08:07 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
A blank line in SQL statement in SQL*Plus simply lets SQL*Plus know you are done entering the command.

Report message to a moderator

Re: set command [message #416311 is a reply to message #416284] Fri, 31 July 2009 10:56 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I know. But in the case that is the problem here, the "blank line" is supposed to be part of the data. So the person with the problem is not really "done" yet.

Report message to a moderator

Re: set command [message #416312 is a reply to message #416311] Fri, 31 July 2009 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And this does not happen if bind variables are used as it should be.

Regards
Michel

Report message to a moderator

Re: set command [message #416313 is a reply to message #416311] Fri, 31 July 2009 11:27 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ThomasG wrote on Fri, 31 July 2009 11:56
I know. But in the case that is the problem here, the "blank line" is supposed to be part of the data. So the person with the problem is not really "done" yet.

That's why I suggested concatenate with chr(10) to create blank lines.

Report message to a moderator

Previous Topic: SQLDeveloper/TOAD
Next Topic: dbms_output.put_line
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 31 09:09:39 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.