Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Unanswered: Urgent:Help to use REF CURSOR

    Hi All,

    I want a help in the use of REF CURSOR...
    Actually my requiremnt is to fetch all the record from any table..the table name and parameter foR selection will be passed to procedure. As I dont know in advance the TABLE NAME, Number of columns and their names passed in the procedure so I m using week REF CURSOR to fetch all the records from the table..
    Error code and error mesg are also passed as the out parameter from the procedure.

    Here I am pasting the code for the package and procedure..they are created successfully but the error is in the anonymous pl/sql block where i want to fetch the records from the passed ref cursor output parameter..
    Please advise if you have the solution of the error..

    ================
    CREATE OR REPLACE PACKAGE RDNXT_pkg AS
    TYPE record_cur_type IS REF CURSOR;
    PROCEDURE RDNXT(VAR_TABLE_NAME IN VARCHAR2,in_string IN VARCHAR2,err_code OUT VARCHAR2,err_msg OUT VARCHAR2,cursor IN OUT record_cur_type);
    END RDNXT_pkg ;


    CREATE OR REPLACE PACKAGE BODY RDNXT_pkg AS
    PROCEDURE RDNXT(VAR_TABLE_NAME IN VARCHAR2,in_string IN VARCHAR2,err_code OUT VARCHAR2,err_msg OUT VARCHAR2,cursor IN OUT record_cur_type)
    IS
    --TYPE COLMLIST is varray(100) of VARCHAR2(25);

    vsql VARCHAR2(100);

    BEGIN

    vsql:='select ' || in_string || ' from ' || VAR_TABLE_NAME;
    DBMS_OUTPUT.put_line('QUERY Is :' ||' '||vsql);
    OPEN cursor FOR vsql;
    err_code := SQLCODE;
    err_msg := substr(SQLERRM, 1, 200);

    EXCEPTION
    WHEN OTHERS THEN
    err_code := SQLCODE;
    err_msg := substr(SQLERRM, 1, 200);

    END;
    END RDNXT_pkg;
    =====================

    DECLARE
    IN_STRING VARCHAR2(32767);
    CURSOR RDNXT_pkg.record_cur_type

    err_code VARCHAR2(20);
    err_msg VARCHAR2(200);
    var_table_name varchar2(30);

    TYPE v_POLYLPDT IS TABLE OF varchar2(12);
    TYPE v_POLYNPDT IS TABLE OF varchar2(12);
    TYPE v_POLYTGFG IS TABLE OF varchar2(12);
    list_POLYLPDT v_POLYLPDT ;
    list_POLYNPDT v_POLYNPDT;
    list_POLYTGFG v_POLYTGFG ;


    BEGIN
    var_table_name :='EMPLOYEE';
    IN_STRING :='EMPID,EMPNAME,EMPDESG ';
    DBMS_OUTPUT.put_line('Primary Table name Is :' ||' '||var_table_name);
    DBMS_OUTPUT.put_line('Data List Input String Is :' ||' '||IN_STRING);


    RDNXT_pkg.RDNXT(var_table_name, IN_STRING ,err_code ,err_msg,CURSOR); ----CALL TO THE STORED READM PROCEDURE
    FETCH CURSOR BULK COLLECT INTO list_POLYLPDT , list_POLYNPDT,list_POLYTGFG ;
    CLOSE CURSOR;

    FOR i IN list_POLYLPDT.FIRST .. list_POLYLPDT.LAST
    LOOP
    DBMS_OUTPUT.PUT_LINE
    ('"' || list_POLYLPDT(i) ||'"' ||','|| '"' || list_POLYNPDT(i) ||'"'||','||'"' ||list_POLYTGFG(i)||'"');

    END LOOP;
    END;
    ================================================== ========
    when I run this anonymous block I got the error MARKED RED AS ABOVE AND description is as below:
    CURSOR RDNXT_pkg.record_cur_type
    *
    ORA-06550 LINE 3 COLUMN 7
    PLS-00103-ENCOUNTERED THE SYMBOL "." WHEN EXPECTING ONE OF THE FOLLOWING : <; IS RETURN
    THE SYMBOL "RETURN WAS inserted before "." to continue

    ===========================
    Please show me the correct way to do this....

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Actually my requiremnt is to fetch all the record from any table..the table name and parameter foR selection will be passed to procedure.

    how does code know the number & datatypes of columns being returned?
    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
    Jan 2011
    Posts
    3
    Actually the requirement is that the number of columns to be fetched from the table is not known before the run time.So it must be handled at the run time..
    I again tried the same thing with the SYS_REFCURSOR...Now it worked fine..
    below is the code..

    ================================================== ===

    CREATE OR REPLACE PROCEDURE READ(VAR_TABLE_NAME IN VARCHAR2,in_string IN VARCHAR2,err_code OUT VARCHAR2,err_msg OUT VARCHAR2,cursor IN OUT SYS_REFCURSOR)
    IS
    vsql VARCHAR2(100);
    BEGIN
    vsql:='select ' || in_string || ' from ' || VAR_TABLE_NAME;
    DBMS_OUTPUT.put_line('QUERY Is :' ||' '||vsql);
    OPEN cursor FOR vsql;
    err_code := SQLCODE;
    err_msg := substr(SQLERRM, 1, 200);
    EXCEPTION
    WHEN OTHERS THEN
    err_code := SQLCODE;
    err_msg := substr(SQLERRM, 1, 200);
    END;
    =============================================

    Now this fetch the multiple records from the table..that I wanted.Please also let me know another method to implement if other methods are there..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Now this fetch the multiple records from the table..that I wanted.
    REALLY?
    which line of posted code actually FETCH data & INTO what?

    What about SQL injection?
    What about required privileges?
    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.

  5. #5
    Join Date
    Jan 2011
    Posts
    3
    Hi,

    I using this procedure in a anonymous block.In the anonymous block itself I fetched the cursor using the BULK COLLECT INTO command and then according to the requirement I m separating the each records.
    see belo code for anonymous block..


    DECLARE
    IN_STRING VARCHAR2(32767);
    CUR_var SYS_REFCURSOR;
    err_code VARCHAR2(20);
    err_msg VARCHAR2(200);
    var_table_name varchar2(30);

    TYPE v_POLYLPDT IS TABLE OF varchar2(12);
    TYPE v_POLYNPDT IS TABLE OF varchar2(12);
    TYPE v_POLYTGFG IS TABLE OF varchar2(12);
    list_POLYLPDT v_POLYLPDT ;
    list_POLYNPDT v_POLYNPDT;
    list_POLYTGFG v_POLYTGFG ;


    BEGIN
    var_table_name :='TPEMPLOYEE'; --TABLE NAME
    IN_STRING :='EMPPOLYLPDT,EMPPOLYNPDT,EMPPOLYTGFG '; --COLUMNS ---------NAME IN A STRING SEPARATED BY ','


    DBMS_OUTPUT.put_line('Primary Table name Is :' ||' '||var_table_name);
    DBMS_OUTPUT.put_line('Data List Input String Is :' ||' '||IN_STRING);



    READ(var_table_name,IN_STRING,err_code ,err_msg,CUR_var); ----CALL TO THE STORED READM PROCEDURE
    FETCH CUR_var BULK COLLECT INTO list_POLYLPDT , list_POLYNPDT,list_POLYTGFG ;
    CLOSE CUR_var ;


    FOR i IN list_POLYLPDT.FIRST .. list_POLYLPDT.LAST
    LOOP
    DBMS_OUTPUT.PUT_LINE
    ('RECORD['|| i || ']'||' ' || '"' || list_POLYLPDT(i) ||'"' ||','|| '"' || list_POLYNPDT(i) ||'"'||','||'"' ||list_POLYTGFG(i)||'"');

    END LOOP;
    END

Posting Permissions

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