Forum Stats

  • 3,838,561 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

Best method to display a text which contains a static and a dynamic value.

Niki Dilip
Niki Dilip Member Posts: 13 Blue Ribbon

Hi,


My aim is to generate a file or print the contents on the screen which has both static and dynamic contents. The static content must be obtained from database(it can be a table or a file) and the dynamic content must be obtained from the values that the user inputs in page 1 and on submit , it will be obtained in the page 2.

Eg: This is a sample file and the file name is : sample.txt.


Here the sample.txt is dynamic content. Static content can be as big as 5 to 6 line then need to have dynamic content , then again different static content and then dynamic and this goes on. 


What will the best approach to do this using plsql PSP.

For static content is it best to store it in table or is it better to store it as a file with parameters as the place holder for the dynamic content. 


Thanks & Regards

Tagged:

Best Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond

    So, if the number of replacements are fixed then you could just opt for a simple "REPLACE" method, based on data you've stored in the database...

    create table static_txt(txt_id number, txt clob)
    /
    insert into static_txt values (1, to_clob('-- *****************************************************************************')||chr(10)||
                                      '--'||chr(10)||
                                      '-- IMPORTANT NOTE: Run this script without any modifications.'||chr(10)||
                                      '--                 Backup your data before running this script!'||chr(10)||
                                      '--'||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Script Name: [D|M|I|Q]-Ticketnr_STT-Nr_(Req-Id)_Client_[P|T|I][A|E|M]_shortname.sql'||chr(10)||
                                      '   DEFINE script_name        = ''{{scriptname}}'''||chr(10)||
                                      '-- Naming conventions:'||chr(10)||
                                      '-- TicketSystem         |TicketNo_Application_Client_ScriptEnvironment|ScriptType_Shortname.sql'||chr(10)||
                                      '-- [D|M|I|Q|PSIS|IN|TAX]|-TocjetNo_TB   _STT-No   (_Req-ID)         _DBI   _[P|T|I]          |[A|E|M]   _Shortname.sql'||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Ticketnumber Customer: This is the ticket number generated automatically'||chr(10)||
                                      '   DEFINE cust_ticket       = ''{{custticket}}'''||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Ticketnumber SMC: Mantis or HPQC Ticket Number - ''0'' if no SMC ticket exists'||chr(10)||
                                      '   DEFINE ticket            = ''{{ticket}}'''||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Informations (short description)'||chr(10)||
                                      '   DEFINE infotext          = ''{{txt}}'''||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)
                         )
    /
    create table dynamic_txt(dyn_id number, txt_id number, scriptname varchar2(50), custticket varchar2(20), ticket varchar2(20), infotext varchar2(100))
    /
    insert into dynamic_txt values (1, 1, 'test_script.sql', '12345', '12345', 'This is my sample file')
    /
    insert into dynamic_txt values (2, 1, 'test_script2.sql', '98765', '34567', 'Another sample file')
    /
    commit
    /
    select replace(replace(replace(replace(s.txt,'{{scriptname}}',d.scriptname),'{{custticket}}',d.custticket),'{{ticket}}',d.ticket),'{{txt}}',d.infotext) as output
    from   static_txt s
           join dynamic_txt d on (d.txt_id = s.txt_id) 
    /
    
    OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------
    -- *****************************************************************************
    --
    -- IMPORTANT NOTE: Run this script without any modifications.
    --                 Backup your data before running this script!
    --
    --------------------------------------------------------------------------------
    -- Script Name: [D|M|I|Q]-Ticketnr_STT-Nr_(Req-Id)_Client_[P|T|I][A|E|M]_shortname.sql
       DEFINE script_name        = 'test_script.sql'
    -- Naming conventions:
    -- TicketSystem         |TicketNo_Application_Client_ScriptEnvironment|ScriptType_Shortname.sql
    -- [D|M|I|Q|PSIS|IN|TAX]|-TocjetNo_TB   _STT-No   (_Req-ID)         _DBI   _[P|T|I]          |[A|E|M]   _Shortname.sql
    --------------------------------------------------------------------------------
    -- Ticketnumber Customer: This is the ticket number generated automatically
       DEFINE cust_ticket       = '12345'
    --------------------------------------------------------------------------------
    -- Ticketnumber SMC: Mantis or HPQC Ticket Number - '0' if no SMC ticket exists
       DEFINE ticket            = '12345'
    --------------------------------------------------------------------------------
    -- Informations (short description)
       DEFINE infotext          = 'This is my sample file'
    --------------------------------------------------------------------------------
    
    
    -- *****************************************************************************
    --
    -- IMPORTANT NOTE: Run this script without any modifications.
    --                 Backup your data before running this script!
    --
    --------------------------------------------------------------------------------
    -- Script Name: [D|M|I|Q]-Ticketnr_STT-Nr_(Req-Id)_Client_[P|T|I][A|E|M]_shortname.sql
       DEFINE script_name        = 'test_script2.sql'
    -- Naming conventions:
    -- TicketSystem         |TicketNo_Application_Client_ScriptEnvironment|ScriptType_Shortname.sql
    -- [D|M|I|Q|PSIS|IN|TAX]|-TocjetNo_TB   _STT-No   (_Req-ID)         _DBI   _[P|T|I]          |[A|E|M]   _Shortname.sql
    --------------------------------------------------------------------------------
    -- Ticketnumber Customer: This is the ticket number generated automatically
       DEFINE cust_ticket       = '98765'
    --------------------------------------------------------------------------------
    -- Ticketnumber SMC: Mantis or HPQC Ticket Number - '0' if no SMC ticket exists
       DEFINE ticket            = '34567'
    --------------------------------------------------------------------------------
    -- Informations (short description)
       DEFINE infotext          = 'Another sample file'
    --------------------------------------------------------------------------------
    
    
    

    (scuse any typos - I just knocked up the test data quickly 😀)

    The above would allow for different static texts to be stored and then the dynamic content would be related to particular static texts (if that's what you want), providing each static text requires the same dynamic content to be used in replacements. Otherwise you'll be looking for something a bit more flexible.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond
    Answer ✓

    HTP.Print is displaying content to a web page, so you need to use HTML equivalents. In that case you need to replace chr(10) characters in the text with <br/> to get line breaks. simple replace, or just store the original static text with that instead of chr(10), whatever suits you best.

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy

    To write to a file which resides on the database server use the facilities of the utl_file package.

    If you want to have something displayed on screen and you are using sqldeveloper or some other client tool, then the most simple way is to use a SQL select. A column in a select may contain static content and dynamic content, that is content in tables which may change in the course of time. If a single select won't do, then you may use a pipelined function and select * from table(my_pipelined_function(... function arguments...). I did use a pipelined function to generate SQL scripts that help migrating some configuration from a database to another.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond


    You haven't said what client tool you are using for "page 1", "page 2" etc. How things get "displayed" depends on the client tool.

    For storing data, it's always better to store data on the database itself rather than in external files or other such places. External files could get renamed or deleted by someone (DBA's, Server administrators etc.) who doesn't know what they are, and if it's stored in the database it not only protects the data, but also allows for it to be updated, if required, from a client tool/user of the application.

    Your requirement does sound very much like you just need a decent reporting tool though, one where the content is defined in the report with it being able to query the database for data that must be dynamically shown/generated etc.

    It really does depend on what you are trying to achieve overall though, so post some example data and show what is dynamic and how it would change under different circumstances.

  • Niki Dilip
    Niki Dilip Member Posts: 13 Blue Ribbon

    My main motto is script automation. Below is the sample script and the one highlighted in red is the dynamic content. I am using Apex. In page 1 i will get the user inputs and in the page 2 i must display the script generated which will contain both static and dynamic content. I will be using PSP for this. What would be the best approach to do this. How can i get the dynamic content to get displayed in the exact place. Should i have a placeholder value for dynamic content, while storing the static content ? Should i break the static content and store in the table or should store the whole static content in a single column?


  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond

    Will the "dynamic" content always be the same number of values to be replaced? Or is the number of replacements also dynamic?

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond

    You may want to take a look at the following community document (PDF) I created a while back:

    https://community.oracle.com/tech/developers/discussion/4417580/pl-sql-101-with-clause

    There's an example in there on p.14 "Multiple Replacement" which may be useful (depends on how large your data is I guess, but the principle may be useful)

  • Niki Dilip
    Niki Dilip Member Posts: 13 Blue Ribbon

    The number of dynamic content is going to be same. Just that the content will be based on the user inputs.

    Thank you for the document.. I will definitely have a look.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond

    So, if the number of replacements are fixed then you could just opt for a simple "REPLACE" method, based on data you've stored in the database...

    create table static_txt(txt_id number, txt clob)
    /
    insert into static_txt values (1, to_clob('-- *****************************************************************************')||chr(10)||
                                      '--'||chr(10)||
                                      '-- IMPORTANT NOTE: Run this script without any modifications.'||chr(10)||
                                      '--                 Backup your data before running this script!'||chr(10)||
                                      '--'||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Script Name: [D|M|I|Q]-Ticketnr_STT-Nr_(Req-Id)_Client_[P|T|I][A|E|M]_shortname.sql'||chr(10)||
                                      '   DEFINE script_name        = ''{{scriptname}}'''||chr(10)||
                                      '-- Naming conventions:'||chr(10)||
                                      '-- TicketSystem         |TicketNo_Application_Client_ScriptEnvironment|ScriptType_Shortname.sql'||chr(10)||
                                      '-- [D|M|I|Q|PSIS|IN|TAX]|-TocjetNo_TB   _STT-No   (_Req-ID)         _DBI   _[P|T|I]          |[A|E|M]   _Shortname.sql'||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Ticketnumber Customer: This is the ticket number generated automatically'||chr(10)||
                                      '   DEFINE cust_ticket       = ''{{custticket}}'''||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Ticketnumber SMC: Mantis or HPQC Ticket Number - ''0'' if no SMC ticket exists'||chr(10)||
                                      '   DEFINE ticket            = ''{{ticket}}'''||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)||
                                      '-- Informations (short description)'||chr(10)||
                                      '   DEFINE infotext          = ''{{txt}}'''||chr(10)||
                                      '--------------------------------------------------------------------------------'||chr(10)
                         )
    /
    create table dynamic_txt(dyn_id number, txt_id number, scriptname varchar2(50), custticket varchar2(20), ticket varchar2(20), infotext varchar2(100))
    /
    insert into dynamic_txt values (1, 1, 'test_script.sql', '12345', '12345', 'This is my sample file')
    /
    insert into dynamic_txt values (2, 1, 'test_script2.sql', '98765', '34567', 'Another sample file')
    /
    commit
    /
    select replace(replace(replace(replace(s.txt,'{{scriptname}}',d.scriptname),'{{custticket}}',d.custticket),'{{ticket}}',d.ticket),'{{txt}}',d.infotext) as output
    from   static_txt s
           join dynamic_txt d on (d.txt_id = s.txt_id) 
    /
    
    OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------
    -- *****************************************************************************
    --
    -- IMPORTANT NOTE: Run this script without any modifications.
    --                 Backup your data before running this script!
    --
    --------------------------------------------------------------------------------
    -- Script Name: [D|M|I|Q]-Ticketnr_STT-Nr_(Req-Id)_Client_[P|T|I][A|E|M]_shortname.sql
       DEFINE script_name        = 'test_script.sql'
    -- Naming conventions:
    -- TicketSystem         |TicketNo_Application_Client_ScriptEnvironment|ScriptType_Shortname.sql
    -- [D|M|I|Q|PSIS|IN|TAX]|-TocjetNo_TB   _STT-No   (_Req-ID)         _DBI   _[P|T|I]          |[A|E|M]   _Shortname.sql
    --------------------------------------------------------------------------------
    -- Ticketnumber Customer: This is the ticket number generated automatically
       DEFINE cust_ticket       = '12345'
    --------------------------------------------------------------------------------
    -- Ticketnumber SMC: Mantis or HPQC Ticket Number - '0' if no SMC ticket exists
       DEFINE ticket            = '12345'
    --------------------------------------------------------------------------------
    -- Informations (short description)
       DEFINE infotext          = 'This is my sample file'
    --------------------------------------------------------------------------------
    
    
    -- *****************************************************************************
    --
    -- IMPORTANT NOTE: Run this script without any modifications.
    --                 Backup your data before running this script!
    --
    --------------------------------------------------------------------------------
    -- Script Name: [D|M|I|Q]-Ticketnr_STT-Nr_(Req-Id)_Client_[P|T|I][A|E|M]_shortname.sql
       DEFINE script_name        = 'test_script2.sql'
    -- Naming conventions:
    -- TicketSystem         |TicketNo_Application_Client_ScriptEnvironment|ScriptType_Shortname.sql
    -- [D|M|I|Q|PSIS|IN|TAX]|-TocjetNo_TB   _STT-No   (_Req-ID)         _DBI   _[P|T|I]          |[A|E|M]   _Shortname.sql
    --------------------------------------------------------------------------------
    -- Ticketnumber Customer: This is the ticket number generated automatically
       DEFINE cust_ticket       = '98765'
    --------------------------------------------------------------------------------
    -- Ticketnumber SMC: Mantis or HPQC Ticket Number - '0' if no SMC ticket exists
       DEFINE ticket            = '34567'
    --------------------------------------------------------------------------------
    -- Informations (short description)
       DEFINE infotext          = 'Another sample file'
    --------------------------------------------------------------------------------
    
    
    

    (scuse any typos - I just knocked up the test data quickly 😀)

    The above would allow for different static texts to be stored and then the dynamic content would be related to particular static texts (if that's what you want), providing each static text requires the same dynamic content to be used in replacements. Otherwise you'll be looking for something a bit more flexible.

  • Niki Dilip
    Niki Dilip Member Posts: 13 Blue Ribbon

    Thanks a lot BluShadow . This helps.

    Just one more thing. Here even if the insert has been done along with chr(10), The text when I print it on the screen using HTP.Print, comes in a single line. The line break is not getting detected. How can this be handled?

    Thanks & Regards,

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond
    Answer ✓

    HTP.Print is displaying content to a web page, so you need to use HTML equivalents. In that case you need to replace chr(10) characters in the text with <br/> to get line breaks. simple replace, or just store the original static text with that instead of chr(10), whatever suits you best.