java.sql.SQLException: java.sql.SQLException: ORA-00904: "T1"."VERSION_DELETED": invalid identifier

2,271 views
Skip to first unread message

NewBie

unread,
Aug 21, 2012, 12:28:08 AM8/21/12
to atg_...@googlegroups.com
Hi,

I am trying to trouble shoot an existing CA installation which had issues.

Now I was able to bring up BCC.

But now  when I try to navigate to "Manage Commerce Assets".. the tree listing is not coming up. Instead it throws repository  error in the back-end as below.

Verified the db -- I desc dcs_catalog to see the "VERSION_DELETED" column there and it exists.

Could some one throw some lights on how to debug further.

It seems to me as configuration missing b/w (versioned or non-versioned) catalog.

Is there any document which I can refer to for doing these .. 

Note this was an exisiting system, which some one previously set-up. 


Warm Regards
KdM

My version : ATG2009.1 / Linux envr


    ... 135 more
2012-08-18 15:43:54,152 ERROR [ContainerBase] Servlet.service() for servlet jsp threw exception
java.sql.SQLException: java.sql.SQLException: ORA-00904: "T1"."VERSION_DELETED": invalid identifier

[++SQLQuery++]
 SELECT t1.catalog_id,t1.asset_version
   FROM dcs_catalog t1
  WHERE (((t1.workspace_id = ?)
    AND (t1.version_editable = ?))
    AND (t1.version_deleted = ?)
    AND NOT EXISTS (SELECT * FROM dcs_ctlg_anc_cats WHERE catalog_id = t1.catalog_id AND asset_version = t1.asset_version))
-- Parameters --
p[1] = {pd: workspace_id} 44305 (java.lang.String)
p[2] = {pd: version_editable} true (java.lang.Boolean)
p[3] = {pd: version_deleted} false (java.lang.Boolean)
[--SQLQuery--]

    at atg.adapter.gsa.SQLStatement.executeQuery(SQLStatement.java:613)




    ... 135 more
2012-08-18 15:43:54,152 ERROR [ContainerBase] Servlet.service() for servlet jsp threw exception
java.sql.SQLException: java.sql.SQLException: ORA-00904: "T1"."VERSION_DELETED": invalid identifier

[++SQLQuery++]
 SELECT t1.catalog_id,t1.asset_version
   FROM dcs_catalog t1
  WHERE (((t1.workspace_id = ?)
    AND (t1.version_editable = ?))
    AND (t1.version_deleted = ?)
    AND NOT EXISTS (SELECT * FROM dcs_ctlg_anc_cats WHERE catalog_id = t1.catalog_id AND asset_version = t1.asset_version))
-- Parameters --
p[1] = {pd: workspace_id} 44305 (java.lang.String)
p[2] = {pd: version_editable} true (java.lang.Boolean)
p[3] = {pd: version_deleted} false (java.lang.Boolean)
[--SQLQuery--]

    at atg.adapter.gsa.SQLStatement.executeQuery(SQLStatement.java:613)


desc dcs_ctlg_anc_cats
Name                           Null     Type                                                                                                                                                                                         
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSET_VERSION                  NOT NULL NUMBER(19)                                                                                                                                                                                   
CATALOG_ID                     NOT NULL VARCHAR2(40)                                                                                                                                                                                 
SEQUENCE_NUM                   NOT NULL NUMBER(10)                                                                                                                                                                                   
CATEGORY_ID                    NOT NULL VARCHAR2(40)                                                                                                                                                                                 

4 rows selected

desc dcs_catalog
Name                           Null     Type                                                                                                                                                                                         
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ASSET_VERSION                  NOT NULL NUMBER(19)                                                                                                                                                                                   
WORKSPACE_ID                   NOT NULL VARCHAR2(40)                                                                                                                                                                                 
BRANCH_ID                      NOT NULL VARCHAR2(40)                                                                                                                                                                                 
IS_HEAD                        NOT NULL NUMBER(1)                                                                                                                                                                                    
VERSION_DELETED                NOT NULL NUMBER(1)                                                                                                                                                                                    
VERSION_EDITABLE               NOT NULL NUMBER(1)                                                                                                                                                                                    
PRED_VERSION                            NUMBER(19)                                                                                                                                                                                   
CHECKIN_DATE                            DATE                                                                                                                                                                                         
CATALOG_ID                     NOT NULL VARCHAR2(40)                                                                                                                                                                                 
VERSION                        NOT NULL NUMBER                                                                                                                                                                                       
DISPLAY_NAME                            VARCHAR2(254)                                                                                                                                                                                
CREATION_DATE                           DATE                                                                                                                                                                                         
LAST_MOD_DATE                           DATE                                                                                                                                                                                         
MIGRATION_STATUS                        NUMBER(3)                                                                                                                                                                                    
MIGRATION_INDEX                         NUMBER(10)                                                                                                                                                                                   
ITEM_ACL                                VARCHAR2(1024)                                                                                                                                                                               




SOURCE EXCEPTION:
org.apache.jasper.JasperException: An exception occurred processing JSP page /tree/treeBranch.jsp at line 28


25:
26:   <%-- Get the contents of the node whose path is given by the "nodePath"
27:        request attribute. --%>
28:   <web-ui:getTreeChildren var="children"
29:                           tree="${param.treeComponent}"
30:                           nodeId="${requestScope.nodePath}"/>
31:   <c:if test="${empty children and empty requestScope.nodePath}" >





Diego Fernando Costa

unread,
Aug 21, 2012, 12:51:50 AM8/21/12
to atg_...@googlegroups.com

Have you confirmed that your ProductCatalog repository is pointing to your management/publishing schema? If it's pointing to your switching data source (production), it won't find that column.

Normally you have the data source property in your ProductCatalog pointing to a JTDataSource, which points to the management schema. Then you have a ProductCatalog_production pointing to your switching data source in the production schema.

--
--
You received this message because you are subscribed to the Google Groups "ATG_Tech" group.
To post to this group, send email to atg_...@googlegroups.com
To unsubscribe from this group, send email to atg_tech-u...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/atg_tech?hl=en
 
 

Kadinamkulam

unread,
Aug 21, 2012, 1:09:23 AM8/21/12
to atg_...@googlegroups.com
Hi,

management/publishing schema  -- Let me work on this, in the mean time here is the output from my current CA server.

Any more inputs highly appreciated, please help..

Warm Regards
KdM


Below are the details, as you said my ProductCatalog  is pointing to ProductCatalogSwitchingDataSource .


Service /atg/commerce/catalog/ProductCatalog/


versionManager    /atg/epub/version/VersionManagerService
$class    atg.adapter.version.VersionRepository
dataSource    /atg/commerce/jdbc/ProductCatalogSwitchingDataSource



http://localhost:8180/dyn/admin/nucleus/atg/commerce/catalog/ProductCatalog_production/
$class    atg.adapter.gsa.GSARepository
dataSource    /atg/commerce/jdbc/ProductCatalogSwitchingDataSource

###########   dyn/admin/nucleus/atg/commerce/catalog/ProductCatalog/?propertyName=serviceConfiguration


File /data/atg/ATG/ATG9.1/Client/CA/config/config.jar/atg/commerce/catalog/ProductCatalog.properties

#Properties File created by CIM
#Thu Apr 16 18:36:48 CDT 2009
dataSource=/atg/dynamo/service/jdbc/JTDataSource
cacheSwitchHot=false
#foreignRepositoryMapper=/atg/repository/ProductionRepositoryMapper

File /data/atg/ATG/ATG9.1/home/servers/myca/localconfig/GLOBAL.properties

cacheSwitchHot=false

File /data/atg/ATG/ATG9.1/home/servers/myca/localconfig/atg/commerce/catalog/ProductCatalog.properties

#Properties File created by CIM
#Tue Jul 06 08:35:09 MDT 2010
dataSource=/atg/commerce/jdbc/ProductCatalogSwitchingDataSource

############

Diego Fernando Costa

unread,
Aug 21, 2012, 11:04:37 AM8/21/12
to atg_...@googlegroups.com
It seems that the ProductCatalog.properties under /data/atg/ATG/ATG9.1/home/servers/myca/localconfig/atg/commerce/catalog/ is overriding the dataSource property and setting it to "/atg/commerce/jdbc/ProductCatalogSwitchingDataSource".

In your CA server, it should be "/atg/dynamo/service/jdbc/JTDataSource". Try commenting out that line and restarting the server.

When you open dyn/admin and navigate to ProductCatalog, you should see the dataSource pointing to
/atg/dynamo/service/jdbc/JTDataSource.

Kadinamkulam

unread,
Aug 21, 2012, 11:31:05 PM8/21/12
to atg_...@googlegroups.com
Hi ,


Now I made the changes and is correctly pointing
but still the error is there and also when i tried to list the sku - via dyn/Admin. it thwors the below error..

http://localhost:8180/dyn/admin/nucleus/atg/commerce/catalog/ProductCatalog/?action=seeitems&itemdesc=sku#seeItems

2012-08-18 22:58:30,971 ERROR [ContainerBase] Servlet.service() for servlet AdminProxyServlet threw exception
java.lang.IllegalArgumentException: querying transient versioned item type product is not supported.
    at atg.adapter.version.VersionView.getQueryBuilder(VersionView.java:79)
    at atg.repository.content.ContentRepositoryAdminServlet.printAllItems(ContentRepositoryAdminServlet.java:420)
    at atg.repository.content.ContentRepositoryAdminServlet.printAdmin(ContentRepositoryAdminServlet.java:274)
    at atg.adapter.gsa.GSAAdminServlet.printAdmin(GSAAdminServlet.java:100)
    at atg.nucleus.ServiceAdminServlet.printService(ServiceAdminServlet.java:285)
    at atg.nucleus.ServiceAdminServlet.service(ServiceAdminServlet.java:220)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at atg.nucleus.Nucleus.service(Nucleus.java:2990)
    at atg.nucleus.Nucleus.service(Nucleus.java:2893)
    at atg.servlet.pipeline.DispatcherPipelineServletImpl.service(DispatcherPipelineServletImpl.java:231)
    at atg.servlet.pipeline.PipelineableServletImpl.passRequest(PipelineableServletImpl.java:135)
    at atg.servlet.pipeline.ServletPathPipelineS




Caused by: java.lang.IllegalArgumentException: querying transient versioned item type catalogFolder is not supported.
    at atg.adapter.version.VersionView.getQueryBuilder(VersionView.java:79)
    at atg.repository.rql.RqlStatement.executeCountQuery(RqlStatement.java:322)
    at atg.web.tree.repository.RepositoryTreeDefinition.getRootCountByType(RepositoryTreeDefinition.java:3370)
    at atg.web.tree.BucketingTreeImpl.getChildrenOptimized(BucketingTreeImpl.java:290)
    at atg.web.tree.BucketingTreeImpl.getChildren(BucketingTreeImpl.java:156)
    at atg.web.tree.TreeState.getChildren(TreeState.java:73)
    at atg.webui.taglib.GetTreeChildrenTag.doStartTag(GetTreeChildrenTag.java:175)
    at org.apache.jsp.tree.treeBranch_jsp._jspx_meth_web_002dui_005fgetTreeChildren_005f0(treeBranch_jsp.java:450)
    at org.apache.jsp.tree.treeBranch_jsp._jspService(treeBranch_jsp.java:133)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:387)
    ... 122 more
2012-08-18 23:07:54,191 ERROR [ContainerBase] Servlet.service() for servlet jsp threw exception
java.lang.IllegalArgumentException: querying transient versioned item type catalogFolder is not supported.
    at atg.adapter.version.VersionView.getQueryBuilder(VersionView.java:79)
   

Caused by: java.lang.IllegalArgumentException: querying transient versioned item type catalogFolder is not supported.
    at atg.adapter.version.VersionView.getQueryBuilder(VersionView.java:79)
    at atg.repository.rql.RqlStatement.executeCountQuery(RqlStatement.java:322)
    at atg.web.tree.repository.RepositoryTreeDefinition.getRootCountByType(RepositoryTreeDefinition.java:3370)
    at atg.web.tree.BucketingTreeImpl.getChildrenOptimized(BucketingTreeImpl.java:290)
    at atg.web.tree.BucketingTreeImpl.getChildren(BucketingTreeImpl.java:156)
    at atg.web.tree.TreeState.getChildren(TreeState.java:73)
    at atg.webui.taglib.GetTreeChildrenTag.doStartTag(GetTreeChildrenTag.java:175)
    at org.apache.jsp.tree.treeBranch_jsp._jspx_meth_web_002dui_005fgetTreeChildren_005f0(treeBranch_jsp.java:450)
    at org.apache.jsp.tree.treeBranch_jsp._jspService(treeBranch_jsp.java:133)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:387)
    ... 122 more
2012-08-18 23:07:54,191 ERROR [ContainerBase] Servlet.service() for servlet jsp threw exception
java.lang.IllegalArgumentException: querying transient versioned item type catalogFolder is not supported.
    at atg.adapter.version.VersionView.getQueryBuilder(VersionView.java:79)

Diego Fernando Costa

unread,
Aug 22, 2012, 10:48:52 PM8/22/12
to atg_...@googlegroups.com
I haven't seem this error before.

Are you sure your catalog definition file is loading in the proper order? Can you list the items in your production server?

Maybe there are some issues in your classpath that is loading the modules in the wrong order... But it's just a thought.

Kadinamkulam

unread,
Aug 22, 2012, 11:15:53 PM8/22/12
to atg_...@googlegroups.com
No, thats the issue now

http://localhost:8180/dyn/admin/nucleus/atg/commerce/catalog/ProductCatalog/?action=seeitems&itemdesc=product#seeItems


When I try to list the items via above, it is throwing the previous error ...


2012-08-19 09:14:29,244 INFO  [ProductCatalog] DEBUG getCountOfAllAssets(category-info)
2012-08-19 09:14:31,925 ERROR [ContainerBase] Servlet.service() for servlet AdminProxyServlet threw exception

VJ

unread,
Aug 23, 2012, 10:57:21 AM8/23/12
to atg_...@googlegroups.com
Have you customized the customCatalog? If yes, can you list the items with OOTB items?

Kadinamkulam

unread,
Aug 24, 2012, 8:51:51 PM8/24/12
to atg_...@googlegroups.com
Note : This issue is fixed, It was wrong configuration + the arguments passed to run.sh whn we start the CA instance.
Reply all
Reply to author
Forward
0 new messages