| From Statement [message #371553] |
Tue, 07 November 2000 00:40  |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
Hi there, can someone help me to solve the problem which I require to select a lot of table name. And it means that I need to write a lot of select statement in the program for different table name. But I don't like writing the long program which is doing a lot of thing in common. And I have tried to use cursor with the parameter of table name as shown below.
cursor customerchecking(tablename VARCHAR2)
select * from tablename
This does not work, since the from statement does not accept the variable.
If you can reply this as soon as possible, It will much be very helpful. Thanks!!!! :P
|
|
|
|
| Re: From Statement [message #371554 is a reply to message #371553] |
Tue, 07 November 2000 03:50  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
You can't pass the tablename like that.
There are two ways you can solve the problem (well actually there are lots, but two of them are easier than the rest)
First, use a Ref cursor:
DECLARE
TYPE c_ref IS REF CURSOE
c_cursor c_ref;
BEGIN
IF {want table one} THEN
OPEN c_cursor FOR
SELECT *
FROM TABLE_ONE
ELSIS {want table two} THEN
OPEN c_cursor FOR
SELEECT *
FROM TABLE_TWO
END IF;
This allows the rest of the code to use one cursor, which has ben opened for the query you want.
The second method is to use dynamic SQL, which is quite a bit more involved.
If you want a walkthrough of this method, just ask.
|
|
|
|
| Re: From Statement [message #371607 is a reply to message #371554] |
Mon, 13 November 2000 23:05 |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
thanks for your tips.
it works. but now I have another problem, which requires to use parameter for select statement.
Because currently I found out that not only I need to have different table to access, but also I need to have different column for the same table. So I thought that passing parameter for select statement is the most efficient. And again, I didn't get the result as what I am expecting. Maybe if you can give me the walkthrough for dynamic SQL might help to solve this problem. Just simply to my email if you want to do it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
