Problem with Dynamic sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Problem with Dynamic sql

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    1
    I am trying to use dynamic sql for getting the max value of any(I/P) column from any table(I/P) dynamically.

    the following procedure compiles without any problems.But while executing it gives 'invalid column error'. Can You pl help me out??


    CREATE OR REPLACE PROCEDURE GetMaxValue (
    p_table_id VARCHAR2(50),
    p_column_id VARCHAR2(50)) AS

    v_CursorID INTEGER;
    v_SelectStmt VARCHAR2(500);
    v_Dummy INTEGER;
    maxp VARCHAR2(500);
    p_table VARCHAR2(50);
    p_column VARCHAR2(50);

    BEGIN
    select ---- into p_column
    from --- where --- = p_column_id;

    select ---- into p_table
    from --- where --- = p_table_id;

    v_CursorID := DBMS_SQL.OPEN_CURSOR;

    v_SelectStmt := 'SELECT max( '||p_column||' ) '||' FROM '||p_table;

    DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
    DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,maxp,50);
    v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
    LOOP


    IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
    EXIT;
    END IF;
    DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, maxp);
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    END LOOP;
    UPDATE FIELD set
    FLDMXMSTRNG = maxp
    where STRCTRSYSTMNM = p_table and
    FLDSYSTMNM = p_column
    ;


    END;


    /

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    Check the column name (p_column) retrieved from the select into statement.

  3. #3
    Join Date
    Oct 2000
    Posts
    123
    First, the passing parameters:
    .................
    (p_table_id VARCHAR2(50),
    p_column_id VARCHAR2(50)) will give warning error( not fatal error) while compiling the procedure, so they should be:
    .................
    (p_table_id VARCHAR2,
    p_column_id VARCHAR2)

    Second, the select....... into ........ statements will return more than one rows that satisfy the condition probably, but I'm not sure this is the exact reason for the error occurred, I don't know what are the "---------" means in the two "select into" statements.

    Take care

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