Home » SQL & PL/SQL » SQL & PL/SQL » Check variable of cursor negative or not? -- error
Check variable of cursor negative or not? -- error [message #344224] Fri, 29 August 2008 02:25 Go to next message
JuzMe
Messages: 6
Registered: August 2008
Location: Singapore
Junior Member

I understood I should use the Sign variable like this: SIGN(variable)


However when I tried this to check if my variable is negative:

IF SIGN(c3_rec.amount) = -1 then
-- c3_rec.amount is variable from cursor


I encountered this error:

PLS-00103: Encountered the symbol "SIGN" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "SIGN" to continue.



How do I solve this error?
Re: Check variable of cursor negative or not? -- error [message #344237 is a reply to message #344224] Fri, 29 August 2008 03:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I am afraid that you have syntax error in the piece of code you did not post, as SIGN function may be used in PL/SQL expression at least in Oracle 10g (as you did not post yours, I suppose that you use the supported one), as demonstrated below:
SQL> set serveroutput on
SQL> declare
  2    cursor c_test is
  3      select level - 2 val from dual connect by level <= 3;
  4  begin
  5    for r_test in c_test loop
  6     if sign( r_test.val ) = -1 then dbms_output.put_line( to_char(r_test.val)||' is negative' );
  7     else dbms_output.put_line( to_char(r_test.val)||' is positive' );
  8     end if;
  9    end loop;
 10  end;
 11  /
-1 is negative
0 is positive
1 is positive

PL/SQL procedure successfully completed.

SQL> 
Re: Check variable of cursor negative or not? -- error [message #344290 is a reply to message #344224] Fri, 29 August 2008 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I solve this error?

Post the rest of the code, above the lines just before this one.

Regards
Michel
Re: Check variable of cursor negative or not? -- error [message #344475 is a reply to message #344224] Fri, 29 August 2008 23:27 Go to previous messageGo to next message
swarit_gupta
Messages: 4
Registered: August 2008
Junior Member
hi,
the code seems to be fine
check the values of c3_rec.amount, i suppose this is the cause.
otherwise IF sign(c3_rec.amount)=-1 ....is fine

Regards Cool
Re: Check variable of cursor negative or not? -- error [message #344494 is a reply to message #344475] Sat, 30 August 2008 01:11 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
check the values of c3_rec.amount, i suppose this is the cause.

How the value of a variable can lead to a COMPILATION error?

Obviously the code is wrong but not in the part OP posted it.

Regards
Michel
Previous Topic: refcursor as in parameter to the procedure
Next Topic: data migration from db2 to oracle (merged)
Goto Forum:
  


Current Time: Fri Dec 02 18:48:49 CST 2016

Total time taken to generate the page: 0.17180 seconds