Home » SQL & PL/SQL » SQL & PL/SQL » problem with substitution vairable
problem with substitution vairable [message #275878] Tue, 23 October 2007 02:55 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a table


create table yess(c1 varchar2(20));



i am trying to insert into this table, so i am using an
sql file which has the insert, and in this file, i am
calling one more sql file, where i am providing the
value

ins.sql is


set define off

SET head off echo off termout ON feedback off verify off linesize 80
SET serveroutput ON SIZE 999999
@name.sql

BEGIN

    INSERT INTO yess values('&l_srv_name');
   dbms_output.put_line('inserted');
      commit;

end;

/




name.sql is


--   NAME:       
define l_srv_name = 'Bill Payment4'



but when i run this...i dont get 'Bill Payment4'
inserted in the table

SQL> select * from yess;

C1
--------------------
&l_srv_name

SQL> 


can anyone please tell me how to make sure that Bill Payment4
gets inserted..

i mean, whats the right way to provide value for a character
substitution varialbe?

i am using single quote while using the variable in my
insert statement..then what went wrong here?

something related to set define?
Re: problem with substitution vairable [message #275880 is a reply to message #275878] Tue, 23 October 2007 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just have a look at the documentation for the meaning of "set define off" you used but don't know its purpose.

Regards
Michel
Re: problem with substitution vairable [message #275915 is a reply to message #275878] Tue, 23 October 2007 06:19 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
ok i said "set define on"

now my problem is

what would i do in this scenario?

create table yess1(c1 varchar2(20),c2 varchar2(20));


my ins.sql is

set define on
SET head off echo off termout ON feedback off verify off linesize 80
SET serveroutput ON SIZE 999999
@name.sql

BEGIN

    INSERT INTO yess1 values('&l_srv_name','/abc/uu?&cm');
   dbms_output.put_line('inserted');
      commit;

end;

/




the data has an & in it

my name.sql is

--   NAME:       
define l_srv_name = 'Bill Payment4'



now i said set define on..but it is asking for a value
because the data has an & in it
Re: problem with substitution vairable [message #275919 is a reply to message #275915] Tue, 23 October 2007 06:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You really should take a peek at the SQL*Plus User's Guide and Reference.

Why don't you use bind variables:
name.sql:
var l_srv_name VARCHAR2(20)

EXEC :l_srv_name := 'Bill Payment4'; 

Orafaq.sql:
CREATE TABLE yess1(c1 VARCHAR2(20),c2 VARCHAR2(20))
/

set define off
SET head off echo off termout ON feedback off verify off linesize 80
SET serveroutput ON SIZE 999999
@@name.sql

BEGIN
  INSERT INTO yess1 values(:l_srv_name,'/abc/uu?&cm');
  
  dbms_output.put_line('inserted');
  commit;
end;
/

SELECT * FROM yess1
/
DROP TABLE yess1 PURGE
/
If I run orafaq.sql I get this:
SQL> @orafaq
inserted

Bill Payment4        /abc/uu?&cm
But first things first: check out the manuals Wink.
MHE
Re: problem with substitution vairable [message #275926 is a reply to message #275878] Tue, 23 October 2007 06:55 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
thanks a lot

what difference does @@name.sql make?

cant i use @name.sql?

curious because i have never seen calling an sql file
using @@

Re: problem with substitution vairable [message #275932 is a reply to message #275926] Tue, 23 October 2007 07:19 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Didn't I advise you to read the manuals? Here's a link:
@@

MHE
Previous Topic: sql order by
Next Topic: Tuning SQL
Goto Forum:
  


Current Time: Fri Dec 02 16:23:43 CST 2016

Total time taken to generate the page: 0.47896 seconds