Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    13

    Unanswered: How to use the column names generated from Dynamic SQL

    Hi,

    I have a problem with Dynamic SQL.

    I have written an SQL which will dynamically generate the Select statement with from and where clause in it.
    But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

    For that i have used a ref cursor to open and insert the table.

    In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

    Please find below the sample code:

    TYPE ref_csr IS REF CURSOR;

    insert_csr ref_csr;

    v_select VARCHAR2 (4000) := NULL;
    v_table VARCHAR2 (4000) := NULL;
    v_where VARCHAR2 (4000) := NULL;
    v_ins_tab VARCHAR2 (4000) := NULL;
    v_insert VARCHAR2 (4000) := NULL;
    v_ins_query VARCHAR2 (4000) := NULL;

    OPEN insert_csr FOR CASE
    WHEN v_where IS NOT NULL
    THEN 'SELECT '
    || v_select
    || ' FROM '
    || v_table
    || v_where
    || ';'
    ELSE 'SELECT ' || v_select || ' FROM ' || v_table || ';'
    END;

    LOOP
    v_ins_query :=
    'INSERT INTO '
    || v_ins_tab
    || '('
    || v_insert
    || ') VALUES ('
    || How to fetch the column names here
    || ');';

    EXECUTE IMMEDIATE v_ins_query;

    END LOOP;

    Please help me out with the above problem.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Easy answer, don't use execute immediate. Use DBMS_SQL instead and for goodnes sake USE BIND VARIABLES!!!!!!

    And then to top it off you are using RBAR (look it up!) processing to perform this insert.

    You are busy writing a TITANTIC security hole into your app! You are also creating THE MOST non performant code possible.
    STEP AWAY FROM THE KEYBOARD NOW!

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    yet another Good Reason to avoid Dynamic SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Aug 2012
    Posts
    13
    Hi dayneo,

    Bind Variables can be used if i'm passing some values to the where clause.

    Since i'm building where clause also but not to a specific condition there is no need of Bind Variables.

    So i'm asking how to execute and get the column values to be inserted.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    Quote Originally Posted by kumar0828 View Post
    Since i'm building where clause also but not to a specific condition there is no need of Bind Variables.
    This shows a major lack of understanding of what bind variables are about. Any variable value used in a SQL statement MUST be a bind variable. If you don't use bind variables then you are firstly going to absolutely kill your db performance (not just this one session but the entire database will be slowed down) and secondly going to leave a massive hole for sql injection (the top gateway for db hackers).

    The general idea is not to have dynamic SQL. Try to find a generic way of achieving your goal if you can. Only use dynamic SQL if there is absolutely no way that you can achieve the desired result without it (and no, having to create 5 different SQL statements to cover all cases is not a good enough excuse). And once more for effect: if you must use dynamic SQL you still need to use bind variables, even if the variable part is in the where clause.

    Before I offer a solution, I would like to understand why you are trying to dynamically build the SQL statements. Is this code for use in a dynamic reporting tool or is this a "utility" for developers to use?

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
  •