3 Replies Latest reply: Jul 3, 2014 10:11 AM by Sonali Singh RSS

    QVD Creation Script using Oracle Database

      Hi Everyone,

      Need Help..

      I am having issue while creating qvd using oracle database.

      I want to peek the column names from the inline table.

      The code is as below:


      List of tables which is cread in the previous tab:


      Table_Names:

      LOAD * inline [
      TableNames

      SCHEMA.TABLE1

      SCHEMA.TABLE2

      ] ;

      QVD CREATION SCRIPT:


      LET rowTP = NoOfRows('Table_Names');

      for i=1 to $(rowTP)


      Let vTableName    = FieldValue('TableNames',i);
      ColumnNames:
        LOAD
        * ;
        
        //select column_name from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';
        select * from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';
       
        LET vFields   =   peek('column_name');
        DROP Table ColumnNames;

      $(vTableName):
      LOAD
        *
        ;
        SELECT
        $(vFields)
        FROM
        $(vTableName)  $(vWhereCondition)
        ;
      STORE $(vTableName) INTO $(QVDFileDir)$(vTableName).QVD;


      DROP TABLE $(vTableName);

      next


      //Drop the inline Table
      Drop table Table_Names;


      Thanks,

      Sonali

        • Re: QVD Creation Script using Oracle Database
          pradeep t

          What is the error message you are getting exactly?

          • Re: QVD Creation Script using Oracle Database
            Jonathan Dienst

            Hi

             

            You need to concatenate the field names. I have added [ ] to handle possible spaces in the names:

             

            ...

              ColumnNames:

              SQL Select column_name from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';

             

              ConcColumns:

              LOAD '[' & Concat(column_name, '],[') & ']' As ConcatColumns

              Resident ColumnNames;

             

              let vFields = Peek('ConcatColumns');

              Drop Table ConcColumns;

             

              $(vTableName):

              SQL SELECT

                $(vFields)

              FROM $(vTableName)  $(vWhereCondition);

              ....

              Drop Table ColumnNames;

            Next

             

            Note that you must drop the ColumnNames table so that you don't land up with TABLE1 and TABLE2 fields in the second pass of the loop.

             

            HTH

            Jonathan

              • Re: QVD Creation Script using Oracle Database

                Hi Jon,

                 

                Thanks for reply,

                 

                Actuallyin my tables there is no field called Column_Name.

                That's why I am getting error and instead of

                       select column_name from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';

                I have used
                     
                       select * from ALL_TAB_COLUMNS where TABLE_NAME ='$(vTableName)';

                 

                But I need to have column names(or you can say Field names) of the tables that can be used further.