Results 1 to 2 of 2

Thread: Dynamic SQL

  1. #1
    Join Date
    Mar 2004
    Location
    Chennai, India
    Posts
    18

    Unanswered: Dynamic SQL

    Hi,

    We are trying to build a front end using forms where the user can select both the columns he wants to see and the where clause. The Result of the query build should be spooled to an excel.

    In the cursor we select all the columns in the table never mind if the user has selected it or not. But while spooling, we need to select only the selected columns.

    Sample code used:

    DECLARE
    TYPE cv_type IS REF CURSOR;
    cv cv_type;

    temp territory_master%rowtype;
    BEGIN

    OPEN cv FOR
    'SELECT * FROM TERRITORY_MASTER WHERE ROWNUM';

    loop
    FETCH cv into temp;
    dbms_OUTPUT.PUT_LINE(???);
    EXIT WHEN cv%NOTFOUND;
    END LOOP;
    END;

    Now how to bring only the selected columns into dbms output.

    Note : I have the user selected columns separated by comma, in a variable
    Palaniappan.S
    Chennai.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Dynamic SQL

    Presumably using logic something like:

    PHP Code:
    IF <user selected COL1THEN
      v_record 
    := v_record||','||temp.col1;
    END IF;
    ... 
    etc
    If you wanted to make this more generic you would have to use DBMS_SQL, which allows you to interrogate the cursor at runtime to see the column definitions using DESCRIBE_COLUMNS.

Posting Permissions

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