DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Urgent!

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Exclamation

    hi
    how to create variables dynamically?
    is this possible?
    write back
    jegannathan@sakinfotech.co.in
    Cheers!
    OraKid.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    In what kind of environment? Need more information on what you are looking for to assist you.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi
    i will be fetching the total no of column count from all_tab_columns for a given table
    if the count is 10 then i should create 10 variables.
    if the count is 150 then i should create 150 variables...so on...

    i want to create a single procedure to handel all the tables
    the max column what i hv is 957 columns and the min is 4 columns

    write back
    Cheers!
    OraKid.

  4. #4
    Join Date
    Sep 2001
    Posts
    15
    Hi!
    May be such code will help:

    declare
    stmt varchar(32767) := 'declare ';
    begin

    for r in (select * from all_tab_columns where ...)
    loop
    stmt := stmt || 'p_' || r.column_name ||' varchar2(4000); ';
    end loop;

    stmt := stmt ||'
    begin
    -- do some work here
    end;';

    execute immediate stmt;

    end;
    Victor
    www.dynamicpsp.com

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    declare
    stmt varchar(32767) := 'declare '||CHR(10);
    table_name varchar(32) := 'TEST' ;
    col_name varchar(32) ;
    datatype_name varchar(32) ;
    begin
    for r in (select column_name,data_type,DATA_LENGTH from all_tab_columns
    where TABLE_NAME ='EMP')
    loop
    stmt := stmt || 'p_' || r.column_name ||' EMP.'||r.column_name||'%TYPE;'|| CHR(10);
    end loop;
    stmt := stmt ||'
    begin
    select ename,empno from dual;
    end;';
    execute immediate stmt INTO ?????;
    exception
    when others then
    dbms_output.put_line('ERROR'||SQLERRM);
    NULL;
    end;

    IN the above example i hv selected ename & empno
    how to assign the values? in the execute immediate ONTO statement???
    thanx
    Cheers!
    OraKid.

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    thanx visctor
    i hv solved the problem
    Cheers!
    OraKid.

  7. #7
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Thumbs up

    hi guys
    i have written a PL?SQL block
    can we write more simpler than this?

    DECLARE
    lv_table_name VARCHAR(32) := 'SEC_D_ACCOUNT' ;
    lv_dest_table_name VARCHAR(32) := 'SEC_D_ACCOUNT_SEC' ;
    col_name VARCHAR(32) ;
    datatype_name VARCHAR(32) ;
    stmt_col VARCHAR(4000) ;
    stmt_column VARCHAR(4000) ;
    sele_stmt VARCHAR(4000) ;
    stmt VARCHAR(32767) ;
    stmt_1 VARCHAR(10) := 'DECLARE '||CHR(10);
    stmt_2 VARCHAR(4000) := '

    TYPE c1 IS REF CURSOR;
    y1 c1;

    PROCEDURE oc (
    ct1 IN VARCHAR2,
    co1 IN OUT c1
    )
    IS
    BEGIN
    OPEN co1 FOR ct1;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    WHEN OTHERS THEN
    NULL;
    END;';
    BEGIN
    FOR r IN (SELECT column_name,data_type,data_length FROM all_tab_columns
    WHERE table_name = lv_table_name)
    LOOP
    stmt_column := stmt_column || ' , ' || r.column_name ;
    sele_stmt := SUBSTR(stmt_column,3);

    stmt := stmt || 'p_' || r.column_name ||' ' ||lv_table_name||'.'||r.column_name||'%TYPE;'|| CHR(10);

    stmt_col := stmt_col || 'p_' || r.column_name || ',';

    END LOOP;

    stmt := stmt_1 || stmt || stmt_2 ||'

    BEGIN
    oc( ''SELECT' || sele_stmt || ' FROM ' || lv_table_name || ' '',y1);
    LOOP
    FETCH y1 INTO ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ';
    EXIT WHEN y1%notfound;
    INSERT INTO '|| lv_dest_table_name || ' VALUES( ' || SUBSTR(stmt_col,1,LENGTH(stmt_col)-1) || ' );
    COMMIT;
    END LOOP;

    END;';

    EXECUTE IMMEDIATE stmt ;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR'||SQLCODE||SQLERRM);
    NULL;

    END;

    jegannathan@sakinfotech.co.in
    Cheers!
    OraKid.

  8. #8
    Join Date
    Sep 2001
    Posts
    15
    Hi balajiyes!
    I think your code must work too, it can possibly be reduced, but if i understand your final aim right, this task has a much easier solution:

    procedure copy_table(src_name in varchar2, dest_name in varchar2)
    is
    stmt varchar2(10000);
    begin
    stmt := ' insert into '||dest_name||' (select * from '||src_name||')';
    execute immediate stmt;
    commit;
    exception when others then
    dbms_output.put_line(sqlerrm);
    end;
    Victor
    www.dynamicpsp.com

  9. #9
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi victor
    thanx

    ya! i just gave a example as INSERT actually there will be lot of business login within that dynamic generated query...
    some calculations....
    good job. help me in giving fine tuning stuff
    regards
    balajiyes@yahoo.com

    Cheers!
    OraKid.

  10. #10
    Join Date
    Sep 2001
    Posts
    15
    Hi!
    Just a few small changes.

    DECLARE
    lv_table_name VARCHAR(32) := 'SEC_D_ACCOUNT' ;
    lv_dest_table_name VARCHAR(32) := 'SEC_D_ACCOUNT_SEC' ;
    stmt_col VARCHAR(4000) ;
    stmt_var VARCHAR(4000) ;
    stmt_column VARCHAR(4000) ;
    stmt VARCHAR(32767) ;
    stmt_1 VARCHAR(10) := 'DECLARE
    TYPE c1 IS REF CURSOR;
    y1 c1; '||CHR(10);
    BEGIN
    FOR r IN (SELECT column_name,data_type,data_length FROM all_tab_columns
    WHERE table_name = lv_table_name)
    LOOP
    stmt_column := stmt_column || r.column_name ||',';
    stmt_var := stmt_var || 'p_' || r.column_name ||' ' ||lv_table_name||'.'||r.column_name||'%TYPE;'|| CHR(10);
    stmt_col := stmt_col || 'p_' || r.column_name || ',';
    END LOOP;

    stmt_column := rtrim(stmt_column, ',');
    stmt_col := rtrim(stmt_col, ',');
    stmt := stmt_1 || stmt_var ||'
    BEGIN
    open y1 for ''SELECT ' || stmt_column || ' FROM ' || lv_table_name ||' '';
    LOOP
    FETCH y1 INTO ' || stmt_col || ';
    EXIT WHEN y1%notfound;
    INSERT INTO '|| lv_dest_table_name || ' VALUES( ' || stmt_col || ' );
    COMMIT;
    END LOOP;
    close y1;
    END;';

    EXECUTE IMMEDIATE stmt ;

    EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR'||SQLCODE||SQLERRM);
    END;
    Victor
    www.dynamicpsp.com

Posting Permissions

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


Click Here to Expand Forum to Full Width