Re: What's wrong with my PLSQL Function?

From: DH <v_dh_at_hotmail.com>
Date: 23 Apr 2003 22:19:17 -0700
Message-ID: <f5042544.0304232119.7cea14af@posting.google.com>

That's right, Sybrand!

The line
v_result := f_line(v_num,v_char);
is OK.

The problem is in the function f_line(). I correct it as below and it run as needed:

CREATE OR REPLACE FUNCTION f_line (v_number in INTEGER, v_character in VARCHAR2) RETURN VARCHAR2
IS

   v_line VARCHAR2(80);
   counter INTEGER := v_number;

BEGIN    WHILE counter > 0 LOOP

       v_line := v_line||v_character;
       counter := counter - 1;

   END LOOP;      RETURN v_line;  

 END;
/

Thanks again for your advise.

DH

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0304230133.74443dea_at_posting.google.com>...
> v_dh_at_hotmail.com (DH) wrote in message news:<f5042544.0304222234.51fb0a93_at_posting.google.com>...
> > Hi all,
> >
> > I'm learning in PL/SQL.
> > I create a little PLSQL block (disp_chars.sql) to execute the function
> > f_line() as below:
> >
> >
> > disp_chars.sql
> > ==============
> > set serveroutput on
> > set verify off
> >
> > CREATE OR REPLACE FUNCTION f_line (v_number in INTEGER, v_character in
> > VARCHAR2)
> > RETURN VARCHAR2
> > IS
> > v_line VARCHAR2(80) := '';
> >
> > BEGIN
> >
> > WHILE v_num > 0 LOOP
> > v_line := v_line + v_character;
> > v_num := v_num - 1;
> > END LOOP;
> >
> > RETURN v_line;
> >
> > END;
> > /
> > Declare
> > v_num INTEGER;
> > v_char VARCHAR2(1);
> > v_result VARCHAR2(80);
> > Begin
> > v_num := &number;
> > v_char := '&character';
> >
> > v_result := f_line(v_num,v_char);
> >
> > dbms_output.put_line(v_result);
> >
> > end;
> >
> > --------------------------------------------------------------------
> >
> > and It failed with the error below:
> >
> > Warning: Function created with compilation errors.
> >
> > Enter value for number: 8
> > Enter value for character: *
> > v_result := f_line(v_num,v_char);
> > *
> > ERROR at line 9:
> > ORA-06550: line 9, column 16:
> > PLS-00905: object SCOTT.F_LINE is invalid
> > ORA-06550: line 9, column 4:
> > PL/SQL: Statement ignored
> >
> > What's wrong with my function f_line() above?
> > How can I debug a PLSQL function?
> > I'm very newbie in Oracle PL/SQL.
> >
> > Thanks in advance for any help.
> >
> > DH
>
> There is no declaration for the variable v_num!
> show errors
> or
> select * from user_errors
> will tell you the compilation errors.
> No need to debug, your function simply didn't compile!
> Other than that your function is known as the RPAD function in Oracle.
>
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Thu Apr 24 2003 - 00:19:17 CDT