Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Unanswered: Dinamic Procedure !

    I wanna know if I can create a dinamic type and ref cursor in a plsql block to know if I'm able to create a generic procedure like this:


    begin
    --I'd like to use any other table instead of dept

    -- Declare the PL/SQL table
    type deptarr is table of dept%rowtype
    index by binary_integer;
    d_arr deptarr;

    -- Declare cursor
    type d_cur is ref cursor return dept%rowtype;
    c1 d_cur;


    i number := 1;
    begin
    -- Populate the PL/SQL table from the cursor
    open c1 for select * from dept;
    loop
    exit when c1%NOTFOUND;
    fetch c1 into d_arr(i);
    i := i+1;
    end loop;
    close c1;

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    jortiz,

    To use another table, instead of dept, you simply "open" your cursor with a string instead of the actual SQL... for instance:

    sqlStatement := 'SELECT * FROM ' || theTable;

    OPEN c1 FOR sqlStatement;

    As far as how you are returning values into the values, fetching into an array won't work the way I think you expect... The way written (by the way is syntactically incorrect), it would return all the rows into the array, not the columns. To get all of the columns, you would have to declare a record type like this:

    c1_record dept%ROWTYPE;

    ...

    FETCH c1 INTO c1_record;

    then you could reference values like this:

    c1_record.column1

    Hope this helps!

    JoeB

  3. #3
    Join Date
    Jun 2003
    Posts
    294
    Yes Sure, But I mean, I want to CREATE the TYPE dinamic, I mean, when I wan to declare: type d_cur is ref cursor return parameter1%rowtype;
    type deptarr is table of parameter1%rowtype

    where parameter1 is a parameter recibed from the user.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    For future homework assignments, I suggest that you have the instructor post the new problem directly to this forum to save you from having to translate & re-type the problems yourself.
    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.

Posting Permissions

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