Results 1 to 2 of 2

Thread: Db_name()

  1. #1
    Join Date
    Sep 2003
    Posts
    20

    Unanswered: Db_name()

    while converting a sproc from MSSQL to oracle using workbench i got an error message that

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    66/4 PL/SQL: SQL Statement ignored
    68/67 PL/SQL: ORA-00904: "DB_NAME": invalid identifier

    and the code in oracle is


    SELECT 1 INTO StoO_selcnt FROM DUAL WHERE EXISTS
    (
    SELECT * FROM ( SELECT 1 FROM sa.tbltlDatabases WHERE tName = Db_Name() and nDbId = 3 )WHERE ROWNUM <= 1
    );

    can somebody point out what is wrong with the code and what can be the solution??

    thanks
    alok

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Db_name()

    Select 1
    INTO StoO_selcnt
    From SA.tbltlDatabases
    WHERE nDbId = 3
    and tName = (select SYS_CONTEXT('USERENV', 'DB_NAME') DB_NAME from dual);

    For this to work, you must have a schema named SA with a Table named tbltlDatabases and a local variable declared in your procedure or type number called StoO_selcnt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •