9 Replies Latest reply: Jan 11, 2010 6:30 AM by 525801 RSS

    View Object On Ref Cursor

    727171
      Hello,

      I am using ADF Business Components 11g.

      I have a function that returns a weak type ref cursor. Number of columns are not known until the function is run. Is there a way to create a view object programmatically on this ref cursor?

      Edited by: Deniz Gulmez on 10.Oca.2010 23:40
        • 1. Re: View Object On Ref Cursor
          Timo Hahn
          I guess so.
          A good starting point is reading '39.8 Using Programmatic View Objects for Alternative Data Sources' in the Fusion Developer's Guide for Oracle ADF. What you need to do is to build the VO after you know the number of columns and their type.
          Still it will be a lot of work.

          Timo
          • 2. Re: View Object On Ref Cursor
            727171
            Hello Timo, thanks a lot for your help.

            I am now applying your suggestion but i am stuck at 1 point. I think if i can manage to iterate thru the columns of a ResultSet (java.sql.ResultSet), I can build the VO in overriden createRowFromResultSet method.

            Do you know a way to get iterator for columns of a ResultSet?
            • 3. Re: View Object On Ref Cursor
              John Stegeman
              call getMetaData() on it...
              • 4. Re: View Object On Ref Cursor
                727171
                Thanks.
                • 5. Re: View Object On Ref Cursor
                  727171
                  Sorry for reviving the topic but i have some other questions.

                  Since I don't have an initial query for view object, i think i also need to create attributes for view object before populating them. Documentation for the sample in Fusion Order Demo project is made by an initial SQL query and View Object attributes.

                  What is the way to programmatically create view object attrbiutes?
                  • 6. Re: View Object On Ref Cursor
                    727171
                    ..

                    Edited by: Deniz Gulmez on 11.Oca.2010 04:00
                    • 7. Re: View Object On Ref Cursor
                      Timo Hahn
                      you can try with
                      public AttributeDef addDynamicAttribute(java.lang.String attrName)
                      // or
                      public AttributeDef addDynamicAttributeWithType(java.lang.String attrName,
                                                                      java.lang.String javaTypeName,
                                                                      java.lang.String transientExpression)
                      Timo
                      • 8. Re: View Object On Ref Cursor
                        525801
                        Hi,

                        Don't know where I got this piece of code but it works for me..
                           /*
                             * Adds a SQL calculated view attribute of type oracle.jbo.domain.Number
                             * having a given precision/scale. An EO-mapped attribute would inherit
                             * this datatype information from the underlying EO attribute, but for
                             * a SQL-devired attribute, we need to set that information ourselves.
                             */
                        
                           protected void addSQLDerivedNumberViewAttr(ViewDefImpl v,
                                                                      String viewAttrName,
                                                                      String SQLExpression,
                                                                      int precision,
                                                                      int scale,
                                                                      boolean key) {
                              addSQLDerivedViewAttr(v,
                                                    viewAttrName,
                                                    SQLExpression,
                                                    Number.class,
                                                    precision,
                                                    scale,
                                                    key);
                           }
                        
                           /*
                             * Adds a SQL calculated view attribute of type oracle.jbo.domain.Date
                             */
                        
                           protected void addSQLDerivedDateViewAttr(ViewDefImpl v,
                                                                    String viewAttrName,
                                                                    String SQLExpression,
                                                                    boolean key) {
                              addSQLDerivedViewAttr(v,
                                                    viewAttrName,
                                                    SQLExpression,
                                                    DateDomain.class,
                                                    0,
                                                    0,
                                                    key);
                           }
                        
                           /**
                            * Adds a SQL calculated view attribute of type String (corresponding
                            * to database VARCHAR2 type), and sets its maximum length.
                            */
                           protected void addSQLDerivedVarcharViewAttr(ViewDefImpl v,
                                                                       String viewAttrName,
                                                                       String SQLExpression,
                                                                       int maxLength,
                                                                       boolean key) {
                              addSQLDerivedViewAttr(v,
                                                    viewAttrName,
                                                    SQLExpression,
                                                    String.class,
                                                    maxLength,
                                                    0,
                                                    key);
                           }
                        
                           /**
                            * Adds a SQL calculated view attribute of a given type with given
                            * precision and scale (if applicable, otherwise pass 0).
                            */
                           private void addSQLDerivedViewAttr(ViewDefImpl v,
                                                              String viewAttrName,
                                                              String SQLExpression,
                                                              Class javaType,
                                                              int precision,
                                                              int scale,
                                                              boolean key) {
                              AttributeDefImpl attr;
                              attr = v.addViewAttribute(viewAttrName,
                                                        SQLExpression,
                                                        javaType);
                              attr.setPrimaryKey(key);
                              /*
                               * Determine the JDBC type id that should be used from the type map
                               * for this Java type, and set the SQL type Id of the new view attribute
                               */
                              int sqlTypeId = JboTypeMap.sqlTypeToSQLTypeId(JboTypeMap.attrTypeToSQLType(javaType.getName()));
                              attr.setSQLType(sqlTypeId);
                              /*
                               * If the column type is a character type, it's good for JDBC performance
                               * to set the maximum length of the string to avoid having the JDBC
                               * driver assume that it's 4000 long. This saves memory at the JDBC layer.
                               */
                              if (JboTypeMap.isCharType(sqlTypeId)) {
                                 attr.setPrecisionScale(precision,
                                                        0);
                              }
                              /*
                               * If a Number type, set its precision and scale
                               */
                              else if (JboTypeMap.isNumericType(sqlTypeId)) {
                                 attr.setPrecisionScale(precision,
                                                        scale);
                              }
                           }
                        • 9. Re: View Object On Ref Cursor
                          525801
                          I realized that this particular project is not yet migrated to 11g.

                          Above code is used in JDev 10g.