Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    17

    Unanswered: Building dynamic query

    Hi,
    I'm trying to build a dynamic query and retun its result within a procedure. Am using ref cursor and execute immediate statement. My proc looks like this:
    CREATE OR REPLACE PROCEDURE PROC_QUERY_CCR_NEW
    (P_RECORDSET OUT SYS_REFCURSOR,
    RELEASE_NM IN CONFIG_REQUEST.RELEASE_NAME%TYPE DEFAULT NULL,
    PRODUCT_NM IN CONFIG_REQUEST.PRODUCT_NAME%TYPE DEFAULT NULL,

    SUB_CAT IN CONFIG_REQUEST.SUB_CATEGORY%TYPE DEFAULT NULL,

    DEVELOPER_NM IN CONFIG_REQUEST.DEVELOPER_NAME%TYPE DEFAULT NULL,

    STAT IN CONFIG_REQUEST.STATUS%TYPE DEFAULT NULL,

    REQID IN CONFIG_REQUEST.CCRID%TYPE DEFAULT NULL
    )
    AS

    WHERE_CLAUSE VARCHAR2(100) := ' WHERE ';
    RN VARCHAR(30) := ' RELEASE_NAME = ''' || RELEASE_NM || '''';
    PN VARCHAR(30) := ' AND PRODUCT_NAME = ''' || PRODUCT_NM || '''';
    SC VARCHAR(30) := ' AND SUB_CATEGORY = ''' || SUB_CAT || '''';
    DN VARCHAR(30) := ' AND DEVELOPER_NAME = ''' || DEVELOPER_NM || '''';
    END_STMT VARCHAR(2) := ';';
    SQL_STMT VARCHAR(1000) := 'SELECT * FROM CONFIG_REQUEST ';--'' || RN || '''';
    BEGIN



    IF RELEASE_NM IS NULL THEN WHERE_CLAUSE := NULL ;
    RN := NULL;

    END IF;
    IF PRODUCT_NM IS NULL THEN PN:= NULL;
    END IF;

    SQL_STMT := SQL_STMT || WHERE_CLAUSE || RN || PN || END_STMT ;
    OPEN P_RECORDSET FOR SQL_STMT;




    END;

    when i execute i using :
    exec PROC_QUERY_CCR_NEW(:x_refcur, '06.38.00');
    this is the error i get:

    ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "SYSTEM.PROC_QUERY_CCR_NEW", line 35
    ORA-06512: at line 1

    Can u help me out with this?

    -regards,
    sajitha

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Obviously, you did not read or FOLLOW the posting guidelines as stated in the #1 STICKY post at the top of this forum.
    >ORA-06512: at "SYSTEM.PROC_QUERY_CCR_NEW", line 35
    OK, I give up. which line is # 35?
    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.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    PL/SQL User's Guide and Reference, OPEN-FOR statement, Usage Notes:
    The dynamic string can contain any multi-row SELECT statement (without the terminator).
    Actually, why did you decided to include END_STMT in sql_stmt?
    Other issue is you are NOT binding, but that is "only" performance affecting. Study the link I provided.

  4. #4
    Join Date
    Jun 2007
    Posts
    17
    The problem has been resolved. Thanks...

  5. #5
    Join Date
    Jun 2007
    Posts
    17
    Quote Originally Posted by anacedent
    Obviously, you did not read or FOLLOW the posting guidelines as stated in the #1 STICKY post at the top of this forum.
    >ORA-06512: at "SYSTEM.PROC_QUERY_CCR_NEW", line 35
    OK, I give up. which line is # 35?


    I'll remember that next time.
    Thanks anyways... the issue is resolved...

Posting Permissions

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