stored procedure with dynamic sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: stored procedure with dynamic sql

  1. #1
    Join Date
    Nov 2005
    Posts
    4

    stored procedure with dynamic sql

    Hello,

    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)
    is
    begin
    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:

    $testanzahl="";
    $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,':spalten_in',&$spalten,100);
    ocibindbyname($sql_statement_search,':searchtext_in',&$searchtext,100);
    ocibindbyname($sql_statement_search,':machword_in',&$machword,100);
    ocibindbyname($sql_statement_search,':searchresult_cv_inout ,&$curs_search,-1,OCI_B_CURSOR);
    ociexecute($sql_statement_search,OCI_DEFAULT);
    ociexecute($curs_search,OCI_DEFAULT);
    while (OCIFetchInto($curs_search,$results))
    ......


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

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

    SQL> select * from testtable;

    ID CAR DOORS
    ----------- ---------
    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)
    is
    begin
    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