Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5061
  • Last Modified:

Running a script having # in SQLPlus

I have a script which has "#" in the script and is very much used.
To make it cleat, The "#" is part of a filed which gets inserted through the script.
But when i run from the sqlplus prompt,
 it gives me the error:
SP2-0734: unknown command beginning "blah balah blah..." - rest of line ignored.

I think I am missing some set statement to make sqlplus not ignore the #

However If I run this script from PLSql Developer, everything goes fine.

cheers
0
rajaloysious
Asked:
rajaloysious
  • 5
  • 3
  • 3
  • +3
2 Solutions
 
schwertnerCommented:
Use chr(35) instead #.
0
 
paquicubaCommented:
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>
0
 
helpneedCommented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
helpneedCommented:
hi

try to eliminate space in both side if its there

regards
0
 
Mehul ShahIT consultantCommented:
Can you post the script contents or the line which gives the above error.
0
 
rajaloysiousAuthor Commented:
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
0
 
rajaloysiousAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
Not famaliar with "PL SSL Developer"  I'm just thinking that maybe this script is in a proprietary language?
0
 
Mehul ShahIT consultantCommented:
Is it PL SSL Developer or PLSQL Developer?
0
 
schwertnerCommented:
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';
0
 
rajaloysiousAuthor Commented:
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
0
 
Mehul ShahIT consultantCommented:
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
0
 
rajaloysiousAuthor Commented:
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
0
 
paquicubaCommented:
You may need to SET the SQLPREFIX to something else ( non-alphanumeric ) than "#":

SQL> sho sqlprefix
sqlprefix "#" (hex 23)

SQL> BEGIN
  2  #SET LINESIZE 8
  2  #SET SERVEROUTPUT ON
  2  dbms_output.put_line('HELLO WORLD');
  3  END;
  4  /
HELLO
WORLD

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> BEGIN
  2  #SET LINESIZE 200
  2  dbms_output.put_line('HELLOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO WORLD');
  3  END;
  4  /
HELLOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO WORLD

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> SET SQLPREFIX ~
SQL> BEGIN
  2  #SET LINESIZE 200
  3  dbms_output.put_line('HELLO WORLD');
  4  END;
  5  /
#SET LINESIZE 200
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "#" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>


Elapsed: 00:00:00.00
0
 
slightwv (䄆 Netminder) Commented:
I believe the issue is with the SQL*Plus controls sqlprefix and sqlblanklines.  See if the following testcase will help fix the problem.

Note:  I set sqlprefix to a tilde.  You can set it to some other junk character that will not appear in the code.

---------------------
set sqlprefix '~'
set sqlblanklines on

drop table tab1;
create table tab1( col1 varchar2(50));

insert into tab1 values('
a
b

#c

d
');

commit;

0
 
slightwv (䄆 Netminder) Commented:
Sorry paq, you caught me typing.....

At least I still have the blank lines going for me.   ;)
0
 
rajaloysiousAuthor Commented:
Thanks Everyone.
0
 
paquicubaCommented:
We got it..

; )
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now