Re: [cx-oracle-users] Reading CLOB output

[Anthony T. <ant...@gm...>]

Just execute an anonymous PL/SQL block as in the following:

cursor.execute("""
    begin
        dbms_metadata.set_transform_param( dbms_metadata.session_transform,
            'SQLTERMINATOR', TRUE);
        dbms_metadata.set_transform_param( dbms_metadata.session_transform,
            'PRETTY', TRUE);
        dbms_metadata.set_transform_param( dbms_metadata.session_transform,
            'SEGMENT_ATTRIBUTES', FALSE);
    end;""")

On 1/24/07, John Elliott <joh...@in...> wrote:
>
> Anthony Tuininga <anthony@...> writes:
>
> >
> > Sure, why not.
> >
> > import cx_Oracle
> >
> > connection = cx_Oracle.connect("user/pw@...")
> > cursor = connection.cursor()
> > clob = cursor.callfunc("dbms_metadata.get_ddl", cx_Oracle.CLOB,
> >         ("TABLE", "NODELINK"))
> > print "Value is:", clob.read()
> >
> > Is that simple enough?
> >
> > On Fri, 2004-03-19 at 13:03, Orr, Steve wrote:
> > > I'm using the cool 9i dbms_metadata package from SQLPlus but can't
> > > figure out how use it with cx_Oracle 4. Here's a clipping from a SQLPlus
> > > session:
> > > ---------------------------------------------------------------------
> > > SQL> SELECT dbms_metadata.get_ddl('TABLE','NODELINK') from dual;
> > >
> > >   CREATE TABLE "HYDRA"."NODELINK"
> > >    (    "ID" NUMBER,
> > >         "PARENTID" NUMBER,
> > >         "NODETEXT" VARCHAR2(252)
> > >    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
> > >   STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
> > >   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> > >   TABLESPACE "USERS"
> > > ---------------------------------------------------------------------
> > >
> > > This returns the DDL for a database object as a CLOB. I've tried to use
> > > the cx_Oracle lob.read() function in Python but can't seem to figure it
> > > out. Can you provide some sample code on this?
> > >
> > >
> > > TIA!!
> > > Steve Orr
>
> How about the procedure calls to make the output pretty and introduce the sql
> terminator at the end.  How would I make the following calls using cx_oracle?
>
> -- set sql terminator so that semicolon and slash are inserted in
> -- the appropriate place.
> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,
> 'SQLTERMINATOR', TRUE);
>
> -- set Pretty paramter to format the output in an asthetically pleasing
> -- way
> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,
> 'PRETTY', TRUE);
>
> -- set Segment_attributes parameter to exclude the storage
> -- and other unwanted info.
> exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,
> 'SEGMENT_ATTRIBUTES', FALSE);
>
> Thanks!  You have no idea how much time this is saving me!
>
> Thanks,
> John
>
>
>
>
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>