Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: using DBMS_METADATA.GET_DDL with table name containing a single quote

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

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 18 Apr 2007 14:59:09 -0700
Message-ID: <1176933549.847516.299970@o5g2000hsb.googlegroups.com>


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' Received on Wed Apr 18 2007 - 16:59:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US