DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: dbms_metadata.get_ddl

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    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?

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    In you schema does SCOTT own the EMP table? Also what roles does SCOTT have granted

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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"
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

Posting Permissions

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


Click Here to Expand Forum to Full Width