DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005

    stored procedure with dynamic sql


    I have a problem to call an stored procedure with dynamic sql in php.
    The next PL/SQL Part shows only a simple Version of my procedure.
    Database is an Oracle 9iR2.

    I have created the procedure in a package like this:

    create or replace package xxx as

    TYPE searchresultTCurTyp is REF Cursor;

    -- i also replaced the last var of the procedure in the head and
    -- body of the package with
    -- searchresult_cv_inout out sys_refcursor but this also doesn't
    show any result in php.

    procedure search(row_in in varchar2,searchtext_in in varchar2,
    searchresult_cv_inout IN OUT searchresultTCurTyp);
    end xxx;

    create or replace package body xxx as

    procedure search(
    row_in in varchar2,
    searchtext_in in varchar2,
    searchresult_cv_inout IN OUT searchresultTCurTyp)
    stmt := 1;
    open searchresult_cv_inout for
    'select myrow from
    (select rownum myrow,'||row_in||' from xxx order by xxx asc)
    where lower('||row_in||') = lower(searchtext_in)';
    end search;

    end xxx;

    the problem is that i don't get any result from this procedure. I have tried many versions and changes, but till now I can't find a solution.

    This is howI call the procedure in php:

    $curs_search = ocinewcursor($connection);
    $sql_statement_search = OCIParse($connection,"begin xxx.search(:spalten_in,:searchtext_in,:machword_in,:searchresult_cv_inout );end;");
    ocibindbyname($sql_statement_search,':searchresult_cv_inout ,&$curs_search,-1,OCI_B_CURSOR);
    while (OCIFetchInto($curs_search,$results))

    Can somebody help me to get this work.
    Last edited by galaxy; 01-02-2006 at 09:13 AM.

  2. #2
    Join Date
    Nov 2005
    I have createt a testtable. It looks like this

    SQL> select * from testtable;

    ----------- ---------
    1 BMW 4
    2 MERCEDES 4
    3 LOTUS 2

    Now I try to execute my "search procedure" on that table.

    procedure car(
    spalten_in in varchar2,
    searchtext_in in varchar2,
    car_cv_inout IN OUT sys_refcursor)
    stmt := 0;
    open car_cv_inout for
    'select myrow from (select rownum myrow,:s from testtable order by car asc)
    where lower(:s) = lower(:search)' USING in spalten_in,spalten_in,searchtext_in;
    end car;

    In SQL Plus I created a var cv refcursor to get the result of this procedure.
    After that I execute the procedure with
    exec car('CAR','BMW',:cv);

    And i checked the result with print cv. But there is always: "no rows selected" (as in PHP)

    If I start the Query without the procedure like this:
    select myrow from (select rownum myrow,CAR from testtable order by car asc)where lower(CAR) = lower('BMW');
    Then it works fine.

    But I can't find the error, why this procedure always shows no rows.

    Can somebody help me?????

Posting Permissions

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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.