Home » SQL & PL/SQL » SQL & PL/SQL » sql problem (sql navigator using oracle 10g)
sql problem [message #391038] Tue, 10 March 2009 14:36 Go to next message
nalifan
Messages: 4
Registered: March 2009
Junior Member
I have a query that is hard coded:

for rec in (

select *
from (
select t.*, rownum as rownumber
from jumper t
where found_date between v_date1 and v_date2
order by rownumber )
where rownumber between v_rowCountStart and v_rowCountEnd
)
loop

htp.p('
<tr>
<td>'||rec.rownumber||'</td>
<td>'||rec.found_date||'</td>
</tr>
');
end loop;

The vairables in the where statement work just fine.
What I need help with is making the from statement like the variables.

Basically I have a website where the user chooses from a select box what table they want to select from. If I put the variable name in the from clause (ie. from variable) it causes problems because the variable name isn't an actual table.

Is there a way to make the from statement dynamic?
I tried using an execute immediate statement which worked, but I can't return any information to be displayed from an execute immediate statement.

Any help would be great.
Re: sql problem [message #391044 is a reply to message #391038] Tue, 10 March 2009 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

have 1 SELECT statement for each table.
Re: sql problem [message #391048 is a reply to message #391038] Tue, 10 March 2009 15:48 Go to previous messageGo to next message
nalifan
Messages: 4
Registered: March 2009
Junior Member
I have a query that is hard coded:
PROCEDURE query_sql( p_from in varchar2 ) 
   IS

v_from varchar2(50);

BEGIN

v_from := p_from;

htp.p(' <table> ');
   FOR rec IN
   (
      SELECT *
      FROM [B]jumper[/B]
   )
   LOOP
      htp.p('
         <tr>
            <td>'||rec.jumperNum||'</td> 
            <td>'||rec.jumperName||'</td> 
            <td>'||rec.jumperPhone||'</td> 
         </tr> ');
   END LOOP;  
htp.p(' </table> ');
END;


1 Joe Adams 3325526633
2 Anna Bear 3325542858
^ proper results

PROCEDURE query_sql( p_from in varchar2 ) 
   IS

v_from varchar2(50);

BEGIN

v_from := p_from;

htp.p(' <table> ');
   FOR rec IN
   (
      SELECT *
      FROM [B]v_from[/B]
   )
   LOOP
      htp.p('
         <tr>
            <td>'||rec.jumperNum||'</td> 
            <td>'||rec.jumperName||'</td> 
            <td>'||rec.jumperPhone||'</td> 
         </tr> ');
   END LOOP;  
htp.p(' </table> ');
END;


(1): PL/SQL: ORA-00942: table or view does not exist

I know the view (v_from) doesn't exist.

I would like the v_from to be used in the from statement, and then display.

Is this possible?

Desired Results
1 Joe Adams 3325526633
2 Anna Bear 3325542858


Sorry if this is still out of posting guidelines.
Re: sql problem [message #391052 is a reply to message #391048] Tue, 10 March 2009 16:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You will need to dynamically open a ref cursor, as in the example in the link below:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#sthref1067
Re: sql problem [message #391615 is a reply to message #391038] Thu, 12 March 2009 13:15 Go to previous message
nalifan
Messages: 4
Registered: March 2009
Junior Member
Excellent.

Everything seems to work perfectly.
Also, it fixed another problem that I was going to ask next.

Thank You very much.
Previous Topic: PL/SQL comment bug
Next Topic: ora-01735
Goto Forum:
  


Current Time: Thu Dec 08 14:05:20 CST 2016

Total time taken to generate the page: 0.21398 seconds