Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

trying to add column with dynamic sql

631898May 25 2008 — edited May 25 2008
I don't understand why i'm getting an error.
Here's the code:
CREATE OR REPLACE PROCEDURE add_sp
 (p_table IN VARCHAR2,
  p_col   IN VARCHAR2,
  p_type  IN VARCHAR2)
 IS
  lv_cursor INTEGER;
  lv_add    VARCHAR2(100);
BEGIN
 lv_cursor:=DBMS_SQL.OPEN_CURSOR;
 lv_add:='ALTER TABLE '||p_table||' ADD ('||p_col||' '||p_type||')';
 DBMS_SQL.PARSE(lv_cursor, lv_add, DBMS_SQL.NATIVE);
 DBMS_SQL.CLOSE_CURSOR(lv_cursor);
END;
/

Procedure created.

SQL> execute add_sp('bb_shopper','standing','varchar2');
BEGIN add_sp('bb_shopper','standing','varchar2'); END;

*
ERROR at line 1:
ORA-00906: missing left parenthesis
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SYSTEM.ADD_SP", line 11
ORA-06512: at line 1
The syntax seems correct...why is this not working?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 22 2008
Added on May 25 2008
4 comments
2,118 views