Home » SQL & PL/SQL » SQL & PL/SQL » Function Access Problem
Function Access Problem [message #300804] Mon, 18 February 2008 02:52 Go to next message
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 #300807 is a reply to message #300804] Mon, 18 February 2008 03:00 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Besides creating the FUNCTION and PROCEDURE I'm sure you did GRANT EXECUTION privilege and/or CREATE SYNONYM.

Coiuld you show what GRANTs and/OR SYNONYMS you created?
Re: Function Access Problem [message #300811 is a reply to message #300804] Mon, 18 February 2008 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and align the columns in result.
Use the "Preview Message" button to verify.

Copy and paste the execution do not describe it.
You say there is an error, how could we know which one it is if you tell us?

Regards
Michel
Re: Function Access Problem [message #300973 is a reply to message #300804] Mon, 18 February 2008 20:53 Go to previous messageGo to next message
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?
Re: Function Access Problem [message #300988 is a reply to message #300973] Mon, 18 February 2008 22:41 Go to previous message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html
Previous Topic: IOT tables.
Next Topic: selecting the column in subquery based on date
Goto Forum:
  


Current Time: Sat Nov 09 12:38:02 CST 2024