Forum Stats

  • 3,759,967 Users
  • 2,251,622 Discussions
  • 7,870,893 Comments

Discussions

problem to export a type with ddl command on Oracle Database 11g Enterprise Edition Release 11.2.0.3

d.fontana
d.fontana Member Posts: 2
edited Feb 4, 2019 4:15AM in SQLcl

Good morning.

I can't export with command ddl a type with body.

I give a concrete example of the problem.

I have a type rs_manager with a body (that's an object)

if i type

ddl rs_manager save test.sql 

i have this message

Multiple objects exist with the name rs_manager. Please specify one of the following types: TYPE,TYPE BODY

But if i type

ddl rs_manager type save test.sql 

i have this message

Object TYPE SAVE TEST.SQL rs_manager not found

Finally i type

ddl rs_manager type

and magically

  CREATE OR REPLACE TYPE "MCSGMEN"."RS_MANAGER" AS OBJECT(    riga_azione rs_args,    constructor function rs_manager(riga_azione in rs_args) return self as result,    member function cerca_aspettativa (        funzione   IN  varchar,        stato      IN  CHAR    ) RETURN rs_testata_aspettativa);/CREATE OR REPLACE TYPE BODY "MCSGMEN"."RS_MANAGER" AS  constructor function rs_manager(riga_azione in rs_args) return self as result is  BEGIN    self.riga_azione := riga_azione;    return;  END;    member FUNCTION cerca_aspettativa (        funzione   IN  varchar,        stato      IN  CHAR    ) RETURN rs_testata_aspettativa is        testata  rs_testata_aspettativa;    BEGIN    select rs_testata_aspettativa(            testate.mcccdsoc,            testate.mccidbol,            testate.mcctpver,            testate.mccidmov,            testate.mccnmope,            testate.mccidast,            testate.mcccdazi) into testata    from (        SELECT DISTINCT            t.mcccdsoc,            t.mccidbol,            t.mcctpver,            t.mccidmov,            t.mccnmope,            t.mccidast,            t.mcccdazi        FROM            mcdgmen.mctrslfa lfa,            mcdgmen.mctrslfc lfc,            mcdgmen.mctrsast t        WHERE            lfa.mcccdsoc = lfc.mcccdsoc            AND lfa.mccidfun = lfc.mccidfun            AND lfa.mcccdstf = lfc.mcccdstf            AND lfc.mcctpcol = 'T'            AND lfc.mcccdfun = lfc.mcccdfun            AND lfa.mccnmide = lfc.mccidbol            AND lfa.mcctpver = lfc.mcctpver            AND lfa.mcccdsoc = t.mcccdsoc            AND lfa.mccnmide = t.mccidbol            AND lfa.mcctpver = t.mcctpver            AND lfc.mccidmov = t.mccidmov            AND lfa.mccnmide = self.riga_azione.gl_mccnmide            AND lfa.mcctprec IN (                pkg_rs_util.tipo_corrente,                pkg_rs_util.tipo_temporaneo            )            AND lfa.mcccdfun = funzione            AND lfa.mcccdstf = stato        ) testate;        RETURN testata;    end cerca_aspettativa;END;/

Anyone have the same problem ?

There is a way to fix this ?

Thanks in advance.

Davide.

Tagged:

Best Answer

  • srg
    srg Member Posts: 21 Blue Ribbon
    edited Jan 30, 2019 10:32AM Accepted Answer

    I was able to reproduce the problem you are experiencing. It appears to me that the parser is greedy and thinks that anything beyond TYPE is part of the object type parameter. But, I was able to make the command work by splitting the command into 2 lines!

    Initially I put in:

    SQL> DDL "eMail" TYPE SAVE test.txtObject TYPE SAVE TEST.TXT "eMail" not found

    Which gives the same error you experienced - but if I up arrow to retrieve content of input buffer and position cursor after TYPE and hit the return,  I get this:

    SQL> DDL "eMail" TYPE  2* SAVE test.txt

    And this was the result:

      CREATE OR REPLACE EDITIONABLE TYPE "xxxxx"."eMail"as object (...        end SendMsg;end;/Wrote file test.txtSQL>

    As you can see, the parser needs a little help to understand how to interpret the command line.

    Hope this helps!

Answers

  • srg
    srg Member Posts: 21 Blue Ribbon
    edited Jan 30, 2019 10:32AM Accepted Answer

    I was able to reproduce the problem you are experiencing. It appears to me that the parser is greedy and thinks that anything beyond TYPE is part of the object type parameter. But, I was able to make the command work by splitting the command into 2 lines!

    Initially I put in:

    SQL> DDL "eMail" TYPE SAVE test.txtObject TYPE SAVE TEST.TXT "eMail" not found

    Which gives the same error you experienced - but if I up arrow to retrieve content of input buffer and position cursor after TYPE and hit the return,  I get this:

    SQL> DDL "eMail" TYPE  2* SAVE test.txt

    And this was the result:

      CREATE OR REPLACE EDITIONABLE TYPE "xxxxx"."eMail"as object (...        end SendMsg;end;/Wrote file test.txtSQL>

    As you can see, the parser needs a little help to understand how to interpret the command line.

    Hope this helps!

  • d.fontana
    d.fontana Member Posts: 2
    edited Feb 4, 2019 4:15AM

    It works.

    Thank you!!!