Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    4

    Unanswered: MS SQL EXEC equivalent?

    Hi All,

    Is there a way in oracle PL/SQL to execute a SQL statement. I'm coming from a MS SQL environment to oracle, and in MS SQL we have an EXEC command to execute statement such as:

    EXEC('select * from table')

    Is there an equivalent in Oracle (8.1.7)?

    Thanks in advance

    MIke

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: MS SQL EXEC equivalent?

    Originally posted by mogsey2003
    Hi All,

    Is there a way in oracle PL/SQL to execute a SQL statement. I'm coming from a MS SQL environment to oracle, and in MS SQL we have an EXEC command to execute statement such as:

    EXEC('select * from table')

    Is there an equivalent in Oracle (8.1.7)?

    Thanks in advance

    MIke
    Well kind of... there is EXECUTE IMMEDIATE, but for the example you gave it wouldn't do very much:

    SQL> BEGIN
    2 EXECUTE IMMEDIATE 'select * from table';
    3 end;
    4 /

    PL/SQL procedure successfully completed.

    It worked, but the result doesn't appear anywhere.

    You can open a cursor for the select like this:

    Code:
    DECLARE
      TYPE rc_type REF CURSOR;
      rc rc_type;
      table_rec table%ROWTYPE;
    BEGIN
      OPEN rc FOR 'select * from table';
      LOOP
        FETCH rc INTO table_rec;
        EXIT WHEN rc%NOTFOUND;
        -- Process this row, e.g.
        DBMS_OUTPUT.PUT_LINE( 'Name: '||table_rec.name );
      END LOOP;
    END;
    /

  3. #3
    Join Date
    Mar 2003
    Posts
    4
    Thanks for the prompt reply.

    I'm connecting to oracle from VB and am using ref cursor to return a recordset to VB. I've been pretty much doing what you suggested. For example:

    ----------------------------------------------
    PROCEDURE UserSearch(piv_Forename IN VARCHAR2, piv_Surname IN VARCHAR2, pocur_Users OUT typrcur_Users) IS

    BEGIN

    OPEN pocur_Users FOR
    SELECT UserID,
    Forename,
    Surname,
    Email,
    Department
    FROM USERS
    WHERE Forename LIKE '%' || piv_forename || '%'
    AND Surname LIKE '%' || piv_surname || '%'
    ORDER BY UserID;

    END UserSearch;
    ----------------------------------------------

    typrcur_Users is defined as a ref cursor and is passed back to vb as a recordset. I ssume that just putting a select in quotes would do the same thing:

    ----------------------------------------------
    PROCEDURE SelectSomething(pocur_Users OUT typrcur_Users) IS

    BEGIN

    OPEN pocur_Users FOR
    'SELECT * FROM USERS';


    END SelectSomething;
    ----------------------------------------------

    Correct? I'll give it a try.

  4. #4
    Join Date
    Mar 2003
    Posts
    4
    Yip that seems to do the trick, thank you andrewst for your time and help.

    Mike

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, that will work.

Posting Permissions

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