Kbase P4131: Generate Delta.sql utility does not assign a value to _file._for-owner (FOREIGN-OWNER)
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Verified
FACT(s) (Environment):
Oracle DataServer
Progress 7.x
Progress 8.x
Progress 9.0x
Progress 9.1A
Progress 9.1B
Progress 9.1C
SYMPTOM(s):
Generate Delta.sql utility does not assign a value to _file._for-owner
Generate Delta.sql utility does not assign a value to FOREIGN-OWNER
No value for _file._for-owner in schema holder for Oracle table
No value/keyword for FOREIGN-OWNER in delta.df file when adding an Oracle table
Using "Generate Delta.sql PROGRESS to ORACLE"/incremental utility
CAUSE:
Prior to 9.1D, the information for _file._for-owner was not available as there was no fill-in "Oracle Object Owner Name" on the "Delta.df to ORACLE Conversion" screen.
The delta sql utility did not ask the user to supply the name for the owner of the objects when they were created in Oracle. This could cause a potential problem in Oracle because the DataServer would try to access the objects using the userid of the connected user. If the objects were not owned by this user, data could not be found.
FIX:
Assign a value to the _file._for-owner field.
Listed below are different ways you can do this.
1. After generating the Delta.sql/.df files, edit the .df and add to the
ADD TABLE statement the following keyword
FOREIGN-OWNER "USER_NAME"
2. If the objects are owned by a single user, then you can manually assign a value to the field _for-owner in the metaschema table _File by running the following code:
DEFINE VARIABLE forowner AS CHARACTER NO-UNDO.
DEFINE VARIABLE db-name AS CHARACTER NO-UNDO.
/*File names of DataServer that do not begin oracle */
DEFINE VARIABLE sysobj AS CHARACTER INITIAL
"PROC-TEXT-BUFFER,SEND-SQL-STATEMENT,closeAllProcs,"
NO-UNDO.
ASSIGN forowner = /* Enter object owner name */
db-name = /*Enter Oracle database name */.
FIND FIRST _Db where _Db._Db-name = db-name
and _Db._Db-type = "ORACLE" NO-ERROR.
IF AVAILABLE _Db THEN DO:
FOR EACH _File of _Db:
IF _File._File-name BEGINS "oracle_" OR
LOOKUP(_File._File-name, sysobj) > 0 THEN NEXT.
IF _File._For-owner = ? OR
_File._For-owner = "" THEN
ASSIGN _File._For-owner = forowner.
END.
3. If the objects are owned by a multiple users, then you will need to modify the above code to prompt for the owner of each table before assigning a value for the file._for-owner field.
DEFINE VARIABLE db-name AS CHARACTER NO-UNDO.
/*File names of DataServer that do not begin oracle */
DEFINE VARIABLE sysobj AS CHARACTER INITIAL
"PROC-TEXT-BUFFER,SEND-SQL-STATEMENT,closeAllProcs,"
NO-UNDO.
ASSIGN db-name = /*Enter Oracle database name */.
FIND FIRST _Db where _Db._Db-name = db-name
and _Db._Db-type = "ORACLE" NO-ERROR.
IF AVAILABLE _Db THEN DO:
FOR EACH _File of _Db:
IF _File._File-name BEGINS "oracle_" OR
LOOKUP(_File._File-name, sysobj) > 0 THEN NEXT.
IF _File._For-owner = ? OR
_File._For-owner = "" THEN DO:
DISPLAY _file._file-name.
UPDATE _File._For-owner.
END.