Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103 (SQL*PLUS /10.2 /XP)
PLS-00103 [message #315185] Sat, 19 April 2008 15:11 Go to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Hi everyone,
I get this error PLS-00103 but could not figure out what is wrong with it. Pls help
SQL> Set serveroutput on
SQL> Create Or Replace FUNCTION AdjustString (p_String OUT Varchar2(50),
  2    p_Req_length IN Number)
  3    Return Varchar2 IS
  4
  5    v_String Varchar(50);
  6    v_Return_string Varchar2(50);
  7    v_String_Length Number;
  8    v_err_code NUMBER;
  9    v_err_msg VARCHAR2(200);
 10
 11  Begin
 12    DBMS_OUTPUT.PUT_LINE('The original string: '||p_String);
 13    v_String := TRIM(Leading ' ' From p_String);
 14    v_String_length := Length(v_String);
 15
 16    If v_String_length < p_Req_length Then
 17      v_Return_string := RPAD(v_String,p_Req_length,' ');
 18      Return v_Return_string;
 19      DBMS_OUTPUT.PUT_LINE(v_String||v_String_length);
 20    Elsif v_String_length > p_Req_length Then
 21      v_Return_string := SUBSTR(v_String,1,p_Req_length);
 22      Return v_Return_string;
 23    Else
 24      v_Return_string := v_String;
 25      Return v_Return_string;
 26    End if;
 27    DBMS_OUTPUT.PUT_LINE('The adjusted string: '||v_Return_string);
 28  Exception
 29    WHEN OTHERS THEN
 30      v_err_code := SQLCODE;
 31      v_err_msg := SUBSTR(SQLERRM, 1, 200);
 32      DBMS_OUTPUT.PUT_LINE('Error code: '||v_err_code);
 33      DBMS_OUTPUT.PUT_LINE('Error message: '||v_err_msg);
 34  End AdjustString;
 35  /

Warning: Function created with compilation errors.

SQL> SHOW ERROR;
Errors for FUNCTION ADJUSTSTRING:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/45     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.


[Edit MC: add code tags]

[Updated on: Sat, 19 April 2008 15:22] by Moderator

Report message to a moderator

Re: PLS-00103 [message #315188 is a reply to message #315185] Sat, 19 April 2008 15:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Parameters can't be constrained, you have to use VARCHAR2 without any length.

Thanks for the code with line numbers. Next time, use code tags as I did it for you.

Regards
Michel

[Updated on: Sat, 19 April 2008 15:24]

Report message to a moderator

Re: PLS-00103 [message #315190 is a reply to message #315188] Sat, 19 April 2008 16:39 Go to previous messageGo to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Thanks Michel. That's a great help. I made changes on my code and compiled it without any error, but I still can not get the result I want. The function adjusts a string to a specific length. Any leading spaces from input string needs deleted. Adding spaces to the right if a required length is greater than actual length. Truncating characters on the right if a required length is less than actual length.
ex: adjust_string(' Now is the Time. ',6) should display
Now is
Here is my revised code with test driver. Your inputs are greatly appreciated.
SQL> Set serveroutput on
SQL> CREATE OR REPLACE FUNCTION Adjust_String(
2 p_String OUT Varchar2,
3 p_Req_length IN Number)
4 RETURN Varchar2 IS
5
6 v_String Varchar(50);
7 v_Return_string Varchar2(50);
8 v_String_Length Number;
9 v_err_code NUMBER;
10 v_err_msg VARCHAR2(200);
11
12 Begin
13 v_String := TRIM(Leading ' ' From p_String);
14 v_String_length := Length(v_String);
15
16 If v_String_length < p_Req_length Then
17 v_Return_string := RPAD(v_String,p_Req_length,' ');
18 Return v_Return_string;
19 Elsif v_String_length > p_Req_length Then
20 v_Return_string := SUBSTR(v_String,1,p_Req_length);
21 Return v_Return_string;
22 Else
23 v_Return_string := v_String;
24 Return v_Return_string;
25 End if;
26 Exception
27 WHEN OTHERS THEN
28 v_err_code := SQLCODE;
29 v_err_msg := SUBSTR(SQLERRM, 1, 200);
30 DBMS_OUTPUT.PUT_LINE('Error code: '||v_err_code);
31 DBMS_OUTPUT.PUT_LINE('Error message: '||v_err_msg);
32 End Adjust_String;
33 /

Function created.

SQL> -- Driver for Adjust_String
SQL> Declare
2 Result Varchar2(100);
3 String1 Varchar2(100) := '&String';
4 Begin
5 Result := Adjust_String(string1, 6);
6 DBMS_OUTPUT.PUT_LINE('The original string: ' || String1);
7 DBMS_OUTPUT.PUT_LINE('The adjust string: ' || Result);
8 End;
9 /
Enter value for string: Now is the Time.
old 3: String1 Varchar2(100) := '&String';
new 3: String1 Varchar2(100) := ' Now is the Time. ';
The original string:
The adjust string:

PL/SQL procedure successfully completed.

Miche, I'm not sure what you mean by "next time tag the code as I did.."
Re: PLS-00103 [message #315193 is a reply to message #315190] Sat, 19 April 2008 17:09 Go to previous messageGo to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
I see what you mean Michel, but how did you do that? It looks right when I cut and paste in the window message and It looks differently when I upload it.
Re: PLS-00103 [message #315195 is a reply to message #315185] Sat, 19 April 2008 17:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
are posting guidelines including how to use <code tags>
Re: PLS-00103 [message #315200 is a reply to message #315185] Sat, 19 April 2008 18:10 Go to previous messageGo to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Thanks Michel. That's a great help. I made changes on my code and compiled it without any error, but I still can not get the result I want. The function adjusts a string to a specific length. Any leading spaces from input string needs deleted. Adding spaces to the right if a required length is greater than actual length. Truncating characters on the right if a required length is less than actual length.
ex: adjust_string(' Now is the Time. ',6) should display
Now is
Here is my revised code with test driver. Your inputs are greatly appreciated.

SQL> Set serveroutput on
SQL> CREATE OR REPLACE FUNCTION Adjust_String(
  2    p_String OUT Varchar2,
  3    p_Req_length IN Number)
  4    RETURN Varchar2 IS
  5
  6    v_String Varchar(50);
  7    v_Return_string Varchar2(50);
  8    v_String_Length Number;
  9    v_err_code NUMBER;
 10    v_err_msg VARCHAR2(200);
 11
 12  Begin
 13    v_String := TRIM(Leading ' ' From p_String);
 14    v_String_length := Length(v_String);
 15
 16    If v_String_length < p_Req_length Then
 17      v_Return_string := RPAD(v_String,p_Req_length,' ');
 18      Return v_Return_string;
 19    Elsif v_String_length > p_Req_length Then
 20      v_Return_string := SUBSTR(v_String,1,p_Req_length);
 21      Return v_Return_string;
 22    Else
 23      v_Return_string := v_String;
 24      Return v_Return_string;
 25    End if;
 26  Exception
 27    WHEN OTHERS THEN
 28      v_err_code := SQLCODE;
 29      v_err_msg := SUBSTR(SQLERRM, 1, 200);
 30      DBMS_OUTPUT.PUT_LINE('Error code: '||v_err_code);
 31      DBMS_OUTPUT.PUT_LINE('Error message: '||v_err_msg);
 32  End Adjust_String;
 33  /

Function created.

SQL> -- Driver for Adjust_String
SQL> Declare
  2    Result Varchar2(100);
  3    String1 Varchar2(100) := '&String';
  4  Begin
  5    Result := Adjust_String(string1, 6);
  6    DBMS_OUTPUT.PUT_LINE('The original string: ' || String1);
  7    DBMS_OUTPUT.PUT_LINE('The adjust string: ' || Result);
  8  End;
  9  /
Enter value for string:    Now is the Time.
old   3:   String1 Varchar2(100) := '&String';
new   3:   String1 Varchar2(100) := '   Now is the Time.   ';
The original string:
The adjust string:

PL/SQL procedure successfully completed.

Thanks for showing me how to use code tag anacedent.

[Updated on: Sat, 19 April 2008 18:19]

Report message to a moderator

Re: PLS-00103 [message #315214 is a reply to message #315200] Sun, 20 April 2008 01:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You have no IN parameter, so there will never be a way to let the function know which string to manipulate.
Re: PLS-00103 [message #315230 is a reply to message #315214] Sun, 20 April 2008 02:50 Go to previous messageGo to next message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Hi Frank,
It's awsome. I just added an IN to my string variable and everything comes out perfectly. Thanks again.
Re: PLS-00103 [message #315247 is a reply to message #315230] Sun, 20 April 2008 06:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you change it to
p_string IN varchar2

or to
p_string IN OUT varchar2 ?


Although both versions are correct syntactically, the first one would be the correct version logically, since you already return the result-string from your function.
Re: PLS-00103 [message #315257 is a reply to message #315247] Sun, 20 April 2008 09:32 Go to previous message
learnSQL
Messages: 19
Registered: April 2008
Location: Texas
Junior Member
Thanks for pointing that out Frank. I did change to
p_string IN varchar2 because that parameter is just used for read onle purpose.
Have a nice weekend!
Previous Topic: LPAD space filled output
Next Topic: Need sql to create test data , already searched the old posts
Goto Forum:
  


Current Time: Sat Dec 10 09:01:28 CST 2016

Total time taken to generate the page: 0.08229 seconds