Oracle Error PLS-00103

I am trying to create a function in oracle 10g that will concatonate output all [authoriztion numbers] in a single comma delimted string that are associated with a parcel number
(ie var file_number_in).  

When I try to create the function, I get a parse error:
PLS-00103: Encountered the symbol "1" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor external language

The error is coming from Line 5 position 4 of the code  at the 1_str string.

Any ideas as to what I am doing wrong?




CREATE  or replace FUNCTION   "FCN_GET_MHPAR"  (
 file_number_in in varchar2 )
 return varchar2
 is
   1_str  varchar2(2000) default null;
   1_sep  varchar2(3) default null;
 begin
   for x in ( Select (MISC_VALUE) AS PARCEL
              FROM LASDW.V_CF_TX_MISC
              WHERE LASDW.V_CF_TX_MISC.FILE_NUMBER = file_number_in
               AND LASDW.V_CF_TX_MISC.TRANSACTION_CODE = 'MHPAR'
                ORDER BY LASDW.V_CF_TX_MISC.MISC_VALUE)
  loop
      1_str := 1_str || 1_sep || x.PARCEL;
      1_sep := ', ';
   end loop;
   return 1_str;
 end;

Open in new window

steveweemsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndytwCommented:
The problem is with your variable names 1_STR and 1SEP.  Identifiers in PL/SQL cannot start with a numeric character.  
"An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs"

Use L_STR instead of  1_STR.


create or replace FUNCTION   "FCN_GET_MHPAR"  (
 file_number_in in varchar2 )
 return varchar2
 is
   l_str  varchar2(2000) default null;
   l_sep  varchar2(3) default null;
 begin
   for x in ( Select (MISC_VALUE) AS PARCEL
              FROM LASDW.V_CF_TX_MISC
              WHERE LASDW.V_CF_TX_MISC.FILE_NUMBER = file_number_in
               AND LASDW.V_CF_TX_MISC.TRANSACTION_CODE = 'MHPAR'
                ORDER BY LASDW.V_CF_TX_MISC.MISC_VALUE)
  loop
      l_str := l_str || l_sep || x.PARCEL;
      l_sep := ', ';
   end loop;
   return l_str;
 end;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steveweemsAuthor Commented:
Thanks for your help. That did fix the error.  I now get another error that may be related to privs but I am going to check with our DBA about that issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.