Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Question Unanswered: Create new column with column name as varibale value.

    Hi,
    I want to add a new column everytime with local variable value in a stored procedure.
    How can I achieve it?
    My Code:
    Code:
    create or replace
    PROCEDURE SP_PARAM_LIST(
       curs OUT SYS_REFCURSOR)
    AS
      CURSOR param_curs
      IS
        SELECT * FROM PARAM_LIST;
      
      param_name  VARCHAR2(20);
      param_value VARCHAR2(20);
    BEGIN
     execute immediate 'CREATE GLOBAL TEMPORARY TABLE Temp(tempid NUMBER)';
      OPEN PARAM_CURS;
      
      LOOP
      FETCH param_curs into param_name, param_value;
      EXIT WHEN param_curs%NOTFOUND;
      execute immediate 'ALTER TABLE Temp ADD '||param_name||' varchar2(20)';
      execute immediate 'insert into Temp('||param_name||') values('||param_value||')';
      END LOOP;
      close param_curs;
      OPEN curs FOR 'SELECT * FROM Temp';
      execute immediate 'DROP TABLE Temp';
    END SP_PARAM_LIST;
    ERROR:
    ORA-01735: invalid ALTER TABLE option
    Last edited by poonam4589; 05-03-12 at 09:18.

  2. #2
    Join Date
    Feb 2005
    Posts
    57
    What values will param_name contain? Ensure they don't contain restricted words.

    Maybe assign Alter Table statement to a variable and output it via dbms_output. This will show you what the statement actually looks like. Then use the variable in the Execute Immediate statement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If you have a look at the manual where the ADD column is documented (http://docs.oracle.com/cd/E11882_01/...1.htm#i2183462), you'll notice that it requires brackets around the column definition. e.g.:
    Code:
    ALTER TABLE Temp ADD (foo  varchar2(20))
    So your could should be:
    Code:
    execute immediate 'ALTER TABLE Temp ADD ('||param_name||' varchar2(20))';

  4. #4
    Join Date
    Apr 2012
    Posts
    7
    @outrider: yea thanks i checked it earlier
    suppose param_name is abc query appears as
    alter table Temp add 'abc' varchar(20);
    but actual query should appear as
    alter table Temp add abc varchar(20);


    @shammat : thanks a lot for the solution. it is working. actually m new to oracle. thanks 1s again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •