dbms_metadata.get_ddl

I am logged in as scott/tiger and performing

SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-00942: table or view does not exist


when I do a select * from emp and can see the table.

Any ideas?

In you schema does SCOTT own the EMP table? Also what roles does SCOTT have granted

Regards

Hi,
in SQL*PLUS it would not work as i tried,
but it worked with my SQLWindow (Pl/sql-Developer).
The problem could be the CLOB-Return-Type.

Try do implement a PL/SQL-Block and fetch the result into a PL/SQL-String.
and display the result with dbms_output()
porbably this could work.

Orca
:cool:

It does work from Sql*Plus:

Code:

---

$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Feb 26 11:38:42 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning option
JServer Release 9.2.0.1.0 - Production

SQL> set pages 0 long 5000
SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;

  CREATE TABLE "SCOTT"."EMP"
  (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

---