Home » SQL & PL/SQL » SQL & PL/SQL » Help urgent.... (Oracle 10g.)
Help urgent.... [message #361169] Tue, 25 November 2008 04:49 Go to next message
snhsundar
Messages: 3
Registered: June 2008
Junior Member
Hi Oracleites,

I am sundar. I am new to forum. I high a request for all please solve it.......

I have table example_data with 2 columns text and action

text action
-------------------------------------------------
table1~123~sundar Insert
table2~oracle~~~123 update

In the above example_data table, i have 2 line items

text description is table name delimited with values
operation is insert.

so here i need to insert the data (123,sundar) into table1(2 columns)

and in next line item, I need to update the table2( 4 columns) with data
(oracle, null, null, 123)

please advise the sol...............

Thanks in advance
sundar.
Re: Help urgent.... [message #361176 is a reply to message #361169] Tue, 25 November 2008 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Remember nothing is urgent in forum.

Regards
Michel
Re: Help urgent.... [message #361178 is a reply to message #361169] Tue, 25 November 2008 05:18 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Sundar,

Please take your time to go through OraFAQ Forum Guide on "How to Format Your Post?"

I didn't get your requirement properly. So explain a bit about your problem with a proper test case. (Create Table and Insert Statements for some sample records)


[***Added: Sorry Didn't see Michel's Post)
Regards,
Jo

[Updated on: Tue, 25 November 2008 05:20]

Report message to a moderator

Re: Help urgent.... [message #361188 is a reply to message #361169] Tue, 25 November 2008 05:32 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

It looks like typical Dynamic SQL problem.

Loop through the table and form the string based on Actiion , table name and fields and EXECUTE IMMEDIATE. Insertion will work fine.

But how the updation will happen ? where will you refer the field name ? And how the where condition will form ?

If field names are in order , you have to refer usera_tab_columns and get the field name and update using Execute Immediate.

Smile
Rajuvan.

[Updated on: Tue, 25 November 2008 05:33]

Report message to a moderator

icon13.gif  Re: Help urgent.... [message #361202 is a reply to message #361188] Tue, 25 November 2008 06:26 Go to previous messageGo to next message
snhsundar
Messages: 3
Registered: June 2008
Junior Member
Thanks for the reply...

but how the insertion will work

Re: Help urgent.... [message #361212 is a reply to message #361188] Tue, 25 November 2008 06:45 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Sundar,

rajavu1 wrote on Tue, 25 November 2008 17:02
It looks like typical Dynamic SQL problem.

Loop through the table and form the string based on Actiion , table name and fields and EXECUTE IMMEDIATE. Insertion will work fine.

But how the updation will happen ? where will you refer the field name ? And how the where condition will form ?

If field names are in order , you have to refer user_tab_columns and get the field name and update using Execute Immediate.



Please explain what haven't you understood from the above quote. Please take some time to go through the link posted by Rajuvan to understand about Dynamic SQL. You will get the answer for your question.

You may need to use SUBSTR Function to split the delimited data. (Still not very clear with your requirement. Only a guess.)

Remember you can get better answers if you follow the guidelines mentioned above.

Hope this helps.
Regards,
Jo
Re: Help in dynamic sql problem.... [message #361836 is a reply to message #361212] Fri, 28 November 2008 03:58 Go to previous messageGo to next message
snhsundar
Messages: 3
Registered: June 2008
Junior Member
here is my details

step1:
---------
table_main with 2 columns
table_name and primary_key

table_name is name of the table. and primary_key is ~ seperated key columns

ex: customer --- table name(say with 5 columns)
customer_id~state_id -- value in primary key column of customer table( uniquly represent the record in customer table.)

step2:
-----

table_sub with 2 columns
text and action

text is ~ seperated words. and actiion is operation we do on table.

ex: text - customer~123~abc~12~~~
(customer table with columns -- customer_id, cust_name,
state_id, add1, add2)

so values from above ex are (123,abc,12,null,null)

action -- Insert/Update/Delete

so based on the action we need to do the operation on the table (table_name is first value from text) with values seperated with ~ delimiter.

we can get the primary keys from table_main table.

Please provide the solution

waiting.....oraclities...... Smile
Re: Help urgent.... [message #361842 is a reply to message #361169] Fri, 28 November 2008 04:20 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

The question was already answered with clues and links.

Now its ypur turn to provide create and insert table script with the code you already tried.

Smile
Rajuvan.
Previous Topic: ORACLE
Next Topic: QUERY SHOULD RETURN 1 HOUR
Goto Forum:
  


Current Time: Thu Dec 08 16:05:51 CST 2016

Total time taken to generate the page: 0.10151 seconds