Home » SQL & PL/SQL » SQL & PL/SQL » Try to create a Dynamic column using EXECUTE IMMEDIATE (9i)
Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277530] Tue, 30 October 2007 08:06 Go to next message
sundaravel_n
Messages: 6
Registered: October 2007
Junior Member
Hi,

we need to create a Dynamic columns as per the user input. so we have used the EXECUTE IMMEDIATE statements, but it not return any values after execute the select statements...

we have used pipelined function to get result as a table, if we use the EXECUTE IMMEDIATE statement, we are not able to get the results and we are not able to put the result of the query into any other table.

help us to get the solution. If you need some more info, please send a mail to sundaravel_n@yahoo.co.in

Thanks


Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277533 is a reply to message #277530] Tue, 30 October 2007 08:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've got a better idea - if we need more info, we'll just ask you for it here - that way everyone can see.

What exactly do you mean by a 'Dynamic Column'?

Can you show us (via a cut and paste from SQL*Plus) what you are doing, and also show us the code that isn't working.
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277559 is a reply to message #277530] Tue, 30 October 2007 09:47 Go to previous messageGo to next message
sundaravel_n
Messages: 6
Registered: October 2007
Junior Member
Thanks for replying,

We need to create output columns as per the user input, example, if input is 10, we will execute one select statement it will give standard 5 columns and we need to append 10 columns and its value with the standard columns, totally 15 columns and its values.

if user input is 8, total number of columns is [5+8] = 13 columns.

if user input is 6, total number of columns is [5+6] = 11 columns.

To achieve this we have tried to construct a query as a string,

for cr in 1.. (Total no of dynamic columns say 6){

select (standard 5 columns), (constant + cr)column 6, (constant + cr)column 7,(constant + cr)column 8,(constant + cr)column 9,(constant + cr)column 10,(constant + cr)column 11 from some_table.

}

we have constructed a query and execute this string using
the EXECUTE IMMEDIATE sttement, but it wont give any output column and we got the message as query executed successfully.

but we need a output columns with result as like a normal select statement...

Try 2:

we have construct one table function and call this table function using the EXECUTE IMMEDIATE statement, this time also we are not able to get any output column.

Help us to get the output columns. Please let me know if you are not able to understand the question.
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277563 is a reply to message #277559] Tue, 30 October 2007 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always fetch all the columns but return only the asked ones there you don't need dynamic query just static one.

Regards
Michel
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277565 is a reply to message #277559] Tue, 30 October 2007 10:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How are you trying to pass the output back from this query?
As a Ref cursor, or as a table of values, or what?

Also, what values are you going to pass back in the additional columns?
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277566 is a reply to message #277530] Tue, 30 October 2007 10:20 Go to previous messageGo to next message
sundaravel_n
Messages: 6
Registered: October 2007
Junior Member
Hi Michael,

AS per suggestion, we need to retreieve all the max columns 15[as per our example] and we need to publish columns as per the user input.

Then please let me know, how we restrict the output columns each time as per user input,

if the user input is 9 then no need to display the column 6, 7, and 8.

only 5 standard columns and column 9 and 10, totally 7 columns only.

Thanks
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277567 is a reply to message #277530] Tue, 30 October 2007 10:33 Go to previous messageGo to next message
sundaravel_n
Messages: 6
Registered: October 2007
Junior Member
Hi,

Last time, we called one table function it will give 5 columns and its values, it expects some input parameter also, the result values of the table function displyed in JSP. Now the user added one more input, as per this input value we need to create a columns and append with the existing one, values for the new column is calcululated from the existing column value.

so we need a solution to get the output coloumn as per the user input. we know how to get the values for the new columns but we dont know, how to create a dynamic column.

JSP expects the columns with the values and the query copied in the JSP, each time query is called by JSP with the input parameters.

Thanks
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277569 is a reply to message #277567] Tue, 30 October 2007 10:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How are you trying to pass the output back from this query?
As a Ref cursor, or as a table of values, or what?


I think it's probably best at this point if you show us some actual code. Your attempts to explain what your code does are only marginaly successfull.

If the code is too large to post, can you at lease post the interface (is the in, out and return parameters and their data types).
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277573 is a reply to message #277566] Tue, 30 October 2007 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom raises the main point, now post the function prototype.

Regards
Michel
Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277582 is a reply to message #277530] Tue, 30 October 2007 12:05 Go to previous messageGo to next message
sundaravel_n
Messages: 6
Registered: October 2007
Junior Member
select
POOL "IP Pool",
PURPOSE "Purpose",
REGION "Region",
NETWORK "Network",
TOC "TOC",
PKG_SPRINT_REPORTS.color( USED_COLOR, USED ) "Used %",
PKG_SPRINT_REPORTS.color( CIDR29_COLOR, CIDR29 ) "/29",
PKG_SPRINT_REPORTS.color( CIDR28_COLOR, CIDR28 ) "/28",
PKG_SPRINT_REPORTS.color( CIDR27_COLOR, CIDR27 ) "/27",
PKG_SPRINT_REPORTS.color( CIDR26_COLOR, CIDR26 ) "/26",
PKG_SPRINT_REPORTS.color( CIDR25_COLOR, CIDR25 ) "/25",
PKG_SPRINT_REPORTS.color( CIDR24_COLOR, CIDR24 ) "/24",
PKG_SPRINT_REPORTS.color( CIDR23_COLOR, CIDR23 ) "/23",
PKG_SPRINT_REPORTS.color( CIDR22_COLOR, CIDR22 ) "/22",
POOL_ID,
TOC_ID
from table( PKG_SPRINT_REPORTS.REPORT_6042660623013579108(
#IP Pool#, #TOC#, #Network#, #Purpose#, #Region# ))

now one new field also added in the input parameter as CIDR, if CIDR is 11 we need to display the available CIDR values for 11, 12, 13.....29. already 22 to 29 is there we need to append the extra column as per the user CIDR input.

If CIDR is 20 then we need to display cidr 20, cidr 21 and the existing column also.

if you need implementation code, i will send it to you.



Re: Try to create a Dynamic column using EXECUTE IMMEDIATE [message #277587 is a reply to message #277582] Tue, 30 October 2007 13:00 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I assume the select is in the JSP code, so select all columns and build the page with the useful ones.

Regards
Michel

Previous Topic: Remote Pipeline
Next Topic: data is not populating through dblink
Goto Forum:
  


Current Time: Fri Dec 09 11:40:24 CST 2016

Total time taken to generate the page: 0.29182 seconds