Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: PL/SQL Stored Function !

    Please consider following code:

    Could you explain:

    1) What does age() function doing below?

    2) After running it, I'm getting out put as 1,2,3,4 and it keeps on increasing
    infinitely and I had to quit SQL plus to get out of this infinite increasing sequence.

    3) Is there an way to get out without quitting the SQL Plus prompt?

    Thanks

    Code:
    create or replace function age (dateOfBirth date)
    return number
    is
    
      mAge number(5.2);
      
      begin
           mAge:=(sysdate-dateOfBirth)/365.25;
    	   return mAge;
      end;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I don't believe what you posted; since function contains syntax errors.
    Code:
    SQL> create or replace function age (dateOfBirth date)
      2  return number
      3  is
      4  
      5    mAge number(5.2);
      6  
      7    begin
      8         mAge:=(sysdate-dateOfBirth)/365.25;
      9             return mAge;
     10    end;
     11  /
    
    Warning: Function created with compilation errors.
    
    SQL> show error
    Errors for FUNCTION AGE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/8      PL/SQL: Item ignored
    5/15     PLS-00325: non-integral numeric literal 5.2 is inappropriate in
             this context
    
    8/8      PL/SQL: Statement ignored
    8/8      PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    9/5      PL/SQL: Statement ignored
    9/12     PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    
    SQL>
    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
    Apr 2013
    Posts
    41
    Well, I'm also getting the same error if I type all the code directly in the SQL Plus command prompt which is strange.

    But it will run if you will save it as ".sql" extension and then run the query in the
    following manner:

    @C:\Users\<username>\Desktop\Query.sql

    And it starts with
    Code:
    13,14,15,16,17,18 and so on...
    Please let me know what could be the problem.

    Thanks

    Quote Originally Posted by anacedent View Post
    I don't believe what you posted; since function contains syntax errors.
    Code:
    SQL> create or replace function age (dateOfBirth date)
      2  return number
      3  is
      4  
      5    mAge number(5.2);
      6  
      7    begin
      8         mAge:=(sysdate-dateOfBirth)/365.25;
      9             return mAge;
     10    end;
     11  /
    
    Warning: Function created with compilation errors.
    
    SQL> show error
    Errors for FUNCTION AGE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/8      PL/SQL: Item ignored
    5/15     PLS-00325: non-integral numeric literal 5.2 is inappropriate in
             this context
    
    8/8      PL/SQL: Statement ignored
    8/8      PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    9/5      PL/SQL: Statement ignored
    9/12     PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    
    SQL>

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Script should be terminated with a slash (/). Otherwise, it just expects you to enter next command (and there's none) but it shows line numbers instead. Therefore: slash, <Enter>.
    Code:
    SQL> @query.sql
      7
      8
      9
     10
     11
     12
     13
     14
     15  /
    
    Function created.
    
    SQL>
    As of "what is that function doing" question: what do you think? It accepts a DATE datatype parameter. SYSDATE returns date. Do you know what is difference of two DATE datatype values? It is a number of days between these two dates. What happens when you divide number of days with 365.25? I'll leave that mystery to you.

  5. #5
    Join Date
    Apr 2013
    Posts
    41
    Thanks for your reply. I'm still getting error even after terminating with slash. And I'm getting the following out put:
    Code:
     SQL> @query.sql
    13
    14
    15  /
    
    Warning: Function created with compilation errors.
    And when I click on Show error, I get the following:

    Code:
    SQL> show error
    Errors for FUNCTION AGE:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/8      PL/SQL: Item ignored
    5/15     PLS-00325: non-integral numeric literal 5.2 is inappropriate in
             this context
    
    8/8      PL/SQL: Statement ignored
    8/8      PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    9/5      PL/SQL: Statement ignored
    9/12     PLS-00320: the declaration of the type of this expression is
             incomplete or malformed
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    How come your code is running fine?


    Quote Originally Posted by Littlefoot View Post
    Script should be terminated with a slash (/). Otherwise, it just expects you to enter next command (and there's none) but it shows line numbers instead. Therefore: slash, <Enter>.
    Code:
    SQL> @query.sql
      7
      8
      9
     10
     11
     12
     13
     14
     15  /
    
    Function created.
    
    SQL>
    As of "what is that function doing" question: what do you think? It accepts a DATE datatype parameter. SYSDATE returns date. Do you know what is difference of two DATE datatype values? It is a number of days between these two dates. What happens when you divide number of days with 365.25? I'll leave that mystery to you.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Because I wrote a valid function code, stored it into a QUERY.SQL and ran that script.

    As you didn't fix errors Oracle generously explained, no wonder that you still have errors. Fix them first.

  7. #7
    Join Date
    Apr 2013
    Posts
    41
    I have fixed the 5.2 related error ( by changing it to 5,2) and now I'm getting Insufficient Privileges error. Could anyone tell me how to get rid if this error?

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Could anyone tell me how to get rid if this error?
    issue GRANT
    I can't provide more details since you provide no clue what specific SQL threw the error.

    Is COPY & PASTE broken for you?
    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.

  9. #9
    Join Date
    Apr 2013
    Posts
    41
    Well, I tried to figure out privilege problem: Here are the steps I followed: In my Windows command prompt I logged in as follows:

    Code:
    sqlplus / as sysdba
    I got the SQL> prompt over there and then I tried to grant privilige on my function "age" as follows:

    Code:
    SQL> GRANT EXECUTE on AGE to SCOTT;
    I got the error ORA-0402: function body doesn't exist. How can I save my function body as I'm just running "Query.sql" file on SQL Plus.In other words how can I make sure the function exists before issuing grant?

    Please help !


    Quote Originally Posted by anacedent View Post
    >Could anyone tell me how to get rid if this error?
    issue GRANT
    I can't provide more details since you provide no clue what specific SQL threw the error.

    Is COPY & PASTE broken for you?

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that your user doesn't have a privilege to create a procedure. Therefore,
    Code:
    grant create procedure to your_user
    By the way, as the owner, you don't need any additional privileges to use that procedure/function (therefore, no "grant execute" at all).

Posting Permissions

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