dynamic sql error ORA-00911

Question

i have create the below simple code it will get table name dynamically and create a table as per the columns given in the input table

    CREATE OR REPLACE
PROCEDURE dyn_sql_sp(
    p_user        VARCHAR2,
    p_table_name  VARCHAR2,
    P_NW_tbl_name VARCHAR2)
AS
  d_cols VARCHAR2(2000);
  CURSOR col_c
  IS
    SELECT column_name
      ||' '
      ||data_type
      ||'('
      ||data_length
      ||')' colm
    FROM All_Tab_Columns
    WHERE owner   =UPPER(p_user)
    AND table_name=UPPER(p_table_name);
BEGIN
  FOR i IN col_c
  LOOP
    d_cols:=d_cols||i.colm||',';
  END LOOP;
  D_Cols:=RTRIM(REPLACE(D_Cols,'DATE(7)','DATE'),',');
  dbms_output.put_line( 'CREATE TABLE '||P_Nw_Tbl_Name||' ('||d_cols||');');
  EXECUTE Immediate '   CREATE TABLE   '||P_Nw_Tbl_Name||' ( '||d_cols||');  ';
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm||SQLCODE);
END;

but im getting error as

SQL> exec dyn_sql_sp('hr','departments','tamil');
CREATE TABLE tamil (DEPARTMENT_ID NUMBER(22),DEPARTMENT_NAME
VARCHAR2(30),MANAGER_ID NUMBER(22),LOCATION_ID NUMBER(22)
);
ORA-00911: invalid character

PL/SQL procedure successfully completed.

hope program is completely correct i can run the output separately and works fine but getting error while i dynamically create the same

SQL> CREATE TABLE tamil (DEPARTMENT_ID NUMBER(22),DEPARTMENT_NAME
  2  VARCHAR2(30),MANAGER_ID NUMBER(22),LOCATION_ID NUMBER(22));

Table created.

Aleksej · Accepted Answer · 2016-09-23 07:51:16Z

4

The problem may be in the final ';' in the dynamic statement:

SQL> begin
  2      execute immediate 'create table tab_one(a number);';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at line 2


SQL> begin
  2      execute immediate 'create table tab_one(a number)';
  3  end;
  4  /

PL/SQL procedure successfully completed.

answered Sep 23, 2016 at 7:51

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

William Robertson Over a year ago

The SQL language has no semicolon. It just looks as though it does because client-side tools generally use them for a command terminator.

Collectives™ on Stack Overflow

dynamic sql error ORA-00911

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related