1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

dynamic file structure

Discussion in 'SQL PL/SQL' started by diva_thilak, Apr 7, 2014.

  1. diva_thilak

    diva_thilak Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi Folks,



    I have the below two table



    TABLE_METADATA



    RPT_NME STRT_POS END_POS LEN FIELD_NME

    VKX100 1 10 10 POL_NUM

    VKX100 11 20 10 FRST_NAME

    VKX100 21 30 10 FILLER

    VKX100 31 40 10 LST_NAME

    VKX200 1 10 10 POL_NUM

    VKX200 11 20 20 ADDRESS



    VKX100

    ID POL_NUM FRST_NAME LAST_NAME ADDRESS

    1 54677889 MARY GORDON 125 MAIN STREET



    My requirement is to create a record dynamically based on the report name. The required fields for the report are stored in the metadata table and the data for the report is stored in the corresponding report table. The query i was looking for is to extract the only required field required for the table and do right pad or left pad with spaces baded on the length.



    Your inputs are highly appreciated.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    One of possible candidate solutions will be:

    1) to add field type if it is absent in meta data

    2) for reading the file you can use UTL_FILE or dbms_lob,or used external table

    http://www.orafaq.com/forum/t/135774/

    http://blog.mclaughlinsoftware.com/2012/03/05/oracle-csv-imports/

    http://www.java2s.com/Code/Oracle/S...eadfromafileandinsertintothelecturertable.htm

    http://www.experts-exchange.com/Database/Oracle/Q_24452983.html

    http://www.orafaq.com/node/848

    http://www.oracle-base.com/articles/9i/external-tables-9i.php

    3) you read out in the portions data from the file, you analyze and you save in a collection.
    the collection can be on the basis of the dynamic ANYDATA type.
    a) when processing a portion of the records use FORALL for insert
     
  3. diva_thilak

    diva_thilak Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thank you krasnoslobodtsev_si.

    The threads you have listed here explains the usage of flat file as the source. Since i have the expected layout in an oracle table and the corresponding data table in Oracle, I was looking for a way to retrieve the data from the table using listed columns from the metadata table.

    It is more of a dynamic select statement where the required columns are present in an different table.

    Thanks again for your help in advance.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    1)
    I simply looked at structure of meta data and decided that the STR_POS and END_POS fields are line items in csv the file....

    2) Example of generation of sql query

    Code (SQL):

    SET serveroutput ON
    --drop table rep_metadata;
    CREATE TABLE rep_metadata
    (
    RPT_NME varchar2(100),
    STRT_POS NUMBER,
    END_POS NUMBER,
    LEN NUMBER,
    FIELD_NME varchar2(30),
    datatype NUMBER
    );
    INSERT INTO rep_metadata
    SELECT 'VKX100' ,1, 10, 10, 'POL_NUM',0 FROM dual UNION ALL
    SELECT 'VKX100', 11 ,20, 10 ,'FRST_NAME',1 FROM dual UNION ALL
    SELECT 'VKX100', 21, 30, 10, 'FILLER',1 FROM dual UNION ALL
    SELECT 'VKX100', 31 ,40, 10 ,'LST_NAME',1 FROM dual UNION ALL
    SELECT 'VKX200', 1, 10 ,10 ,'POL_NUM',0 FROM dual UNION ALL
    SELECT 'VKX200', 11 ,20, 20, 'ADDRESS',1 FROM dual;
    commit;

    var l_sql varchar2(2000);

    DECLARE
    l_rep_name rep_metadata.rpt_nme%TYPE := 'VKX100';
    BEGIN

    SELECT
    'select '||chr(10)||
    listagg('substr('||CASE WHEN datatype = 0 THEN 'to_char('||FIELD_NME||')'
    ELSE FIELD_NME END
    ||',1,'||LEN||') '||FIELD_NME
    , ','||chr(10)
    )
    WITHIN GROUP (ORDER BY strt_pos )
    ||chr(10)
    ||' from '|| l_rep_name ||';'
    INTO
    :l_sql
    FROM rep_metadata
    WHERE rpt_nme = l_rep_name;
    END;
    /
    print l_sql
    --execute immediate l_sql;
    SQL>



    TABLE REP_METADATA dropped.
    TABLE REP_METADATA created.
    6 ROWS inserted.
    committed.
    anonymous block completed
    L_SQL
    --------------------------------------
    SELECT
    substr(to_char(POL_NUM),1,10) POL_NUM,
    substr(FRST_NAME,1,10) FRST_NAME,
    substr(FILLER,1,10) FILLER,
    substr(LST_NAME,1,10) LST_NAME
     FROM VKX100;


     
     
    diva_thilak likes this.
  5. diva_thilak

    diva_thilak Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thank you krasnoslobodtsev_si, you are the best.

    It works like a charm.
     
  6. diva_thilak

    diva_thilak Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Krasnolslobodtsev_si, Is there any other alternate function for listagg i could try for getting this query in lower version of oracle. The oracle version in my environment seems to be Personal Oracle Database 11g Release 11.1.0.7.0 - Production
    PL/SQL Release 11.1.0.7.0 - Production
     
  7. diva_thilak

    diva_thilak Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I tried using the below query, but not sure WM_CONCAT would order the field names based on start pos like you have specified in the listagg function.

    SELECT rpt_nme, wm_concat(field_nme) AS clause
    FROM rep_metadata
    GROUP BY rpt_nme
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    as possible version of the decision - yes.
    but :
    wm_concat is an undocumented function.
    from version 10.2.0.5 returns result as clob.
    in 12с this function isn't supported any more.

    lisagg works starting with version 11 and higher.
    if the decision working at the version oracle earlier 11g , then it is necessary to use xmlagg, for example.
     
    diva_thilak likes this.
  9. diva_thilak

    diva_thilak Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thank you Kran.

    I tried xmlagg and i am able to get the select generated but the last column in the output comes out with a comma.

    Code (SQL):
    SELECT  
    --rpt_nme,
    'select '||chr(10),
             RTRIM(
                XMLAGG (XMLELEMENT(field_nme, 'substr('||CASE WHEN datatype = 0 THEN 'to_char('||FIELD_NME||')'
    ELSE FIELD_NME END
    ||',1,'||LEN||') '||FIELD_NME||',') ORDER BY strt_pos).EXTRACT('//text()')
    ||chr(10)
    ||' from '|| 'VKX100' ||';'
             )
      --   INTO l_sql
        FROM rep_metadata
        WHERE rpt_nme= 'VKX100'
    Code (SQL):

    'SELECT'||CHR(10) RTRIM(XMLAGG(XMLELEMENT(FIELD_NME,'SUBSTR('||CASEWHENDATATYPE=0THEN'TO_CHAR('||FIELD_NME||')'ELSEFIELD_NMEEND||',1,'||LEN||')'||FIELD_NME||',')ORDERBYSTRT_POS).EXTRACT('//TEXT()')||CHR(10)||'FROM'||'VKX100'||';')--INTOL_SQL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
    ----------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    SELECT            substr(to_char(POL_NUM),1,10) POL_NUM,substr(FRST_NAME,1,10) FRST_NAME,substr(FILLER,1,10) FILLER,substr(LST_NAME,1,10) LST_NAME,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                       FROM VKX100;  
     
    Is there a way i could avoid this ??

    Also when i get this in a procedure like below
    Code (SQL):


    CREATE OR REPLACE PROCEDURE TestProc
    AS
    l_sql varchar2(2000);

    l_rep_name rep_metadata.rpt_nme%TYPE := 'VKX100';

    BEGIN

    SELECT  
    --rpt_nme,
    'select '||chr(10),
             RTRIM(
                XMLAGG (XMLELEMENT(field_nme, 'substr('||CASE WHEN datatype = 0 THEN 'to_char('||FIELD_NME||')'
    ELSE FIELD_NME END
    ||',1,'||LEN||') '||FIELD_NME||',') ORDER BY strt_pos).EXTRACT('//text()')
    ||chr(10)
    ||' from '|| 'VKX100' ||';'
             )
     INTO l_sql
        FROM rep_metadata
        WHERE rpt_nme= l_rep_name;
    END;
    /
     
    I get the below errors in the procedure

    Warning: execution completed with warning
    PROCEDURE TestProc Compiled.
    20/5 PL/SQL: ORA-00947: not enough values
    9/1 PL/SQL: SQL Statement ignored
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Code (SQL):


    CREATE OR REPLACE PROCEDURE TestProc
    AS
    l_sql varchar2(2000);
     
    l_rep_name rep_metadata.rpt_nme%TYPE := 'VKX100';
     
    BEGIN
     
    SELECT  
    --rpt_nme,
    'select '||chr(10)  
                ||  RTRIM(
                XMLAGG (XMLELEMENT(field_nme, 'substr('||CASE WHEN datatype = 0 THEN 'to_char('||FIELD_NME||')'
    ELSE FIELD_NME END
    ||',1,'||LEN||') '||FIELD_NME||',') ORDER BY strt_pos).EXTRACT('//text()')
    ||chr(10)
    ||' from '|| 'VKX100' ||';'
             )
     INTO l_sql
        FROM rep_metadata
        WHERE rpt_nme= l_rep_name;
    END;
     
     
    diva_thilak likes this.
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Code (SQL):


    CREATE OR REPLACE PROCEDURE TestProc
    AS
    l_sql varchar2(2000);
     
    l_rep_name rep_metadata.rpt_nme%TYPE := 'VKX100';
     
    BEGIN
     
    SELECT  
    --rpt_nme,
    'select '||chr(10)  
             ||RTRIM(
                XMLAGG (XMLELEMENT(field_nme, 'substr('||CASE WHEN datatype = 0 THEN 'to_char('||FIELD_NME||')'
    ELSE FIELD_NME END
    ||',1,'||LEN||') '||FIELD_NME||',') ORDER BY strt_pos).EXTRACT('//text()'),',')
    ||chr(10)
    ||' from '|| 'VKX100' ||';'
             
     INTO l_sql
        FROM rep_metadata
        WHERE rpt_nme= l_rep_name;
    dbms_output.put_line(l_sql);    
    END;
    /

    EXEC  TestProc

    SQL>

    SELECT
    substr(to_char(POL_NUM),1,10) POL_NUM,substr(FRST_NAME,1,10) FRST_NAME,substr(FILLER,1,10) FILLER,substr(LST_NAME,1,10) LST_NAME
     FROM VKX100;



     
     
  12. diva_thilak

    diva_thilak Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):

    SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
    substr(to_char(POL_NUM),1,10) POL_NUM,substr(FRST_NAME,1,10) FRST_NAME,substr(FILLER,1,10) FILLER,substr(LST_NAME,1,10) LST_NAME,                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
     FROM VKX100;
     
    How can i get the last (comma) get removed from the generated SQL.
     
  13. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Use RTRIM function : RTRIM(.... , ',')...

    example.

    Code (SQL):

    CREATE OR REPLACE PROCEDURE TestProc
    AS
    l_sql varchar2(2000);
     
    l_rep_name rep_metadata.rpt_nme%TYPE := 'VKX100';
     
    BEGIN
     
    SELECT  
    --rpt_nme,
    'select '||chr(10)  
             ||RTRIM(
                XMLAGG (XMLELEMENT(field_nme, 'substr('||CASE WHEN datatype = 0 THEN 'to_char('||FIELD_NME||')'
    ELSE FIELD_NME END
    ||',1,'||LEN||') '||FIELD_NME||',') ORDER BY strt_pos).EXTRACT('//text()'),',')
    ||chr(10)
    ||' from '|| 'VKX100' ||';'
     
     INTO l_sql
        FROM rep_metadata
        WHERE rpt_nme= l_rep_name;
    dbms_output.put_line(l_sql);    
    END;
    /
     
    EXEC  TestProc
     
    SQL>
     
    SELECT
    substr(to_char(POL_NUM),1,10) POL_NUM,substr(FRST_NAME,1,10) FRST_NAME,substr(FILLER,1,10) FILLER,substr(LST_NAME,1,10) LST_NAME
     FROM VKX100;

     
     
  14. artistic21

    artistic21 Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Hi, can you please help me with a stored procedure in plsql? i have a table employees filled with data. Employees table has attributes: first_name,last_name, email,manager_id,dept_id. I want to insert a new employee, but i have to control the manager_id attribut. If manager have more than 4 employees in supervision,this new employee wuoldn't be added. (so w have to print the message<dbms_output.put_line('the manager'||nameOfManager|| 'have maximum number of employees')>, else the new employee should be added<dbms_output.put_line(nameOfManager|| 'have a new employee')>