Running a script having # in SQLPlus

Use chr(35) instead #.

I still don't understand what is it that you're trying to accomplish. If the issue is prompting and inserting "#", just add the single quotes around the substitution variable:

SQL> @test
enter new number_sign: #

1 row created.

Elapsed: 00:00:00.00
SQL> select * from test;

C
-
#

Elapsed: 00:00:00.00
SQL> get test
  1  set verify off
  2  accept number_sign prompt 'enter new number_sign: '
  3* insert into test values( '&number_sign' );
SQL>

hi

check if there are any space

i think this error is due to the space in front of the '#'   try to remove the space and
if possible please put the original message with error from the sqlplus or the editor

regards

hi

try to eliminate space in both side if its there

regards

Can you post the script contents or the line which gives the above error.

I need the spaces. Also this is a column of type Long (Long strings)
I try to insert a text which has control breaks and line feed ( as multiple lines)
and some lines need to start with a #

cheers

I guess this has something to do with Set statements
like set verify off etc...
This goes fine with a third party tool PL SSL Developer but not trough sqlplus

I am afraid i can send the script...

cheers

Not famaliar with "PL SSL Developer"  I'm just thinking that maybe this script is in a proprietary language?

Is it PL SSL Developer or PLSQL Developer?

Example
Try instead
SQL>select a,b from my_table where position ='      # Crocodile';

this one

SQL>select a,b from my_table where position ='      ' || chr(35) || ' Crocodile';

You can even create a variable

sp_char1 varchar2(1) = chr(35);

and use

SQL>select a,b from my_table where position ='      ' || sp_char1 || ' Crocodile';

Oops, It is PL SQL Developer.

Thanks for all the comments, but
I cant do major changes to the scripts now, as we are in an advanced stage.

The script runs perfectly well with PLSQL Developer and doesnt with SQL Plus.

There should be some environment variable which needs to be set up

cheers

what is the oracle error code that you are getting. that is ORA-

check for the below example.

SP2-0734: unknown command beginning "if exists(..." - rest of line ignored.
end if
*
ERROR at line 2:
ORA-00933: SQL command not properly ended <== what is the error number

DECLARE
  2  
  3        variable1 long default '
  4  
  5  
  6  select
  7  a, b,c ,d,
..
28  #XYZ
SP2-0734: unknown command beginning "XYZ..." - rest of line ignored.

Again I stress that there is no problems whatsoever in the scripts, but there is something missing in the environment in SQL plus which is taken care well in PLSQL Devloper.

Dear Experts in Oracle,
As I said earlier, Please dont suggest to change the Script as it is freezed already.
Just let me know why this isnt working in SQL PLUS

The clue is that there are a lot of lines which start with # for a long variable which gets inserted into a table

Cheers

Sorry paq, you caught me typing.....

At least I still have the blank lines going for me.   ;)

Thanks Everyone.

We got it..

; )