Function Access Problem [message #300804] |
Mon, 18 February 2008 02:52 |
wincuk
Messages: 2 Registered: February 2008
|
Junior Member |
|
|
There is a function as following:
CREATE OR REPLACE FUNCTION DB01.SECONDS_TO_DATE (i_seconds IN INTEGER)
RETURN DATE IS
i_tz_offset INTEGER := 8;
i_converted_date DATE;
BEGIN
i_converted_date := TO_DATE('01011970','MMDDRRRR') + i_seconds / (60*60*24);
i_converted_date := i_converted_date + i_tz_offset/24;
RETURN i_converted_date;
END;
/
I have to create the stored proc as following
CREATE OR REPLACE PROCEDURE DB02.GET_SQU(
PROCESS_SIZE IN NUMBER
)
IS
t_RE NUMBER;
BEGIN
select acctID into t_re from acct
where SECONDS_TO_DATE(create_date) <= sysdate;
dbms_output.put_line('acctID ->:'||t_Re);
END MRB_ASSIGN_PROCESS_SEQUENCE;
/
I can run the sql 'select acctID from acct
where SECONDS_TO_DATE(create_date) <= sysdate;'
in db02 successfully. However with the stored-proc creation in db02, error keep appear regarding the function SECONDS_TO_DATE, regarding invalid identifier.
Please anyone of you have any clue on this case.
Thanks.
|
|
|
|
|
Re: Function Access Problem [message #300973 is a reply to message #300804] |
Mon, 18 February 2008 20:53 |
wincuk
Messages: 2 Registered: February 2008
|
Junior Member |
|
|
Please find the following as the formatted code:
CREATE OR REPLACE FUNCTION db01.seconds_to_date (i_seconds IN INTEGER)
RETURN DATE
IS
i_tz_offset INTEGER := 8;
i_converted_date DATE;
BEGIN
i_converted_date :=
TO_DATE ('01011970', 'MMDDRRRR')
+ i_seconds / (60 * 60 * 24);
i_converted_date := i_converted_date + i_tz_offset / 24;
RETURN i_converted_date;
END;
/
CREATE OR REPLACE PROCEDURE db02.get_squ (process_size IN NUMBER)
IS
t_re NUMBER;
BEGIN
SELECT acctid
INTO t_re
FROM acct
WHERE seconds_to_date (create_date) <= SYSDATE;
DBMS_OUTPUT.put_line ('acctID ->:' || t_re);
END mrb_assign_process_sequence;
--- Error case is for the highlighted one. While I can run the respected sql statment (
SELECT acctid
INTO t_re
FROM acct
WHERE seconds_to_date (create_date) <= SYSDATE;)
without error in normal prompt. Just cannot run in the creation of stored-proc.
the Synonym of the function is PUBLIC
and the GRANTS is to a group named 'INFRANET_ROLE' with privilege in 'EXECURE' and 'No' in Grant Option. And I have no rights to change any of the items in function.
Please suggest if anything can be done in solving the case?
|
|
|
|