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
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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>
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';
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
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('HELL
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('HELL
3 END;
4 /
HELLOOOOOOOOOOOOOOOOOOOOOO
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> SET SQLPREFIX ~
SQL> BEGIN
2 #SET LINESIZE 200
3 dbms_output.put_line('HELL
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
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;
Business Accounts
Answer for Membership
by: schwertnerPosted on 2005-04-18 at 04:18:28ID: 13805000
Use chr(35) instead #.