Home » SQL & PL/SQL » SQL & PL/SQL » Approach Needed ( Pl/SQl table?) (Oracle 11g)
Approach Needed ( Pl/SQl table?) [message #561242] Thu, 19 July 2012 13:41 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Hi,

Here is my requirement.

1. User searches on a key word ex: 'Paris'.
2. I need to search a regular oracle table(say TABLE_A) for that keyword( i.e paris) and get unique id's from the table.
3. Based on these ID's I need to query TABLE_B to get the details information and insert them into a pl/SQL table( Iam thinking).

The application will call this procedure and use the records from this pl/sql table and display them.

Question: what is the best approach to store the detailed information from TABLE_B Which can be accessible, I do not store them in regular table.

is pl/sql table the best way to do it. if so can someone provide me example?


If there is a better approach please let me know with the example.


Thanks
Re: Approach Needed ( Pl/SQl table?) [message #561244 is a reply to message #561242] Thu, 19 July 2012 14:19 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you think that you need PL/SQL? SQL can do that easily.
select b.*
from table_b b
where b.id in (select a.id
               from table_a a
               where a.key_word = 'paris'
              )
Re: Approach Needed ( Pl/SQl table?) [message #561245 is a reply to message #561244] Thu, 19 July 2012 14:33 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Sorry, I should have been more specific.

TABLE_B was an example. the table name changes based on the ID( most likely it will be a different table for each id)

i.e if the id = 1 then table will be table_b, id = 2 table will be table_c etc.

Hope it is clear now.

Thanks
Re: Approach Needed ( Pl/SQl table?) [message #561246 is a reply to message #561245] Thu, 19 July 2012 15:56 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
>TABLE_B was an example. the table name changes based on the ID( most likely it will be a different table for each id)
Above is bad design, but let us continue the nonsense to the end.
How do you decide/determine the actual table_name to be queried based upon a specific ID value?
You could utilize IF THEN construct or do it more inefficiently using EXECUTE IMMEDIATE.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Approach Needed ( Pl/SQl table?) [message #561249 is a reply to message #561246] Thu, 19 July 2012 16:20 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
I think the task here is to find out the best approach to store the information retrieved out of tables( which are dependent upon the value of the ID column).
Re: Approach Needed ( Pl/SQl table?) [message #561250 is a reply to message #561249] Thu, 19 July 2012 16:29 Go to previous message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
>I think the task here is to find out the best approach to store the information retrieved out of tables
since the data is already stored within the DB, why do you feel it is necessary & appropriate to store details again?


It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Previous Topic: building a pl/sql query from select statement
Next Topic: Truncate precision in number datatype oracle
Goto Forum:
  


Current Time: Fri Aug 22 20:48:01 CDT 2014

Total time taken to generate the page: 0.31323 seconds