| DBMS_XMLSTORE not able to identify table name without schema name [message #163935] |
Mon, 20 March 2006 21:04 |
himang
Messages: 282
Registered: March 2005
Location: Bangalore
|
Senior Member |
|
|
I am using DBMS_XMLSTORE package for XML updation. The problem I am facing is that I have one schema, which is owner of the table and another schema on which the package is existing through which I am using the DBMS_XMLSTORE to update the table. Synonym pointing to the table is already created. When I use the same table name in the DBMS_XMLSTORE.newContext(‘Table_name’), it is giving error
SQL> DECLARE
2 updCtx DBMS_XMLSTORE.ctxType;
3 rows NUMBER;
4 sqlstr varchar2(100) := 'TCS_PARAMETERS';
5 xmlDoc CLOB :=
6 '<ROWSET>
7 <ROW>
8 <ID_PARAMETER>0</ID_PARAMETER>
9 <UPDATED_BY>XX</UPDATED_BY>
10 </ROW>
11 </ROWSET>';
12 BEGIN
13 updCtx := DBMS_XMLSTORE.newContext(sqlstr); -- get the context
14 DBMS_XMLSTORE.clearUpdateColumnList(updCtx); -- clear update settings
15 -- Specify that column employee_id is a "key" to identify the row to update.
16 DBMS_XMLSTORE.setKeyColumn(updCtx, 'ID_PARAMETER');
17 rows := DBMS_XMLSTORE.updateXML(updCtx, xmlDoc); -- update the table
18 DBMS_XMLSTORE.closeContext(updCtx); -- close the context
19 END;
20 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-04043: object TCS_PARAMETERS does not exist
The synonym is already created and I can SELECT, UPDATE, INSERT, DELETE directly without using schema name
SQL> show user
USER is "TCEG1_SOURCE"
SQL> select * from tcs_parameters;
ID_PARAMETER O O S S C D R MIN_ORDER_SIZE ROUT MAX_SHIP_BY_DATE E S U NBR_UNITS_IN_ASRS_FLAG
------------ - - - - - - - -------------- ---- ---------------- - - - ----------------------
SHIP_BY_DATE_FLAG PERCENT_IN_ASRS_FLAG GAP_IN_ASRS_FLAG MAX_NUM_PAST_KIT MAX_NUM_PAST_BUILD
----------------- -------------------- ---------------- ---------------- ------------------
MAX_NUM_PAST_BURN MAX_NUM_PAST_BOX MAX_NUM_PAST_SCANPT_1 MAX_NUM_SWAPS MAX_PERMITTED_FAIL_RATE T
----------------- ---------------- --------------------- ------------- ----------------------- -
CREATION_ CREATED_BY UPDATED_D
--------- -------------------------------------------------- ---------
UPDATED_BY I E W
-------------------------------------------------- - - -
0 Y Y N N N N 1 2 N N N 0
1 0 0 -1 -1
-1 -1 -1 2 1 N
20-MAR-06
YX N N N
If I give the schame_name.table name, then it works fine.
I am not sure whether any extra privilege is required for DBMA_XMLSTORE as with DBMS_XMLSAVE my code is working fine.
Please help.
Regards
Himanshu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
