Results 1 to 4 of 4

Thread: Dynamic SQL

  1. #1
    Join Date
    Feb 2003
    Posts
    7

    Unanswered: Dynamic SQL

    I have a dynamic sql like ' Select || fields|| from || tab|| where || where_clause||
    here fields,tab & where_clause is populated dynamically.
    I have a requirmeent, wherein,I have to execute this query ,which will result in multiple rows and result of each rows to be concatenated (1,'abc','23-03-2003') and retuned ,to be used in a Insert proc which I have.
    I have the logic for concatenation of values depending on the datatype,but how do I loop through each record for the above query????

    Can anybody help me or anybody has a reusable code for doing this.

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Question Oracle ?

    Hello,

    when you use Oracle you can use the DBMS_SQL package to open,
    bind vars, fetch records and of course close the cursor.

    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Feb 2003
    Posts
    7

    Re: Oracle ?

    I am able to achieve it if the dynamic sql return only one row. If the query return more than one row,how do I achieve that.
    Once I execute th query,how do I find that ,it has reurned more than 1 row and how do I bind it later???
    I would appreciate ur ans in this rgd

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Example for dynamic fetching Cursors

    Hello,

    here is an example:

    handle Integer;
    dbms_return Integer;
    tablename varchar(30);
    colname varchar(30);
    datatype varchar(9);
    counter integer := 0;

    BEGIN

    handle := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(handle,
    'select distinct table_name,column_name,data_type ' ||
    'from user_tab_columns ', DBMS_SQL.V7);

    DBMS_SQL.DEFINE_COLUMN(handle, 1, tablename, 30);
    DBMS_SQL.DEFINE_COLUMN(handle, 2, colname, 30);
    DBMS_SQL.DEFINE_COLUMN(handle, 3, datatype, 9);


    dbms_return := DBMS_SQL.EXECUTE(handle);

    counter := 0;
    loop
    if DBMS_SQL.FETCH_ROWS(handle) = 0 then
    exit;
    else
    DBMS_SQL.COLUMN_VALUE(handle, 1, tablename);
    DBMS_SQL.COLUMN_VALUE(handle, 2, colname);
    DBMS_SQL.COLUMN_VALUE(handle, 3, datatype);

    counter := counter + 1;

    ... do everything you want with that values !!!
    end if;
    end loop;
    n := counter;
    DBMS_SQL.CLOSE_CURSOR(handle);
    END;


    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.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
  •