Home » SQL & PL/SQL » SQL & PL/SQL » Changing the value via procedure
Changing the value via procedure [message #412682] Fri, 10 July 2009 10:47 Go to next message
AnnaOracle
Messages: 6
Registered: July 2009
Junior Member
Hello All,

I am trying to change the value of the global var "V_PHONE"

SP:
CREATE OR REPLACE PROCEDURE CHANGE_PHONE
(V_PHONE_NO IN OUT VARCAHR2)
IS
BEGIN
V_PHONE_NO := SUBSTR(V_PHONE_NO,1,3||'-'SUBSTR(V_PHONE_NO,4,4);
END CHANGE_PHONE;


==================================
SQL> print v_phone
V_PHONE
--------------------------------
5555555

SQL> exec change_phone(:v_phone);
BEGIN change_phone(:v_phone); END;
      *
ERROR at line 1:ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.CHANGE_PHONE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored 


thanks!
Re: Changing the value via procedure [message #412686 is a reply to message #412682] Fri, 10 July 2009 10:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The procedure has syntax errors. You can view them with "show err" directly after the "Warning: Procedure created with compilation errors."

SQL> CREATE OR REPLACE PROCEDURE CHANGE_PHONE
  2  (V_PHONE_NO IN OUT VARCAHR2)
  3  IS
  4  BEGIN
  5  V_PHONE_NO := SUBSTR(V_PHONE_NO,1,3||'-'SUBSTR(V_PHONE_NO,4,4);
  6  END CHANGE_PHONE;
  7  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE CHANGE_PHONE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/41     PLS-00103: Encountered the symbol "SUBSTR" when expecting one of
         the following:
         ) , * & | = - + < / > at in is mod remainder not rem => ..
         <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
         LIKE4_ LIKEC_ between || member SUBMULTISET_
         The symbol "," was substituted for "SUBSTR" to continue.

5/63     PLS-00103: Encountered the symbol ";" when expecting one of the
         following:
         . ( ) , * % & | = - + < / > at in is mod remainder not range
         rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or

LINE/COL ERROR
-------- -----------------------------------------------------------------
         like LIKE2_ LIKE4_ LIKEC_ between || multiset member
         SUBMULTISET_
         The symbol ")" was substituted for ";" to continue.

SQL>
Re: Changing the value via procedure [message #412687 is a reply to message #412682] Fri, 10 July 2009 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I doubt VARCAHR2 is a valid datatype.

Regards
Michel
Re: Changing the value via procedure [message #412688 is a reply to message #412687] Fri, 10 July 2009 11:01 Go to previous messageGo to next message
AnnaOracle
Messages: 6
Registered: July 2009
Junior Member
it is valid
Re: Changing the value via procedure [message #412689 is a reply to message #412688] Fri, 10 July 2009 11:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No it isn't.

SQL> CREATE OR REPLACE PROCEDURE CHANGE_PHONE
  2  (V_PHONE_NO IN OUT VARCAHR2)
  3  IS
  4  BEGIN
  5  NULL;
  6  END;
  7  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE CHANGE_PHONE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/20     PLS-00201: identifier 'VARCAHR2' must be declared
SQL>
Re: Changing the value via procedure [message #412697 is a reply to message #412689] Fri, 10 July 2009 12:35 Go to previous messageGo to next message
AnnaOracle
Messages: 6
Registered: July 2009
Junior Member
sorry, i have not seen..not spelled right
Re: Changing the value via procedure [message #412900 is a reply to message #412697] Mon, 13 July 2009 05:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Adn, because no-one else has said it yet - Don't create things in the SYS/SYSTEM schemas.

They're for oracle DB administration, not for you to play around in.
Create a user, grant some roles and privileges to that user, and use that.
Re: Changing the value via procedure [message #412902 is a reply to message #412900] Mon, 13 July 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Adn, because no-one else has said it yet - Don't create things in the SYS/SYSTEM schemas.

This is very weird, I'm pretty sure when I answered there was a post (from BlackSwan I think) saying something like "You don't create your procedure in SYSTEM schema, do you?".

Regards
Michel

[Updated on: Mon, 13 July 2009 06:02]

Report message to a moderator

Re: Changing the value via procedure [message #412915 is a reply to message #412902] Mon, 13 July 2009 06:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Odd - is there a log anywhere of posts deleted?
Re: Changing the value via procedure [message #412919 is a reply to message #412915] Mon, 13 July 2009 07:15 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I remember well, Frank Naude can tell who deleted what and when.
Re: Changing the value via procedure [message #412928 is a reply to message #412902] Mon, 13 July 2009 07:51 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Mon, 13 July 2009 07:00

This is very weird, I'm pretty sure when I answered there was a post (from BlackSwan I think) saying something like "You don't create your procedure in SYSTEM schema, do you?".



I do remember that, but I remember it being in a different post. I do remember two posts within a day or two range that were like this, but I remember the one Blackie responded to was in the SYS schema.
Re: Changing the value via procedure [message #412933 is a reply to message #412928] Mon, 13 July 2009 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joy_division wrote on Mon, 13 July 2009 14:51
I do remember that, but I remember it being in a different post. I do remember two posts within a day or two range that were like this, but I remember the one Blackie responded to was in the SYS schema.

OK, maybe (most likely) I remember wrongly, I read so many posts. Wink

Regards
Michel

Re: Changing the value via procedure [message #412981 is a reply to message #412686] Mon, 13 July 2009 15:49 Go to previous messageGo to next message
ez2009
Messages: 1
Registered: July 2009
Junior Member
Besides misspelled datatype, all other errors reside on line #5 where V_PHONE_NO gets a makeover.

Hints:
1) number of opening parentheses should be equal to number of closing parentheses;
2) there is missing concatenation operator.

HTH
Good luck!
Re: Changing the value via procedure [message #412990 is a reply to message #412902] Mon, 13 July 2009 18:40 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Mon, 13 July 2009 04:00
Quote:
Adn, because no-one else has said it yet - Don't create things in the SYS/SYSTEM schemas.

This is very weird, I'm pretty sure when I answered there was a post (from BlackSwan I think) saying something like "You don't create your procedure in SYSTEM schema, do you?".

Regards
Michel




http://www.orafaq.com/forum/m/412512/136107/#msg_412514
Re: Changing the value via procedure [message #413011 is a reply to message #412990] Tue, 14 July 2009 00:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it was not the same topic but the same person yet.
Thanks.

Michel
Previous Topic: Porblem with Procedure...
Next Topic: Performance ramifications Parallel hint.(merged 2) 10g
Goto Forum:
  


Current Time: Sat Dec 03 06:06:13 CST 2016

Total time taken to generate the page: 0.17948 seconds