Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Location
    Germany, near Munich
    Posts
    4

    Unanswered: PL/SQL Beginners question

    Sorry, I tried search, FAQ and everything:
    I'm new to PL/SQL and I try to create and execute the most simple function:

    create or replace function TESTONE( fname IN VARCHAR2 )
    return NUMBER
    is
    retval NUMBER(10,2);
    begin
    update TMP1 set f1 = 97;
    return 1;
    end;

    This "create" works without error message from Oracle, but when I try to execute:

    execute TESTONE('dummy');

    I get:

    BEGIN TESTONE('dummy'); END;

    *
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00201: identifier 'TESTONE' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Oracle Version is 9.2.
    Any ideas?
    Thanks!

    Thomas

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >execute TESTONE('dummy');
    This is not how a function is invoked.
    The fine PL/SQL Reference manual can be found at http://tahiti.oracle.com
    It appears you subscribe to the Ready, Fire, Aim approach to programming.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2005
    Location
    Germany, near Munich
    Posts
    4
    Quote Originally Posted by anacedent
    >execute TESTONE('dummy');
    This is not how a function is invoked.
    The fine PL/SQL Reference manual can be found at http://tahiti.oracle.com
    It appears you subscribe to the Ready, Fire, Aim approach to programming.
    Thanks for your reply, but it's of no use for me. I have the book "Oracle 9i The Complete Reference" by Loney/Koch. This an "official" Oracle book. It gives me this example:

    execute BOOK_MANAGEMENT.NEW_BOOK( ... some arguments ...);

    which looks very similar to what I tried, except that I don't use a package. I can't find another example of how to invoke a function.

    I just need a very, very, very simple example of a function, where e.g. one line is inserted into a table. How do I create the function? How do I call it?

    I'm asking here, since I don't want to pay for a one week course and read hundreds of pages, just for the most basic thing.

    I consulted the "PL/SQL User's Guide and Reference" and I still don't know what is wrong. Why don't I get I useful error message from Oracle?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >execute BOOK_MANAGEMENT.NEW_BOOK( ... some arguments ...);
    The line above invokes a procedure; which is not a FUNCTION.
    A FUNCTION returns a value. Right?
    In your code into what object should the returned value be placed?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    You might have seen some very useful errors
    like:

    Code:
    ERROR at line 1:
    ORA-14551: cannot perform a DML operation inside a query
    ORA-06512: at "SCOTT.TESTONE", line 6
    
         
    ERROR at line 1:
    ORA-06550: line 1, column 7:
    PLS-00221: 'TESTONE' is not a procedure or is undefined
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    IF you 'GOOGLE' the error number
    example:

    PLS-00221

    You would probably find something helpful:

    Oracle Error :: PLS-00221
    'string' is not a procedure or is undefined

    Cause
    An identifier being referenced as a procedure was not declared or actually represents another object (for example, it might have been declared as a function).

    Action
    Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.



    There is actually no need to spend any money to learn oracle. Everything you will need is on the web if you put forth a little effort.

    Here is an example of a simple and almost useless function.

    Code:
    CREATE OR REPLACE FUNCTION DayofBirthday( nDate IN VARCHAR2 ) RETURN VARCHAR IS
           RetText  VARCHAR2 (12);
        BEGIN
           SELECT TO_CHAR(TO_DATE(nDate, 'DD-MON-YYYY'), 'DAY')
           INTO RetText
           FROM dual;
           RETURN  RetText;
      END;
    /

    Code:
    SELECT dayofbirthday('31-OCT-2007') DAY FROM dual;
    Code:
    DAY
    --------------------------------------------------------------------------------
    WEDNESDAY
    I hope this helps.

    But, if it has been difficult for you to grasp the difference between functions and procedures after doing all of the research you say you have done,you should give up now.

Posting Permissions

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