problem in executing the procedure

[srinivasm]

I created a procedure to create a table using dynamic sql
(EXECUTE IMMEDIATE) ,procedure created successfully.

i am using oracle8i

i created the procedure as following

SQL> create procedure create_table1 (table_name in varchar2,column1 in varchar2,column2 in varchar2)

2 as
3 begin
4 execute immediate 'create table' || table_name || '('
5 || column1 || 'varchar2(10),'
6 || column2 || 'varchar2(10))';
7 end;
8 /


but while excuting the procedure its giving errors as follows

SQL> execute create_table1 ('sptable','name','city');
BEGIN create_table1 ('sptable','name','city'); END;

*
ERROR at line 1:
ORA-00901: invalid CREATE command
ORA-06512: at "SYSTEM.CREATE_TABLE1", line 4
ORA-06512: at line 1


and again i tried like this


SQL> begin
2 create_table('sptable','name','city');
3 end;
4 /
create_table('sptable','name','city');
*
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00905: object SYSTEM.CREATE_TABLE is invalid
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored



please help me. i am using oracle8i

[marist89]

There's something wrong with your statement. Check spacing. (Using _ as a blank)

execute immediate 'create table_' || table_name || '_('
|| column1 || 'varchar2(10),'
|| column2 || 'varchar2(10))';

[mber]

[QUOTE][i]Originally posted by marist89 [/i]
[B]There's something wrong with your statement. Check spacing. (Using _ as a blank)

execute immediate 'create table_' || table_name || '_('
|| column1 || 'varchar2(10),'
|| column2 || 'varchar2(10))';

[/B][/QUOTE]

Don't forget there should also have apaces between "column1" and "varchar2(10)", "column2" and varchar2(10)" as well.

[mary]

It is a little hard to identify errors when writing dynamic sql since it compiles and displays errors during the execution of the procedure. I usually create a dummy table having a colum varchar(2000). At the early stage of testing of my procedure I comment 'execute immidiate' line out and write a simple inset statement containing a syntax to insert my DML statement into my dummy table. Since I am pretty familiar with that type of statements I can identify all my errors when I retreive data from the dummy table. This method decreases my developement time.