I am unable to create a plsql function inside a while loop.Is it possible to write plsql function inside while loop?

Question

        create or replace FUNCTION ACHEHBBDA40(p_sum_date IN VARCHAR2) RETURN NUMBER AS 
          CURSOR BISNES_T_INFO IS
          SELECT  a.TARGETID, a.CLIENTID,b.BSNSID
          FROM CLIENT_XREF_T a
          INNER JOIN BISNES_T b
          ON 
          a.CLIENTID = b.CLIENTID AND      
          a.TYPE = '1' AND               
          b.WORKKBN = '0';

        BEGIN
        DBMS_OUTPUT.PUT_LINE('PGM_NAME');
        LOOP
    BEGIN 
     FETCH BISNES_T_INFO
              INTO  l_compid,l_bsnsid,l_clientid;

      --beginning of nested function in declaration section 
               FUNCTION getClientXref(l_clientid VARCHAR2) RETURN Number AS 

                  CURSOR CLIENT_XREF_T_INFO IS
                  SELECT d.BRNO, d.TRNO
                  FROM CLIENT_XREF_T x INNER JOIN CLIENT_T c ON x.CLIENTID = c.CLIENTID;

                  BEGIN
                  OPEN CLIENT_XREF_T_INFO;
                      FETCH CLIENT_XREF_T_INFO
                      INTO  l_brno,l_trno
                  END;
                  return 1;
                END getClientXref;
    UTL_FILE.PUT_LINE(v_filehandle,l_compid ||CHR(9)|| l_brno ||CHR(9)|| l_trno);

EXCEPTION
 WHEN NO_DATA_FOUND THEN
    -- Close the file after the process is over
                    DBMS_OUTPUT.PUT_LINE('MSGID_ERREND : ' ||commonUtilities.GC_MSGID_ERREND);
                    DBMS_OUTPUT.PUT_LINE('MSG_ERREND : ' ||commonUtilities.GC_MSG_ERREND);
                    DBMS_OUTPUT.PUT_LINE('MSGID_NO_DATA : ' ||commonUtilities.GC_MSGID_NO_DATA);
                    DBMS_OUTPUT.PUT_LINE('MSG_NO_DATA : ' ||commonUtilities.GC_MSG_NO_DATA);
                    RETURN lc_failure;
                EXIT;
          END;


            END LOOP;

When i create the nested function getClientXref inside the while loop its throws the compile time error "Error(213,21): PLS-00103: Encountered the symbol "GETCLIENTXREF" when expecting one of the following: := . ( @ % ; " in sql developer.

Answer 1

Honestly I don't understand the purpose of the code you posted: it never calls the function you are trying to declare... But, since your question is about syntax, I have to correct what all other posters and commentators are saying:

You CAN declare procedures and functions inside a loop

The following code works!

  begin
     for c in (select * from dict) loop
        DECLARE 
            -- procedure inside a loop
            procedure local_print_current_row is
            begin
               -- here I am even accessing the external 
               -- "c" for loop variable
               dbms_output.put_line(c.table_name || ' -> ' || c.comments);
            end;    

        BEGIN
           local_print_current_row;
        END;
     end loop;
  end;

1. of course this is just a "toy" example to illustrate the syntax, but PL/SQL allows you to nest declarations (not only of variables) almost everywhere using the declare/begin/exception/end construct.
2. in your code you wrote the comment "--beginning of nested function in declaration section ", but you didn't actually define any nested declaration section. you need the DECLARE keyword.

A lot of people do not realize that in PL/SQL "begin/end" is not simply the same of "{"/"}" in java. The complete syntax of the begin/end block allows all these parts:

 DECLARE
    <declarations>
 BEGIN
    <code>
 EXCEPTION
    <exception handlers>
 END

It is just optional to write the "DECLARE" and "EXCEPTION" sections, but the BEGIN/END block is actually made of all the above parts.

in any DECLARE section you can declare:

1. function and procedures
2. types
3. cursors
4. variables
5. exceptions
6. ...

and all the things you declare in that section will be visible only within the corresponding begin[/exception]/end sections.

Moreover you can nest other blocks wherever you can write actual "runnable" code. This kind of nesting can be done:

declare
    ...
begin
      declare
         procedure MyLocalProc is
            procedure NestedProc is
            begin
            end;
         begin
            .... 
            declare
               ...
            begin
               ...
            exception
               ..
            end  
            ... 
         exception
         end    
      begin
         ...
      end 

exception when others then 
      declare
        ...
      begin
        ...  
      end 
end

P.S.:Note that after a "procedure is" or "function ... is" the "DECLARE" section is implicit: this is why you can start declaring stuff immediately after a procedure/function declaration without writing "declare". This does not happen for triggers, where you actually have to write "declare" if you want to add local declarations.

Answer 2

I don't really get it why you have to create a function inside your while loop. Functions were not really meant for that kind of approach. You can just create your function separately and call it each time you needed inside your while loop and pass your parameters.