just because SQL*Plus (or any tool you use) says, that your procedure did run successfully, its not sure that it really did. This is because you have
programmed a when others exception without an raise. You just close the cursor. That means you will never know if there was an error or not.
For more explanation look at http://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html.
So put a raise in your exception handler and see what error message you will get.
As a guess I would say it is the 's' in dbms_sql.bind_variables. The subprogram is named dbms_sql.bind_variable.
Hope that helps.
you should not comment out the close cursor statement, just add an raise ....
The must be something wrong with the bind, but I do not see what is wrong ..
This is a way the procedure will work - without bind :
Create or replace procedure test(p_table_name varchar2) AS
v_statement := 'create table ' || p_table_name || ' (id number,name varchar2(50),location varchar2(50))';
when others then
Can you post the output of below cmd
create or replace procedure test222(p_table_name varchar2) AS
execute immediate 'create table '||p_table_name||'(id number,name varchar2(50),location varchar2(50))';
select * from (
select OWNER,OBJECT_TYPE,OBJECT_NAME,CREATED from dba_objects order by CREATED desc)
where rownum <= 10;