Changing the value via procedure [message #412682] |
Fri, 10 July 2009 10:47  |
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   |
ThomasG
Messages: 3212 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 #412689 is a reply to message #412688] |
Fri, 10 July 2009 11:04   |
ThomasG
Messages: 3212 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 #412900 is a reply to message #412697] |
Mon, 13 July 2009 05:48   |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #412928 is a reply to message #412902] |
Mon, 13 July 2009 07:51   |
joy_division
Messages: 4963 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 #412981 is a reply to message #412686] |
Mon, 13 July 2009 15:49   |
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!
|
|
|
|
|