Home » SQL & PL/SQL » SQL & PL/SQL » how to use the new_value to pass the variable (oracle 10g/9i)
how to use the new_value to pass the variable [message #410202] Thu, 25 June 2009 11:58 Go to next message
cakzxd
Messages: 4
Registered: June 2009
Junior Member
I am trying to use the new_value to pass the variable in my script.

column losnapid new_value losnapid format 9999999999;
select min(snap_id) losnapid from perfstat.stats$snapshot;
variable vlosnapid number;
begin
vlosnapid:=&losnapid;
end;
/

It worked. but if the table perfstat.stats$snapshot is truncated, and select did bot get any result. there is an error.
vlosnapid:= -~;
*
ERROR at line 2:
ORA-06550: line 2, column 21:
PLS-00103: Encountered the symbol "~" when expecting one of the following:
( - + case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current max min prior sql stddev sum variance execute
forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quoted SQL string>


How to fix this problem? Thanks

Kevin

[Updated on: Thu, 25 June 2009 12:23]

Report message to a moderator

Re: how to use the new_value to pass the variable [message #410204 is a reply to message #410202] Thu, 25 June 2009 12:04 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


after trancate your table try try to execute below query

select snap_id from perfstat.stats$snapshot
Re: how to use the new_value to pass the variable [message #410205 is a reply to message #410202] Thu, 25 June 2009 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How to fix this problem?
NVL() function?
Re: how to use the new_value to pass the variable [message #410206 is a reply to message #410204] Thu, 25 June 2009 12:11 Go to previous messageGo to next message
cakzxd
Messages: 4
Registered: June 2009
Junior Member
Hi gentlebabu,

Thank you,
The perfstat.stats$snapshot is just a sample to test the measure to pass the variable by using the new_value.
The key of my question is, if the select does not get any result, how to pass the value to a variable in a script by using the new_value (to void the ORA-06550 error).
Thanks

Kevin
Re: how to use the new_value to pass the variable [message #410210 is a reply to message #410205] Thu, 25 June 2009 12:38 Go to previous messageGo to next message
cakzxd
Messages: 4
Registered: June 2009
Junior Member

Hi BlackSwan,

Thanks

I tried to replace the
vlosnapid:=&losnapid;
by the
vlosnapid:=NVL(&losnapid,0);

It still does not work. same error,

ERROR at line 2:
ORA-06550: line 2, column 25:
PLS-00103: Encountered the symbol "~" when expecting one of the following:
( - + case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current max min prior sql stddev sum variance execute
forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quoted SQL string>
Re: how to use the new_value to pass the variable [message #410211 is a reply to message #410210] Thu, 25 June 2009 12:48 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
try the NVL here:
select nvl(min(snap_id),0) losnapid from perfstat.stats$snapshot;


Oh, and I just noticed, you need to DECLARE vlosnapid.

[Updated on: Thu, 25 June 2009 12:52]

Report message to a moderator

Re: how to use the new_value to pass the variable [message #410212 is a reply to message #410211] Thu, 25 June 2009 12:59 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
SQL> column losnapid new_value losnapid format 9999999999;
SQL> select nvl(min(1),0) losnapid from dual where 1 = 3;

   LOSNAPID
-----------
          0

SQL> variable vlosnapid number;
SQL> begin
  2  vlosnapid:=&losnapid;
  3  end;
  4  /
old   2: vlosnapid:=&losnapid;
new   2: vlosnapid:=         0;
vlosnapid:=         0;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'VLOSNAPID' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
You should use ":"
SQL> begin
  2  :vlosnapid:=&losnapid;
  3  end;
  4  /
old   2: :vlosnapid:=&losnapid;
new   2: :vlosnapid:=         0;

PL/SQL procedure successfully completed.
By
Vamsi

[Updated on: Thu, 25 June 2009 13:03]

Report message to a moderator

Re: how to use the new_value to pass the variable [message #410213 is a reply to message #410202] Thu, 25 June 2009 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, Aim

SQL> @rfa
SQL> set term on echo on serveroutput on
SQL> drop table cakzxd;

Table dropped.

SQL> create table cakzxd (snap_id number);

Table created.

SQL> column losnapid new_value losnapid format 9999999999;
SQL> select nvl(min(snap_id),0) losnapid from cakzxd;

   LOSNAPID
-----------
	  0

SQL> declare
  2  vlosnapid number;
  3  begin
  4  dbms_output.enable(10000);
  5  vlosnapid:=&losnapid;
  6  dbms_output.put_line('Empty table = '||vlosnapid);
  7  end;
  8  /
old   5: vlosnapid:=&losnapid;
new   5: vlosnapid:=	     0;
Empty table = 0

PL/SQL procedure successfully completed.

SQL> INSERT into cakzxd values(1);

1 row created.

SQL> INSERT into cakzxd values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select nvl(min(snap_id),0) losnapid from cakzxd;

   LOSNAPID
-----------
	  1

SQL> declare
  2  vlosnapid number;
  3  begin
  4  dbms_output.enable(10000);
  5  vlosnapid:=&losnapid;
  6  dbms_output.put_line('NOT Empty table = '||vlosnapid);
  7  end;
  8  /
old   5: vlosnapid:=&losnapid;
new   5: vlosnapid:=	     1;
NOT Empty table = 1

PL/SQL procedure successfully completed
Re: how to use the new_value to pass the variable [message #410221 is a reply to message #410213] Thu, 25 June 2009 13:59 Go to previous message
cakzxd
Messages: 4
Registered: June 2009
Junior Member
It works, thank you,BlackSwan,joy_division,and vamsi kasina.
Thank you for all the input.
Previous Topic: Display Oracle Data in Hierarchical format
Next Topic: Home Work Question - Updating View
Goto Forum:
  


Current Time: Fri Dec 09 04:03:47 CST 2016

Total time taken to generate the page: 0.09017 seconds