1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Noresult after using EXECUTE IMMEDIATE

Discussion in 'General' started by ktn23www, Jul 30, 2009.

  1. ktn23www

    ktn23www Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    There is no result after i used following sentence. Actually, select triederror FROM sa_users2 have a result in sql*plus(Oracle10g). I don't know why?

    BEGIN
    EXECUTE IMMEDIATE 'select triederror FROM sa_users2';
    END;

    Is there anyone explain it please?
    Thanks.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    You can't see the output from a execute immediate statement in a procedure. You can either put your output from select statement in a variable and dbms_output it or in your case you should use a ref cursor.

    Something like this:
    Code (SQL):

    CREATE OR REPLACE PROCEDURE proc( rc IN OUT SYS_REFCURSOR ) IS
    BEGIN
    OPEN rc FOR 'select ' || col_name || ' from ' || TABLE_NAME;
    END;
     
  3. ktn23www

    ktn23www Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thank you very much. I will try.
     
  4. ktn23www

    ktn23www Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi !

    Thank you for reply my question. I changed the code like this:

    CREATE OR new PACKAGE BODY SELECT_Sa_users2 AS
    PROCEDURE GetSa_users2
    (
    p_employee_id IN NUMBER,
    cur_JobHistory OUT T_CURSOR
    )
    IS
    BEGIN
    OPEN cur_JobHistory FOR
    SELECT * FROM sa_users2
    WHERE userid = p_employee_id;

    END GetSa_users2;
    END SELECT_Sa_users2;

    But Error: ORA-00905: missing keyword
    ?
     
  5. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    This error shows that a required keyword is missing check your syntax

    ORA-00905 missing keyword
    Cause: A required keyword is missing.
    Action: Correct the syntax.
     
  6. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi

    haven't gone through your code, just noticed that you have "new" in place of "replace"
    Code (SQL):

    CREATE OR REPLACE PACKAGE BODY select_sa_users2
    AS
       PROCEDURE getsa_users2 (
          p_employee_id    IN       NUMBER,
          cur_jobhistory   OUT      t_cursor
       )
       IS
       BEGIN
          OPEN cur_jobhistory FOR
             SELECT *
               FROM sa_users2
              WHERE userid = p_employee_id;
       END getsa_users2;
    END select_sa_users2;
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There is a result, you simply cannot see it; using BULK COLLECT and a collection variable you can access the data returned:
    Code (Text):

    SQL> create table sa_users2(
      2          attempt number,
      3          triederror varchar2(12)
      4  );

    Table created.

    SQL>
    SQL> insert into sa_users2
      2  values (1, 'You betcha');

    1 row created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> DECLARE
      2          type t_err_typ is table of sa_users2%rowtype index by binary_integer;
      3          errtab t_err_typ;
      4  BEGIN
      5          EXECUTE IMMEDIATE 'select attempt, triederror FROM sa_users2' bulk collect into errtab;
      6          for i in errtab.first..errtab.last loop
      7            dbms_output.put_line(errtab(i).triederror);
      8          end loop;
      9  END;
     10  /
    You betcha

    PL/SQL procedure successfully completed.

    SQL>
    SQL> begin
      2          for i in 1..10 loop
      3              insert into sa_users2
      4              select i+1, 'Another try' from dual;
      5          end loop;
      6
      7          commit;
      8
      9  end;
     10  /

    PL/SQL procedure successfully completed.

    SQL>
    SQL>
    SQL> DECLARE
      2          type t_err_typ is table of sa_users2%rowtype index by binary_integer;
      3          errtab t_err_typ;
      4  BEGIN
      5          EXECUTE IMMEDIATE 'select attempt, triederror FROM sa_users2' bulk collect into errtab;
      6          for i in errtab.first..errtab.last loop
      7            dbms_output.put_line(errtab(i).triederror);
      8          end loop;
      9  END;
     10  /
    You betcha
    Another try
    Another try
    Another try
    Another try
    Another try
    Another try
    Another try
    Another try
    Another try
    Another try

    PL/SQL procedure successfully completed.

    SQL>
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    BULK COLLECT works quite well with EXECUTE IMMEDIATE so no ref cursor is necessary.

    See my example posted elsewhere in this thread.
     
  9. ktn23www

    ktn23www Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thank you very much. close studing....