| How To Access Customized Query in Stored Procedure ? [message #38839] |
Mon, 20 May 2002 06:13  |
mohan
Messages: 16
Registered: August 2001
|
Junior Member |
|
|
I have written a procedure in oracle 8.1.7, which accepts a SELECT St/- from the web page and returns a VARCHAR2 value. This SELECT St/- is constructed on fly by the page and passed to the procedure. This query may retrieve more than one row.
How can I open a cursor using this SELECT St/- and access the rows. I tried "execute immediate", but, it didn't work. May be I made some mistakes in that.
Any help ? Thanks.
|
|
|
|
| Re: How To Access Customized Query in Stored Procedure ? [message #38840 is a reply to message #38839] |
Mon, 20 May 2002 07:00  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
SQL> get dy
1 /* change this stored proc to accept values from the webpage. I have used the standard demo table EMP*/
2 create or replace procedure dy
3 as
4 type my_curs_type is REF CURSOR;
5 curs my_curs_type;
6 str varchar2(200);
7 ret emp%rowtype;
8 begin
9 str := 'select * from emp';
10 OPEN curs FOR str;
11 loop
12 FETCH curs INTO ret;
13 exit when curs%notfound;
14 dbms_output.put_line(ret.ename||','||ret.sal);
15 end loop;
16 CLOSE curs;
17* end;
18 /
Procedure created.
SQL> exec dy
SMITH,800
ALLEN,1600
WARD,1250
JONES,2975
MARTIN,1250
BLAKE,2850
CLARK,2450
SCOTT,3000
KING,5000
TURNER,1500
ADAMS,1100
JAMES,950
FORD,3000
MILLER,1300
PL/SQL procedure successfully completed.
|
|
|
|
| Re: How To Access Customized Query in Stored Procedure ? [message #38842 is a reply to message #38839] |
Mon, 20 May 2002 10:59 |
mohan
Messages: 16
Registered: August 2001
|
Junior Member |
|
|
yes! the length I required may vary from 7000 to 10000 chars. But still it fails. I concatenate using || symbols. It is not taking after 4000 chars. The max size of varchar2 is 4000. so i declared as LONG. But, I got this error.
ORA-00997: illegal use of LONG datatype
Is there any other datatype i can use ?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
