Results 1 to 3 of 3

Thread: pl/sql function

  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unhappy Unanswered: pl/sql function

    this is a simple code how to write a funtion.
    I got an error. it says
    ------------------------
    end;
    *
    ERROR at line 17:
    ORA-06550: line 17, column 4:
    PLS-00103: Encountered the symbol "end-of-file" when expecting
    following:
    begin function package pragma procedure form
    ------------------------------------------------------
    code starts from here.

    would you please tell me what it is worng?
    thank you
    ------------------------------------

    SET serveroutput on
    set verify off

    ACCEPT date_of_convert prompt 'Please enter data you want to convert into MM/DD/YYYY:'

    declare

    date_prompt varchar2(100);

    function convert_date (date_prompt in out varchar2)
    return varchar2

    IS
    date_in varchar2(100);
    date_result varchar2(100);

    begin
    date_prompt:='&date_of_convert';
    date_in := date_prompt;
    date_result := to_date(date_in, 'mmddyyyy');
    return date_result;
    dbms_output.put_line(date_result);
    end;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    At a minimum another END is needed to terminate the code block which starts with the "DECLARE".

  3. #3
    Join Date
    Feb 2004
    Location
    Madison, WI
    Posts
    1

    Re: pl/sql function

    Originally posted by muykc1
    this is a simple code how to write a funtion.
    I got an error. it says
    ------------------------
    end;
    *
    ERROR at line 17:
    ORA-06550: line 17, column 4:
    PLS-00103: Encountered the symbol "end-of-file" when expecting
    following:
    begin function package pragma procedure form
    ------------------------------------------------------
    code starts from here.

    would you please tell me what it is worng?
    thank you
    ------------------------------------

    SET serveroutput on
    set verify off

    ACCEPT date_of_convert prompt 'Please enter data you want to convert into MM/DD/YYYY:'

    declare

    date_prompt varchar2(100);

    function convert_date (date_prompt in out varchar2)
    return varchar2

    IS
    date_in varchar2(100);
    date_result varchar2(100);

    begin
    date_prompt:='&date_of_convert';
    date_in := date_prompt;
    date_result := to_date(date_in, 'mmddyyyy');
    return date_result;
    dbms_output.put_line(date_result);
    end;
    /
    This depends on what you are trying to accomplish.
    Do you want to create a stored function called "convert_date"
    or are you just trying to run a sqlplus script? It looks like the latter in which case remove

    function convert_date (date_prompt in out varchar2)
    return varchar2

    IS

    and you should be set.
    If you DO want a stored function, then you need:
    CREATE or REPLACE function ......

    But then there will be other bugs since you would need a 'return' line at the end of your function, and you also would need to call the function.

    From what it looks like, I think using the first solution I suggested would give you what you want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •