Can procedure support CREATE TABLE and CREATE INDEX? 1

[mlu]

I have ORACLE 8i running on Linux and NT.&nbsp;&nbsp;<br><br>I try to create a procedure including CREATE TABLE or CREATE INDEX statemants,&nbsp;&nbsp;they always fail, but the same CREATE TABLE or CREATE INDEX statements are running fine outside of the procedure or BEGIN...END.<br><br>Following is my sample code. It doesn't work.<br>CREATE PROCEDURE CreateTiesTable<br>IS<br>begin<br>CREATE TABLE TiesTable_New (<br> ID NUMBER(10),<br>&nbsp; TieStateID NUMBER(4) <br>);<br>END CreateTiesTable ;<br><br>So how can I create a table or index in a procedure?&nbsp;&nbsp;Does ORACLE support this?<br><br>Thanks very much your help in advance!<br><br>Mary<br>

 

[redrockgrp]

Mary,<br><br>You need to use dynamic sql in order to execute DDL within<br>a procedure.&nbsp;&nbsp;If you review the DBMS_SQL package it should give you some examples.<br><br>Hope this helps,<br>--tl

 

[mlu]

tl,<br><br>Thanks very much!&nbsp;&nbsp;Your information is very helpful and it works.<br><br>Mary<br>

 

[hpux]

Look for &quot;Execute Immediate&quot; command in Oracle8i.<br>This is better than dbms_sql package.<br>U just have to prepare the command string.<br><br>For example <br>sql_stmt varchar2(256);<br>sql_stmt = 'Create Table .....'<br>Execute immediate sql_stmt;<br><br>This will do the job<br>This is a new addition in Oracle8i<br><br>