ORACLE中的SQL语句语法错误(使用sql developer将sqlserver转换到oracle).

Question

大家好.在使用sql developer将sqlserver转换到oracle,脚本执行时候出现了一些莫名的错误，因为对oracle语法不熟悉，找不出来错误的原因，希望大家忙吧谢谢1.CREATE OR REPLACE VIEW vDropdownPages
AS
   SELECT DISTINCT Application.ApplicationName,
                   PagePermission.Page,
                   Treeview.Caption NameOnTreeView,
                   PagePermission.LibraryID,
                   PagePermission.Page || '_' || PagePermission.LibraryID || '_' || CAST(Page.AppID AS NVARCHAR2) PageAppID,
                   Application.AppID
     FROM PagePermission
            JOIN Page
             ON PagePermission.Page = Page.Page
            AND PagePermission.LibraryID = Page.LibraryID
            JOIN Application
             ON Page.AppID = Application.AppID
            LEFT JOIN Treeview
             ON Page.LinkID = Treeview.LinkID
      WHERE ( PagePermission.Valid = 1 )
     ORDER BY Application.ApplicationName,
              PagePermission.LibraryID,
              PagePermission.Page;提示红字那一行有错
SQL 错误: ORA-00906: 缺失左括号
00906. 00000 -  "missing left parenthesis"

Answer 1

CAST() 
oracle应该没有这个函数，可能是这里的问题

Answer 2

2.
CREATE OR REPLACE VIEW vGroupPermissions
AS
   SELECT GrantorUserGroup.GroupID Grantor,
          Organization.Name GranteeOrgName,
          GranteeUserGroup.GroupID Grantee,
          (CASE GroupPermission.Permission
                                          WHEN 1 THEN 'Read'
                                          WHEN 2 THEN 'Insert'
                                          WHEN 4 THEN 'Update'
                                          WHEN 8 THEN 'Delete'
                                          WHEN 3 THEN 'Read+Insert'
                                          WHEN 5 THEN 'Read+Update'
                                          WHEN 9 THEN 'Read+Delete'
                                          WHEN 6 THEN 'Insert+Update'
                                          WHEN 10 THEN 'Insert+Delete'
                                          WHEN 12 THEN 'Update+Delete'
                                          WHEN 7 THEN 'Read+Insert+Update'
                                          WHEN 14 THEN 'Insert+Update+Delete'
                                          WHEN 11 THEN 'Read+Insert+Delete'
                                          WHEN 13 THEN 'Read+Update+Delete'
                                          WHEN 15 THEN 'Read+Insert+Update+Delete'   END) PermissionSettings,
          (CASE
                WHEN (UTL_RAW.BIT_AND(GroupPermission.Permission,CAST_FROM_NUMBER(1))) = 1 THEN 1
          ELSE 0
             END) READ,
          (CASE
                WHEN (UTL_RAW.BIT_AND(GroupPermission.Permission,CAST_FROM_NUMBER(2))) = 2 THEN 1
          ELSE 0
             END) INSERT,      注释：这行error
          (CASE
                WHEN (UTL_RAW.BIT_AND(GroupPermission.Permission,CAST_FROM_NUMBER(4))) = 4 THEN 1
          ELSE 0
             END) UPDATE,
          (CASE
                WHEN (UTL_RAW.BIT_AND(GroupPermission.Permission,CAST_FROM_NUMBER(8))) = 8 THEN 1
          ELSE 0
             END) DELETE,
          GroupPermission.PermissionID,
          GranteeUserGroup.Admin IsGranteeAdminGroup,
          GroupPermission.Grantor GrantorID,
          GroupPermission.Grantee GranteeID,
          Organization.OrgID GranteeOrgID,
          GrantorUserGroup.OrgID
     FROM GroupPermission
            JOIN UserGroup GrantorUserGroup
             ON GroupPermission.Grantor = GrantorUserGroup.ResourceGroupID
            JOIN UserGroup GranteeUserGroup
             ON GroupPermission.Grantee = GranteeUserGroup.ResourceGroupID
            JOIN Organization
             ON GranteeUserGroup.OrgID = Organization.OrgID
            JOIN OrgCode
             ON Organization.OrgID = OrgCode.OrgID
      WHERE ( GroupPermission.Grantor <> GroupPermission.Grantee )
              AND ( GrantorUserGroup.Valid = 1 )
              OR ( GroupPermission.Grantor <> GroupPermission.Grantee )
              AND ( GranteeUserGroup.Valid = 1 )
              OR ( GroupPermission.Grantor <> GroupPermission.Grantee )
              AND ( GrantorUserGroup.Valid = 0 )
              AND ( GrantorUserGroup.Grantor = 1 )
              OR ( GroupPermission.Grantor <> GroupPermission.Grantee )
              AND ( GrantorUserGroup.Grantee = 1 )
              AND ( GrantorUserGroup.Admin = 1 )
              OR ( GroupPermission.Grantor <> GroupPermission.Grantee )
              AND ( GranteeUserGroup.Valid = 0 )
              AND ( GranteeUserGroup.Grantor = 1 )
              OR ( GroupPermission.Grantor <> GroupPermission.Grantee )
              AND ( GranteeUserGroup.Grantee = 1 )
              AND ( GranteeUserGroup.Admin = 1 )
错误报告:
SQL 错误: ORA-00923: 未找到要求的 FROM 关键字
00923. 00000 -  "FROM keyword not found where expected"

Answer 3

CAST(Page.AppID AS NVARCHAR2(200)) 
给NVARCHAR2订个长度就好了

Answer 4

3
CREATE OR REPLACE VIEW vUserPermission_ListGroupIDs
AS
   SELECT GroupID,
          ResourceGroupID,
          OrgID
     FROM ( SELECT 'Select Group' GroupID,       注释错误/error
                   '-1' ResourceGroupID,
                   '-1' OrgID
              FROM DUAL
            UNION
            SELECT DISTINCT GroupID,
                            ResourceGroupID,
                            OrgID
            FROM UserGroup
               WHERE ( NVL(Tag, '') <> GroupID )
                       AND ( Program = 0 )
                       AND ( Valid = 1 ) ) P
     ORDER BY OrgID
命令出错, 行: 4,021 列: 19
错误报告:
SQL 错误: ORA-12704: 字符集不匹配
12704. 00000 -  "character set mismatch"

Answer 5

CAST(Page.AppID AS NVARCHAR2)改成to_char(Page.AppID)

Answer 6

4.CREATE OR REPLACE VIEW vPermission_OrgList
AS
   SELECT '-1' OrgID,             //error
          'Select Organization' NAME,
          '-1' UserID
     FROM DUAL
   UNION
   SELECT DISTINCT o.OrgID,
                   o.NAME,
                   oa.UserID
     FROM Organization o
            JOIN OrgAdmin oa
             ON o.OrgID = oa.OrgID
            JOIN OrgCode oc
             ON o.OrgID = oc.OrgID错误报告:
SQL 错误: ORA-01790: 表达式必须具有与对应表达式相同的数据类型
01790. 00000 -  "expression must have same datatype as corresponding expression"

Answer 7

5.BEGIN
   -- Insert into our temp table
   INSERT INTO tt_PageIndex
     ( ItemId )
     SELECT PerUser.Id
       FROM aspnet_PersonalizationPerUser PerUser,
            aspnet_Users Users,
            aspnet_Paths Paths
        WHERE Paths.ApplicationId = v_ApplicationId
                AND PerUser.UserId = Users.UserId
                AND PerUser.PathId = Paths.PathId
                AND ( v_Path IS NULL
                OR Paths.LoweredPath LIKE LOWER(v_Path) )
                AND ( v_UserName IS NULL
                OR Users.LoweredUserName LIKE LOWER(v_UserName) )
                AND ( v_InactiveSinceDate IS NULL
                OR Users.LastActivityDate <= v_InactiveSinceDate )
       ORDER BY Paths.PATH ASC,
                Users.UserName ASC;   v_TotalRecords := SQL%ROWCOUNT;   SELECT Paths.PATH,
          PerUser.LastUpdatedDate,
          LENGTHB(PerUser.PageSettings),
          Users.UserName,
          Users.LastActivityDate
     FROM aspnet_PersonalizationPerUser PerUser,
          aspnet_Users Users,
          aspnet_Paths Paths,
          tt_PageIndex PageIndex
      WHERE PerUser.Id = PageIndex.ItemId
              AND PerUser.UserId = Users.UserId
              AND PerUser.PathId = Paths.PathId
              AND PageIndex.IndexId >= v_PageLowerBound
              AND PageIndex.IndexId <= v_PageUpperBound
     ORDER BY Paths.PATH ASC,
              Users.UserName ASC;END;
BEGIN
   RETURN;
   /*Limitation:Syntax Not Recognized:null */
END;
错误报告:
ORA-06550: 第 41 行, 第 1 列:  //估计错误行报的不准确
PLS-00103: 出现符号 "BEGIN"
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.没有了，希望大家帮忙，谢谢.

Answer 8

INSERT 是关键字,在ORACLE 不能当别名用,要么"INSERT" 这样用

Answer 9

这个是sql server中的函数吧，用to_char(Page.AppID)试试

Answer 10

3.那个好像是union上边的和下面sql对应字段类型不一样吧

Answer 11

是说把我红字那个改成 "insert" 或者别的吗？ 这样影响程序吗？

Answer 12

4，o.OrgID的类型要和前面'-1' OrgID一致吧

Answer 13

5.应该是留下开始的begin和最后的end，把中间的那个end和begin去了就好了

Answer 14

因为是从sqlserver转换到了oracle ，所以很多类型都变了.我想问下他这句什么意思呢？
select '-1'  orgid,          '-1' orgin 这种写法代表什么意思呢？    在oracle中 '-1' 代表字符型吗？，如果是的话后边这个orgid又怎么呢？，如果是个2个字段怎么不用 ，号呢? 
SELECT '-1' OrgID,            //error 
          'Select Organization' NAME, 
          '-1' UserID 
    FROM DUAL 

Answer 15

select '-1' orgid from dual;
你会得到这个结果：orgid // 列名
-1 // 该列的值，永远是'-1'，而且是个字符型的

Answer 16

你这边应该是类型不对，比如你的第4段SQL
4. CREATE OR REPLACE VIEW vPermission_OrgList 
AS 
  SELECT '-1' OrgID,            -- 这边你指定了类型为字符型的
          'Select Organization' NAME, 
          '-1' UserID 
    FROM DUAL 
  UNION 
  SELECT DISTINCT o.OrgID, --如果这里你的字段类型是number，就和上面的'-1' OrgID冲突，所以就会出错
                  o.NAME, 
                  oa.UserID 
    FROM Organization o 
            JOIN OrgAdmin oa 
            ON o.OrgID = oa.OrgID 
            JOIN OrgCode oc 
            ON o.OrgID = oc.OrgID 

Answer 17

恩，我去试试去，谢谢您. 跟sqlserver差别太大了.