-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|