Re: using DBMS_METADATA.GET_DDL with table name containing a single quote

On Apr 18, 5:59 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Apr 18, 4:57 pm, yaru22 <yar..._at_gmail.com> wrote:
>
>
>
>
>
> > Hi,
>
> > I have a table named "Hi$Schema's" in my schema 'myschema'.
>
> > However, if I try the following sql statement, I get a failure
> > message.
>
> > SELECT DBMS_METADATA.GET_DDL('TABLE','Hi$Schema''s','myschema') FROM
> > dual
>
> > The error mesesage is:
>
> > ORA-19206: Invalid value for query or REF CURSOR parameter
> > ORA-06512: at "SYS.DBMS_XMLGEN", line 83
> > ORA-06512: at "SYS.DBMS_METADATA", line 345
> > ORA-06512: at "SYS.DBMS_METADATA", line 410
> > ORA-06512: at "SYS.DBMS_METADATA", line 449
> > ORA-06512: at "SYS.DBMS_METADATA", line 615
> > ORA-06512: at "SYS.DBMS_METADATA", line 1221
> > ORA-06512: at line 1
>
> > I don't know how to deal with this single quote.
>
> > Does anyone know a solution or a workaround?
>
> You really have a table name with a single quote in it? ( That's ugly
> and I did not really think it would be valid ... )
>
> You may be able to use the vertical pipe ( concatenation operator )
>
> 'beginning of string||'''||end of string' or some similar coding plus
> you can use the chr operator in the middle to generate the correct
> character
>
> 'beginning'||chr(somenumber)||'end of string'- Hide quoted text -
>
> - Show quoted text -

There is no excuse for having a quote in the table_name nor is it wise for the table_name to be anything but uppercase.

IMHO -- Mark D Powell -- Received on Wed Apr 18 2007 - 18:02:01 CDT