CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
user1990383
user1990383

Reputation: 125

Create statement in Dynamic Sql with xml content

How can i write the query in Dynamic SQL, I am getting error given below.

 EXECUTE IMMEDIATE 
 'CREATE TABLE SAMPLE
  SELECT EXTRACT (VALUE (d), '''//row/text()''').getstringval () 
  FROM (SELECT XMLTYPE (   '''<rows><row>'''
  || REPLACE ('''venkat,vijay,bylla,12''', ''',''', '''</row><row>''')
   || '''</row></rows>'''
    ) AS xmlval
     FROM DUAL) x,
   TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '''/rows/row'''))) d';

The error is :

Error(118,34): PLS-00103: Encountered the symbol "/" when expecting
one of the following:     ( - + case mod new null <an identifier>    
<a double-quoted delimited-identifier> <a bind variable>

Upvotes: 0

Views: 1086

Answers (1)

DazzaL
DazzaL

Reputation: 21973

your quotes are wrong and you need to alias the column..eg:

SQL> begin
  2  EXECUTE IMMEDIATE
  3   'CREATE TABLE SAMPLE AS
  4    SELECT EXTRACT (VALUE (d), ''//row/text()'').getstringval () a
  5    FROM (SELECT XMLTYPE (   ''<rows><row>''
  6    || REPLACE (''venkat,vijay,bylla,12'', '','', ''</row><row>'')
  7     || ''</row></rows>''
  8      ) AS xmlval
  9       FROM DUAL) x,
 10     TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, ''/rows/row''))) d';
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from sample;

A
--------------------------------------------------------------------------------
venkat
vijay
bylla
12

but why are you trying to create tables dynamically in oracle? If you're considering creating tables on-the-fly in your code, it is bad practice and is to be avoided in oracle (use global temporary tables instead if required).

Upvotes: 1

Related Questions