Re: DBMS_LOCK package. How to call those functions?

A copy of this was sent to mcui_at_cc.usu.edu (if that email address didn't require changing) On 4 May 99 15:03:43 MDT, you wrote:

>I am trying to call a build-in function in DBMS_LOCK package but
>it always got an error as follows.
>SQLWKS> SELECT DBMS_LOCK.request(2,2,2,false) from dual
> 2>
>SELECT DBMS_LOCK.request(2,2,2,false) from dual
> *
>ORA-00904: invalid column name
> I login in as SYS or DBA.
> II login as either sys or DBA. How can I call those functions?
>
>muyi
>MUYI CUI
>Dentrix Inc
>mcui_at_dentrix.com

You cannot call dbms_lock from a SQL statement since

• it is not 'pure' enough. no pragmas have been asserted for this package. functions called from SQL like that must at least NOT write to the database state. DBMS_LOCK writes to the database state making it not callable from SQL
• its interface uses non-sql types (boolean, FALSE). You must use only SQL types (number, date, string, etc) in PL/SQL called from SQL.

Try this:

declare

   x number;
begin

   x := dbms_lock.request( 2, 2, 2, false ); end;
/

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed May 05 1999 - 09:09:51 CDT