Home » SQL & PL/SQL » SQL & PL/SQL » Calling Function
Calling Function [message #267164] Wed, 12 September 2007 11:18 Go to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello..
I have a situation where in i have to test whther the function is working properly or not .The function is being called from a Package ...can any help me out how to test a function from a package ..BELOW IS THE PACKAGE AND THE FUCTION WITHIN IT...I have to check the last function SHIPPING_DAYS_USED
CREATE OR REPLACE PACKAGE BODY.  .SAP_STAGING.LOAD_DAILY_TABLES
IS
   commit_point           NUMBER;
   err_msg                VARCHAR2 (100);
   err_num                NUMBER;
   v_cursor               NUMBER;
--   v_current_period       NUMBER;
--   v_shipping_days        NUMBER;
--   v_shipping_days_used   NUMBER;
--   v_load_date            DATE;

    FUNCTION shipping_days
        RETURN number
    IS
        v_shipping_days number;
    BEGIN
        SELECT shipping_days
          INTO v_shipping_days
          FROM dw_admin.period_Ref_monthly
         WHERE cy_cp = 'Y';
        RETURN v_shipping_days;
    END;

--/*  Get the number of shipping days for the current period */
--   CURSOR c4
--   IS
--      SELECT shipping_days
--        FROM dw_admin.period_ref_monthly
--       WHERE cy_cp = 'Y';

    FUNCTION shipping_days_used
        RETURN number
    IS
        v_shipping_days_used number;
    BEGIN
        SELECT COUNT (DISTINCT (billing_date)) 
          INTO v_shipping_days_used
          FROM dw_admin.sales_fact c
         WHERE billing_date IN (SELECT oracle_date
                                  FROM period_ref
                                 WHERE shipping_day = 'Y' AND cy_cp = 'Y');
        --Currently the trended net sales is 
        --averaged over the shipping days used. When the
        --average shipping days is 0, the load aborts. 
        --Defaulting to 1 will return a trended net sales
        --equal to the net sales.
        RETURN (DECODE(v_shipping_days_used,0,1));
    END;

Re: Calling Function [message #267171 is a reply to message #267164] Wed, 12 September 2007 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check it the same way you do it for a standalone function.
What is the problem?

Regards
Michel
Re: Calling Function [message #267174 is a reply to message #267164] Wed, 12 September 2007 11:33 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello,
I ahve tried it but there is a syntax error..can you give a suggestion how to call the function and check it whther it is giving out any value...

thanks
Re: Calling Function [message #267177 is a reply to message #267174] Wed, 12 September 2007 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First fix the syntax error.

Regards
Michel
Re: Calling Function [message #267178 is a reply to message #267164] Wed, 12 September 2007 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I ahve tried it but there is a syntax error
Error? What error? I don't see any error.
Re: Calling Function [message #267179 is a reply to message #267164] Wed, 12 September 2007 11:44 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello,
Thanks for the reply ....

I have tried calling
select
SAP_STAGING.LOAD_DAILY_TABLES.FUNCTION_shipping_days_used
from dual;

The error is ORA-00904 : Invalid column

thanks
Re: Calling Function [message #267184 is a reply to message #267164] Wed, 12 September 2007 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It appears you could benefit from Reading The Fine PL/SQL Reference Manual.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1662
Re: Calling Function [message #267187 is a reply to message #267164] Wed, 12 September 2007 12:05 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Thanks for providing the link...because iam new to pl/sql is there any fault in my function calling from package ..can you suggest
Re: Calling Function [message #267189 is a reply to message #267164] Wed, 12 September 2007 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> iam new to pl/sql
I am shocked; just shocked by this revelation.
Have you EVER written any functioning PL/SQL code?
You might want to get & study "Teach Yourself PL/SQL in 21 Days"; ISBN 0-672-31123-2
Re: Calling Function [message #267196 is a reply to message #267164] Wed, 12 September 2007 13:02 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello Anacedent thats the reason i have put my problem in PL/SQL NEWBIES not in the EXPERT group...so that somebody could give me a suggestion for my problem

Thanks for the reply
Re: Calling Function [message #267199 is a reply to message #267196] Wed, 12 September 2007 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want us to help you, you have to copy and paste what you did and the description of your tables.
Copy and paste the package creation with "show errors", copy and paste the function call and so on.

Regards
Michel
Re: Calling Function [message #267212 is a reply to message #267164] Wed, 12 September 2007 13:41 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Thanks micheal.....

I have a table in which the data loads everday .......by name Table1 where in it shows all the errors messages ..in one error message there was a error ORA-01476: DIVISOR EQUAL TO ZERO
when checked with the error .This coming from a function which is in the Package Called LOAD_DAILY_TABLES ......
    FUNCTION shipping_days_used
        RETURN number
    IS
        v_shipping_days_used number;
    BEGIN
        SELECT COUNT (DISTINCT (billing_date)) 
          INTO v_shipping_days_used
          FROM dw_admin.sales_fact c
         WHERE billing_date IN (SELECT oracle_date
                                  FROM period_ref
                         WHERE shipping_day = 'Y' AND cy_cp = 'Y');
 Return v_shipping_days_used;


Now i have changed the code by giving
FUNCTION shipping_days_used
RETURN number
IS
v_shipping_days_used number;
BEGIN
SELECT COUNT (DISTINCT (billing_date))
INTO v_shipping_days_used
FROM dw_admin.sales_fact c
WHERE billing_date IN (SELECT oracle_date
FROM period_ref
WHERE shipping_day = 'Y' AND cy_cp = 'Y');
Return (decode_v_shipping_days_used,0,1);
end;
The problem i have to check the function whether it is working with new Decode statement or not....

Any help
Re: Calling Function [message #267215 is a reply to message #267212] Wed, 12 September 2007 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First you have to maje it compile.
I don't want you to post the code.
I want you to post EXECUTION that is waht you did.
That's all, copy and paste the execution.
That's all, nothing more, copy and paste your screen after an execution.
Don't you understand?

Regards
Michel
Re: Calling Function [message #267216 is a reply to message #267164] Wed, 12 September 2007 13:53 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello Micheal...
when i try to compile is says error at line : pls-00204: Function or pseudo-column 'DECODE' may be used in SQL statment only :PL/SQL:STATMENT IGNORED
Re: Calling Function [message #267218 is a reply to message #267216] Wed, 12 September 2007 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't want you to tell me what it says.
I want you to COPY AND PASTE YOUR SCREEN.
Is this so difficult? Take SQL*Plus.

Regards
Michel
Re: Calling Function [message #267219 is a reply to message #267164] Wed, 12 September 2007 13:57 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
WHen i tried to execute the FUNCTION IN sql:
select LOAD_DAILY_TABLES .shipping_days_used from dual

iam getting an error 0RA-00904 : LOAD_DAILY_TABLES.SHIPPING_DAYS_USED ARE INVALID IDENTIFIERS where
Load_daily_tables is the Package and Shipping_days_used is the function....
thanks
Re: Calling Function [message #267220 is a reply to message #267219] Wed, 12 September 2007 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same answer.

Regards
Michel
Re: Calling Function [message #267221 is a reply to message #267164] Wed, 12 September 2007 14:04 Go to previous messageGo to next message
live2learn
Messages: 34
Registered: February 2006
Member
Hi,

"Return (decode_v_shipping_days_used,0,1);"
What is this? Please let me know the usage of this?

Live2Learn,
Re: Calling Function [message #267222 is a reply to message #267164] Wed, 12 September 2007 14:06 Go to previous messageGo to next message
live2learn
Messages: 34
Registered: February 2006
Member
The DECODE statement is wrong.....Plz check
Re: Calling Function [message #267224 is a reply to message #267164] Wed, 12 September 2007 14:13 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
The decode statement:RETURN (DECODE(v_shipping_days_used,0,1)) is this wrong
Re: Calling Function [message #267225 is a reply to message #267224] Wed, 12 September 2007 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try it and post the result.
COPY AND PASTE the result, don't word it.

Regards
Michel
Re: Calling Function [message #267230 is a reply to message #267164] Wed, 12 September 2007 14:38 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Hello Friends...thanks for your patience but really im in a troubled situation wherin i cant explain the right process to you guys.........its my bad and i accept it ..thanks for all the suggestions you guys have given but can anybody suggest me how to check the function or call a function:
thanks
Re: Calling Function [message #267233 is a reply to message #267230] Wed, 12 September 2007 15:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's a major misunderstanding here; people would like to see how it looks like (a photography), not how you see it (a painting).

This is what you do: "my function won't compile properly and I don't know why."

This is what they want you to do: "here's how my SQL*Plus session looks like":
SQL> create or replace function my_function
  2  return number
  3  is
  4  begin
  5    return (decode_something_there, 0, 1);
  6  end;
  7  /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION MY_FUNCTION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: Statement ignored
5/11     PLS-00201: identifier 'DECODE_SOMETHING_THERE' must be declared
SQL>

See the difference? Noone here is a mind-reader, and noone but you sees what really happened on your screen.

So - could you, finally, copy and paste your SQL*Plus session into your next message in this topic?
Re: Calling Function [message #267234 is a reply to message #267164] Wed, 12 September 2007 15:16 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Iam using toad not SQL*PLUS
THANKS
Re: Calling Function [message #267235 is a reply to message #267164] Wed, 12 September 2007 15:18 Go to previous messageGo to next message
live2learn
Messages: 34
Registered: February 2006
Member
Now i have changed the code by giving
FUNCTION shipping_days_used
RETURN number
IS
v_shipping_days_used number;
BEGIN
SELECT COUNT (DISTINCT (billing_date))
INTO v_shipping_days_used
FROM dw_admin.sales_fact c
WHERE billing_date IN (SELECT oracle_date
FROM period_ref
WHERE shipping_day = 'Y' AND cy_cp = 'Y');
"Return (decode_v_shipping_days_used,0,1);"
end;

I said this...is it not wrong....
Re: Calling Function [message #267236 is a reply to message #267164] Wed, 12 September 2007 15:21 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
If you cannot figure out how to cut and paste a TOAD or SQL*Plus session, perhaps you should be doing something other than database development.

YOYO (Your on your own)

[Updated on: Wed, 12 September 2007 15:24]

Report message to a moderator

Re: Calling Function [message #267237 is a reply to message #267164] Wed, 12 September 2007 15:38 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Here is my TOAD SESSION when i try to compile
[CODE] BEGIN
2 SELECT COUNT (DISTINCT (billing_date))
3 INTO v_shipping_days_used
4 FROM dw_admin.sales_fact c
5 WHERE billing_date IN (SELECT oracle_date
6 FROM period_ref
7 WHERE shipping_day = 'Y' AND cy_cp = 'Y');
--20070904-CJM-Currently the trended net sales is
--averaged over the shipping days used. When the
--average shipping days is 0, the load aborts.
--Defaulting to 1 will return a trended net sales
--equal to the net sales.
8 RETURN (DECODE(v_shipping_days_used,0,1));
END;

Line 8 :PLS-00204: Function or pseudo-column 'DECODE'may be used inside a SQL statement only;
Line 8 :PL/SQL: statement ignored

Re: Calling Function [message #267239 is a reply to message #267237] Wed, 12 September 2007 15:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the message is clear:
at line 8 you use DECODE out of SQL but this function can only be used inside SQL.

Regards
Michel
Re: Calling Function [message #267243 is a reply to message #267164] Wed, 12 September 2007 16:27 Go to previous messageGo to next message
raksora
Messages: 18
Registered: August 2007
Junior Member
Is there any way i can use the statement in SQL other than in return condition:
Re: Calling Function [message #267280 is a reply to message #267164] Wed, 12 September 2007 23:30 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way i can use the statement in SQL
YES, it must be used only in SQL statements

>other than in return condition:
It appears you don't recognize the difference between SQL & PL/SQL

"RETURN" is a PL/SQL construct; which is why DECODE can not be used with it.

It is too bad for all concerned that you appear to be attempting to learn PL/SQL by trial & error; rather than actually trying to RTFM to learn what is valid syntax.

I ask again, "Have you EVER written any functioning PL/SQL code?"

EXACTLY what is the purpose of the function; which is nothing more than a simple SELECT?
SELECT COUNT (DISTINCT (billing_date)) 
FROM dw_admin.sales_fact c
WHERE billing_date IN (SELECT oracle_date
                       FROM period_ref
                       WHERE shipping_day = 'Y' 
                         AND cy_cp = 'Y');
Previous Topic: hoe to select the concatenated column
Next Topic: 2 face commit on two diffirent databases
Goto Forum:
  


Current Time: Sun Dec 11 00:26:36 CST 2016

Total time taken to generate the page: 0.07502 seconds