Results 1 to 4 of 4

Thread: How to execute

  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: How to execute

    Hi
    I've a query in a variable(PL/SQL). Can anybody help in executing that query?
    ex:
    declare
    qry varchar2(1000);
    begin
    qry:='select last_name from employees';
    end;

    How to execute qry?
    Thanks in advance

    Anitha

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    There are two ways to execute
    declare
    qry varchar2(1000);
    begin
    qry:='select last_name from employees';
    Execute immediate qry;
    end;

    Declare
    Cur INTEGER;
    qry varchar2(1000);
    Begin
    Cur:=dbms_sql.open_cursor;
    qry:= 'select last_name from employees';
    dbms_sql.parse(Cur,qry,dbms_sql.v7);
    dbms_sql.execute(Cur);
    dbms_sql.close_cursor(Cur);
    end;

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Here is a third:

    Code:
    TYPE cur_type IS REF CURSOR;
    cur_var  cur_type;
    qry VARCHAR2(1000);
    last_name_v employees.last_name%TYPE;
    BEGIN
      qry := 'select last_name from employees';
      OPEN cur_var FOR qry;
      LOOP
        FETCH cur_var INTO last_name_v;
        EXIT WHEN cur_var%NOT_FOUND;
    ...
      END LOOP;
      CLOSE cur_var;
    END;
    More useful if you have more than one row to return.

    JoeB

  4. #4
    Join Date
    Mar 2004
    Posts
    14

    Wink

    Fourth:

    Execute immediate 'select * from employers' bulk collect INTO my_table;

    where my_table is table of employers%rowtype

    but I like ref cursor more

Posting Permissions

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