This discussion is archived
6 Replies Latest reply: Nov 20, 2012 1:11 PM by mattknowles RSS

DBMS_METADATA.GET_DDL doesn't include function parameters

mattknowles Newbie
Currently Being Moderated
Sometimes, when I call DBMS_METADATA.GET_DDL to get the DDL statement for a function (which we always do to ensure we're starting from the source that's actually in production), the returned text doesn't include the parameters. Other times, it does, and I'm not able to see any pattern.

I was going to just make a function and show the output, but that did show the parameters. Then I tried calling GET_DDL while logged in as SYS as SYSDBA on my development database, and I still did not get the parameters, so I don't think it's a permissions issue.

Does anyone have any idea what's going on here?
  • 1. Re: DBMS_METADATA.GET_DDL doesn't include function parameters
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ


    It is difficult to say what you are doing wrong when you don't show us exactly what you do.
  • 2. Re: DBMS_METADATA.GET_DDL doesn't include function parameters
    mattknowles Newbie
    Currently Being Moderated
    It's really difficult to show you exactly what I'm doing when I can't consistently reproduce the problem, as I described.

    But here, I'll try.
    declare
         the_ddl clob;
    begin
         the_ddl := dbms_metadata.get_ddl('FUNCTION', '*****', '*****');
         dbms_output.enable(null);
         dbms_output.put_line(the_ddl);
    end;
    /
    Sometimes, it produces output like this:
    CREATE OR REPLACE FUNCTION "*****"."*****" (
         ***** in varchar2,
         ***** in number,
         ....
    ) return *****
    ...
    And other times, it produces output like this:
    CREATE OR REPLACE FUNCTION "*****"."*****" return *****
    ...
    I am not able to determine any consistent reason for this. In our production database, I can log in as myself and get the parameters for a specific function, but another user (that our automated process uses) doesn't see the parameters. In my development database, I log in as myself and I do not see the parameters, but I still don't see them when I log in as SYS AS SYSDBA.

    For what it's worth, if I recreate the function on my development database, then subsequent calls do show the parameters.
  • 3. Re: DBMS_METADATA.GET_DDL doesn't include function parameters
    sb92075 Guru
    Currently Being Moderated
    SQL> desc dba_source
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     OWNER                                              VARCHAR2(30)
     NAME                                               VARCHAR2(30)
     TYPE                                               VARCHAR2(12)
     LINE                                               NUMBER
     TEXT                                               VARCHAR2(4000)
  • 4. Re: DBMS_METADATA.GET_DDL doesn't include function parameters
    mattknowles Newbie
    Currently Being Moderated
    My goal here is to understand why DBMS_METADATA.GET_DDL sometimes doesn't return the parameters. I already have a workaround.
  • 5. Re: DBMS_METADATA.GET_DDL doesn't include function parameters
    BobLilly Expert
    Currently Being Moderated
    Try selecting from ALL_ARGUMENTS and ALL_PROCEDURES for the functions in question and see if you can detect any differences there when it works vs. when it doesn't work.

    Regards,
    Bob
  • 6. Re: DBMS_METADATA.GET_DDL doesn't include function parameters
    mattknowles Newbie
    Currently Being Moderated
    I am no longer able to find any more instances where the GET_DDL fails to return the parameters. I will try this again the next time I do have this problem. Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points