Forum Stats

  • 3,734,280 Users
  • 2,246,936 Discussions
  • 7,857,218 Comments

Discussions

Oracle PL/SQL Procedure/function to dynamically create view with dynamic columns

Vinipanda
Vinipanda Member Posts: 103 Red Ribbon
edited Nov 24, 2020 3:00PM in SQL & PL/SQL

I had similar requirement earlier but now i have been given a clear approach that needs to be implemented.

I need to write a procedure(preferably)/function, which would on basis of app_id passed as parameter, change the column headers for corresponding values pivoted in the dynamic view.

P.S. Application at the end is Oracle apex from where the proc/function would be called.

The entire sample data and structure is available here:


https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=63c08123fbf2fb13de81df0bff360c0b


The table with column headers is DATA_HEADER.



The table with value is DATA_VALUE. 


The column headers and values need to be pivoted in order.


Both tables have app_id on common. So for app_id=1, labels from DATA_HEADER would be used against values in DATA_VALUE. However, the values change on basis of PID only, so they would remain constant, only headers would change per app id. 


When app_id is passed in proc/funct, the expected view should be:



So basically, the headers change for each app_id, and **the max number of column headers will be 20**. So the number or name shall vary as in the table DATA_HEADER.

The values are uniquely identified on the basis of pid.


The order of column headers would be as per the seq column in DATA_HEADER.

Similarly the order of values would be as per seq column in data value, so the sequence must be followed and pivoted accordingly.


Oracle Version: 12.1

Tagged:

Best Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    Accepted Answer

    After running that, you don't have to run the create view statement again. What Solomon has shown is the output given from the "create_view" procedure which displays the create view statement it is creating.

    Vinipanda

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond

    Hi,

    When app_id is passed in proc/funct, the expected view should be:


    Is that one view with 10 rows, of is it two views, each with 2 rows?

    If it's one view, you can get the results using GROUP SETS or UNION.

    If it's two views, you need dynamic SQL.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    There could be as many view based on app_id. When i say view, it would be the data on basis of app_id passed. So for app_id=1, it will pick headers from the data_header table and values from data_value table. Pivot and place them as a view.

    So at one time it would be one view only, based on the app id with different pid and values.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    This is sample data, so there are only 2 app ids. There could be multiple app ids.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond

    Hi,

    This is sample data, so there are only 2 app ids. There could be multiple app ids.

    I understand that it's sample data. I still don't understand if the output in this example is one view with 10 rows, or two possible views with two rows each.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon
    edited Nov 24, 2020 1:48PM

    Output is 2 possible view with 2 rows each, because the output would be pivoted.

    Each row is for each pid coming from data_value table.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    P.S. Application at the end is Oracle apex from where the proc/function would be called. Sorry for having missed this in original question.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond

    Hi,

    So you want to pass an app_id, and have the procedure create a view for that app_id. In this example, if you pass either 1 or 2 as the argument, the view created should have 2 rows. Is that right?

    Check that the results you posted (copied below) are correct. For app_id=1, do you really want pid=120 on both rows? If so, explain why. If the results below are wrong, post the correct results wanted from the given data.



  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    So you want to pass an app_id, and have the procedure create a view for that app_id. In this example, if you pass either 1 or 2 as the argument, the view created should have 2 rows. Is that right?

    Yes thats right.

    Sorry that was a typo.

    Foranyapp_id,all pids should be there.


    So,


  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    Does the view need to be dynamically named too? Otherwise you could have multiple users calling it with different app id's and the same view getting overwritten by the latter call.

    Then if you have a dynamically named view... how are you going to use that in your application?

    Once you get in to the realms of unknown view names and unknown column names, you start creating applications with unkown errors and unknown security concerns. You really need to be sure you know what you're doing if you go down this route.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Does the view need to be dynamically named too? Otherwise you could have multiple users calling it with different app id's and the same view getting overwritten by the latter call.

    No, view is just to represent data, so for every app id a new view is generated.

    The end application is oracle apex(report), so basically in apex when i call the proc: call proc(app_id=>1), it should use the column headers for app_id=1 with the values. and so on..

  • Stefan Jager
    Stefan Jager Member Posts: 1,749 Silver Trophy

    I'm starting to think there's a miscommunication here. Are you talking about a view in the database, or are you talking about the grid in the APEX frontend?

    Because what you are saying does not make sense if you are talking about a database view: then you are only doing this because somebody loves the word "dynamic" (suprised it doesn't have to be dynamic pl/sql code...)

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    The requirement is to generate view via procedure with dynamic column headers where the column headers change with app id.


    The procedure would later on be called in apex and app_id passed as parameter.

    So in apex the procedure can be used to display data as repot with changing column headers with app ids.

    Apex comes last so it does not hold priority. The proc will just be called there.

    The crux is column headers need to change dynamically, and for that this approach needs to be implemented.

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond


    That doesn't make sense. You say it doesn't need to be a dynamically names view but then you say a new view is generated for each app id... which means that it IS dynamically named.

    When you refer to "app id" are you meaning an apex application id? a session number (per user)? or something else. When do these views get generated? If you've got multiple users using the application, are they doing something within the application to trigger the creation of the view, and is that view specific to that user/session? If so, it would have to be dynamically named. How is your apex application then going to refer it's reporting to a view that it doesn't know the name of until runtime?

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    The apex application ids are already set up.

    Here in procedure, i just need to take the reference from data_header table and formulate such that column headers change per app id.

    So in application, whenever the user selects say application 1, the view would change with column headers linked with application 1.

    If you've got multiple users using the application, are they doing something within the application to trigger the creation of the view, and is that view specific to that user/session? If so, it would have to be dynamically named. How is your apex application then going to refer it's reporting to a view that it doesn't know the name of until runtime?

    The view is specific to session.

    Yes you're right. Sorry here i misunderstood your question and made wrong assumption. yes the view would need to be named.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon
    EXEC CREATE_VIEW(2)
    

    After this, why do i have to run the create view statement again? And if i have to run create view with hardcoded headers how did it make a difference ?

    Sorry but maybe i am perplexed a bit.

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond
    Accepted Answer

    After running that, you don't have to run the create view statement again. What Solomon has shown is the output given from the "create_view" procedure which displays the create view statement it is creating.

    Vinipanda
  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Got it, thanks!

  • cormaco
    cormaco Member Posts: 1,559 Bronze Crown

    Starting with Oracle 18 you could use a polymorphic function for this:

    https://oracle-base.com/articles/18c/polymorphic-table-functions-18c

    Vinipanda
  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Hi,

    I need to make some changes to the solution suggested by Solomon.

    The proc is working perfectly fine. Now i need to amend it such that, the way its creating view, similarly the select statement would also get generated dynamically inside the procedure,.i.e. a function returning sql query.

    The end requirement is to call this is interactive report in oracle apex, so by directly calling the procedure it would display the data directly.

    So basically, the select * from APP_&APP_ID_VW : this would also get dynamically created via proc after view is created.

    Please let me know if this is possible.

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    Sounds like you need a pipelined table function of some sort, but if the number of columns are dynamic it will be much harder.

    Even passing queries back, would normally be done with a ref cursor, though usually to a 3rd party application layer e.g. something written in Java or .NET, not Apex.

    All this "dynamic" requirement is just causing you more problems than any developer should have to deal with. Proper requirements gathering, design and implementation should mean you have fixed requirements and not have to try and make things dynamic. Each requirement would be coded specifically, and if needed, different screens in the application developed to deal with each of those requirements separately. Then you're effectively dealing with known input to the development to give a known output, and that is paramount to any solid testing process. The moment things become "dynamic" you lose control over the design and the testing, and leave it all open for bugs and issues everywhere.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Should i raise a separate thread for this?

  • BluShadow
    BluShadow Member, Moderator Posts: 40,989 Red Diamond

    If you got an answer to your original question then you should mark the appropriate response as the correct answer and yes, start a new thread for your new requirement (perhaps reference back to this thread so people can easily see the background of your related requirements)

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Thanks, marked correct answer and created new thread!

Sign In or Register to comment.