Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: execute dynamic sql stmt in stored procedure

    Hello,

    I have the following stored proc:

    Code:
    CREATE OR REPLACE  PROCEDURE "SYSADM"."SP_TEST" ( bcr_id_in in 
        number, cursor_out out types.cursorType )
    AS
          
          mySQL varchar2(255);
     
    BEGIN
      mySQL := 'select * from bcrs';
      open cursor_out for execute mySQL;
    END;
    but when I compile it I get this error:

    Code:
    Line # = 8 Column # = 31 Error Text = PLS-00103: Encountered the symbol "MYSQL" when expecting one of the following:     . ( * @ % & = - + ; < / > at in is mod not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like    between using || The symbol "." was substituted for "MYSQL" to continue.
    I have also tried this:

    Code:
    CREATE OR REPLACE  PROCEDURE "SYSADM"."SP_TEST" ( bcr_id_in in 
        number, cursor_out out types.cursorType )
    AS
          
          mySQL varchar2(255);
     
    BEGIN
      mySQL := 'select * from bcrs';
      open cursor_out for execute :mySQL;
    END;
    but I get this error:

    Code:
    Line # = 8 Column # = 31 Error Text = PLS-00049: bad bind variable 'MYSQL'
    Line # = 8 Column # = 31 Error Text = PLS-00103: Encountered the symbol "" when expecting one of the following:     . ( * @ % & = - + ; < / > at in is mod not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like    between using || The symbol "*" was substituted for "" to continue.
    I would appreciate any help with this I can get.

    What I am ultimately trying to accomplish is to build a sp that I can pass search values to. It will dynamically build the where clause, get the results and pass them back in a cursor.

    I know how to pass the result set back, and how to dynamically build the SQL statement...but just cannot get it to execute.

    Thanks in advance for your help.

    James

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    lookup execute_immediate

    this will do what you want to accomplish.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697

    Re: execute dynamic sql stmt in stored procedure

    Originally posted by jrlaughlin
    Code:
    CREATE OR REPLACE  PROCEDURE "SYSADM"."SP_TEST" ( bcr_id_in in 
        number, cursor_out out types.cursorType )
    AS
          mySQL varchar2(255);
    BEGIN
      mySQL := 'select * from bcrs';
      open cursor_out for execute mySQL;
    END;
    but when I compile it I get this error:

    Code:
    Line # = 8 Column # = 31 Error Text = PLS-00103: Encountered the
    Hi,

    Try this...

    open cursor_out for mySQL;

    This certainly works....
    open cursor_out for 'select * from tablename order by '||orderstr;

    etc.

    Hth
    Bill

Posting Permissions

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