OraFAQ Forum: SQL & PL/SQL » Approach Needed ( Pl/SQl table?)

Home » SQL & PL/SQL » SQL & PL/SQL » Approach Needed ( Pl/SQl table?) (Oracle 11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Approach Needed ( Pl/SQl table?) [message #561242]

Thu, 19 July 2012 13:41

gentleman777us
Messages: 99
Registered: April 2005

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

Report message to a moderator

Re: Approach Needed ( Pl/SQl table?) [message #561244 is a reply to message #561242]

Thu, 19 July 2012 14:19

Littlefoot
Messages: 17256
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'
              )

Report message to a moderator

Re: Approach Needed ( Pl/SQl table?) [message #561245 is a reply to message #561244]

Thu, 19 July 2012 14:33

gentleman777us
Messages: 99
Registered: April 2005

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

Report message to a moderator

Re: Approach Needed ( Pl/SQl table?) [message #561246 is a reply to message #561245]

Thu, 19 July 2012 15:56

BlackSwan
Messages: 20247
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/

Report message to a moderator

Re: Approach Needed ( Pl/SQl table?) [message #561249 is a reply to message #561246]

Thu, 19 July 2012 16:20

gentleman777us
Messages: 99
Registered: April 2005

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).

Report message to a moderator

Re: Approach Needed ( Pl/SQl table?) [message #561250 is a reply to message #561249]

Thu, 19 July 2012 16:29

BlackSwan
Messages: 20247
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.

Report message to a moderator

Previous Topic:	building a pl/sql query from select statement
Next Topic:	Truncate precision in number datatype oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 20 04:17:07 CDT 2013

Total time taken to generate the page: 0.24857 seconds