0

I have an issue with migration of data in Oracle DB during some release upgrades.

Case:

  • Table X in release 1 has three coulmns.
  • Same Table X in release 2 has five columns(two added in release 2).
  • Same table in release 3 has five columns as in release 2.
  • Upgrade paths include Release 1 to Release 3 and Release 2 to Release 3.

I need a Oracle SQL query which copies data from a TMP table to actual table in both cases based on coulmns size from TMP where i have stored the data temporarily(this has to be done).

Below is the query which i tried but it isnt working.

insert into USER.X values (CASE (select count(*) from all_tab_columns where table_name='TMP') WHEN '3' THEN (select USER.TMP.*, null NEWCOL1 from USER.TMP, null NEWCOL2 from USER.TMP) WHEN '5' THEN (select USER.TMP.* from USER.TMP) END );

Please help in this regard and if there is a better way of doing the same please let me know.

Improve this question
1
  • isn't working? usually it helps to post the exact error. This is a rare case where I can tell though. instead of values (a,b,c); use select a,b,c from dual; Commented Oct 5, 2015 at 7:11

1 Answer 1

Reset to default
2

Edit: There are multiple problems in you logic.

  1. You cannot determine number of parameters to insert statement at runtime. You have to determine it before creating insert statement.
  2. Case returns only 1 value. More than that and you will get error too many values

So you should

  • Create stored proc
  • Use if else and create insert statement based on that.
  • Execute it by Execute immidiate

Prev. Response

The first problem in your query is that select count(*) from all_tab_columns where table_name='TMP' returns an integer, whereas in case you are comparing it to '3' and '5' as varchar. So assuming that rest of your query return result correctly, try replacing '3' and '5' as 3 and 5

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Nick.McDermaid : I have changed the query as suggested INSERT INTO USER.X VALUES ( CASE (SELECT COUNT(*) FROM all_tab_columns WHERE table_name='TMP' ) WHEN 3 THEN (SELECT USER.TMP.*, NULL NEWCOL1, NULL NEWCOL2 FROM USER.TMP ) WHEN 5 THEN (SELECT USER.TMP.* FROM USER.TMP ) END ); But i get the below error: Error at Command Line:2 Column:53 Error report: SQL Error: ORA-00947: not enough values 00947. 00000 - "not enough values" *Cause: *Action:
Editing the answer to explain what is wrong with your query.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.