Allow access to the created function in PL/SQL

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Wed, 14 Dec 2005 11:02 am

Friends, I created a function to perform the conversion from Julian date for normal date.
The function fully meets my goals, however, only I can use it-there are other analysts who wish to have access to the same, but when do references in an SQL statement, Oracle returns the message Invalid identifier.
What must be done so that other users have access to the same?
lrmaross
Location: PIRACICABA - SP

Poston Wed, 14 Dec 2005 11:53 am

You have to give permission to other users to access your function.

Enter the user you created your function and run the following command:
Code: Select all
grant execute on fnc_sua_funcao to public;
If you want to give the privilege to only one person, do it like this:
Code: Select all
grant execute on fnc_sua_funcao to joao;
would that be?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 14 Dec 2005 1:11 pm

Dr_gori, good afternoon.

Really what I need is to give a GRANT in my function.
Had already performed this test and the response I get when I go into another session (another user) is: ORA-00904: \"TO_JULIANA\": invalid identifier so that you can see better what is happening, follow the function that I'm using:
Code: Select all
CREATE OR REPLACE Function to_juliana (dt_a_converter DATE )
RETURN NUMBER IS
dt_juliana NUMBER;

BEGIN
/* Inicio do Calculo de conversão para Data Juliana */

dt_juliana := 0;
dt_juliana := To_Char( TO_DATE( dt_a_converter )  , 'YYYYDDD') - 1900000 ;

/* atribuímos o retorno da função */
RETURN( dt_juliana );
END To_Juliana;
/

GRANT EXECUTE ON to_juliana TO PUBLIC;
I'm using SQL interpreter the SQLTools and am using two Schemas (users) with the same permissions in the database-I realize the creation and the function works correctly and the other date problems for use the function.

What might be occurring?
lrmaross
Location: PIRACICABA - SP

Poston Thu, 15 Dec 2005 11:05 am

Ola ...

Probably you must add your user name before the name of your function when in another user is calling this function.

E.g.
Code: Select all
SQL> Con Vinicius
Connected as Vinicius

SQL> Select Irmaross.To_Juliana(Sysdate) From Dual;
I hope work:P
lodvini
Location: Sapiranga - RS

Poston Fri, 16 Dec 2005 6:43 pm

lrmaross, sorry to join the discussion, but you want to convert normal date to julian? Why you wrote the function to_char already do that? With the advantage of no context switch between sql and plsql that could generate numerous recursive sql depending on the number of records that the function would read.

Another thing, their function does not seem to return properly the value of a date Julian, see:
Code: Select all
ops$marcio@LNX10GR2> CREATE OR REPLACE Function to_juliana (dt_a_converter DATE )
  2  RETURN NUMBER IS
  3  dt_juliana NUMBER;
  4
  5  BEGIN
  6  /* Inicio do Calculo de conversão para Data Juliana */
  7
  8  dt_juliana := 0;
  9  dt_juliana := To_Char( TO_DATE( dt_a_converter )  , 'YYYYDDD') - 1900000 ;
10
11  /* atribuímos o retorno da função */
12  RETURN( dt_juliana );
13  END To_Juliana;
14  /

Function created.

ops$marcio@LNX10GR2> select to_juliana(trunc(sysdate)) from dual;

TO_JULIANA(TRUNC(SYSDATE))
--------------------------
                    105350

1 row selected.

ops$marcio@LNX10GR2> select to_date( 105350, 'j') from dual;

TO_DATE(105350,'J')
-------------------
07/06/4424 00:00:00

1 row selected.

Agora com o to_char

ops$marcio@LNX10GR2> select to_char(trunc(sysdate), 'j') from dual;

TO_CHAR
-------
2453721

1 row selected.

ops$marcio@LNX10GR2> select to_date( 2453721, 'j' ) from dual;

TO_DATE(2453721,'J'
-------------------
16/12/2005 00:00:00

1 row selected.

and here part of the manual that explains:
Using Julian Dates the Julian date is the number of days since January 1, 4712 bc. Julian dates allow continuous dating from a common reference. You can use the date format model \"J\" with date functions TO_DATE and TO_CHAR Oracle DATE values to convert between Julian and their equivalents.
Example This statement returns the Julian equivalent of January 1, 1997: SELECT TO_CHAR (TO_DATE (' 1/1/1997 ', ' MM-DD-YYYY '), ' J ') FROM DUAL;

TO_CHAR--------2450450
http://download-west.oracle.com/docs/cd ... .htm#47829
mportes
Location: São Paulo


Poston Sat, 17 Dec 2005 12:16 am

Probably you must add your user name before the name of your function when in another user is calling this function.


Just to complement: If you do not want to put the OWNER before calling the function, you can make it public by creating a public synonym!!!
Code: Select all
CREATE PUBLIC SYNONYM sua_function FOR sua_function;
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered


  • See also
    Replies
    Views
    Last Post
cron