Thanks for the question, Sushant.

Asked: May 10, 2003 - 8:21 am UTC

Last updated: March 30, 2007 - 12:01 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom

I have a user with 2 roles connect & resource

following is my code

create or replace procedure chkimm

as

begin

execute immediate('create table mytab (a number)');

end;

it compiles but when i execute i get the following error

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PAYLOG.CHKIMM", line 10
ORA-06512: at line 1

now i alter the code with invoker rights
=========================================

create or replace procedure chkimm

authid current_user

as

begin

execute immediate('create table mytab (a number)');

end;

It compiles & executes fine without any errors.

In asktom forums I got some solutions but i am still a bit confused
about invoker rights

Roles are not enabled in plsql so any privileges i have received via
roles wont execute in plsql(correct me if i am wrong). I reckon the
same is happening in the 1st code stated above & hence the error.

I wont have encountered the above problem had i received the create
table privilege not via a role but directly as privilege.

In the second code i have just added a line authid current_user
(invoker
rights) , on the execution of which the error disappears.

Here is what i got from the fourms , by using invoker rights
my procedure will run with the privileges of the user executing the
query plus any & all roles they have

but still my create table privilege is via role(connect , resource)
i havent specially granted create table privilege to scott.

So can i say by using invoker rights all privileges granted to me via
roles can behave as if they are granted individually as a privilege &
not via roles.Correct me if i am wrong.

Since roles are not enabled in plsql, Can i generalize that using
invoker rights in my procedures all privileges granted to me via roles
behave as if they are granted to me individually.

If not how can a privilege error disappear just by adding a line
authid current_user.

Regards
Sushant

and Tom said...

See
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

and if you have my book "Expert one on one Oracle" -- i have a very in depth chapter on this topic.

when you run a procedure the right way (definer rights), it runs with the base set of privs granted to the OWNER of the procedure.

When you do it using invokers rights -- it runs with all enabled privs of the current schema. Meaning, if you call the procedure from sqlplus -- then it will run AS IF IT WERE AN ANONYMOUS plsql block almost -- all privs will be inherited from your session, role based privs inclusive. If you call that same invokers rights routine from a definers rights routine, it will run with the BASE set of privs of the owner of the definers rights routine.

In general, you can use invokers rights routine for simple utilities. Do not design a system around it however, it will surely defeat the entire concept of shared sql, it'll be nightmarish to manage, it would be ugly.

Rating

(11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Is this possible ??

Rama, May 11, 2003 - 11:15 am UTC

Tom,

Let me apologise sincerely for asking a question here. I have been trying
earnestly for 2 days and couldnt get chance. As this is critical and has to
implement a solution on Monday, I had to resort to this. Please....answer my
question.

I have few Tables that has the following structure: (tables have different
structures,
but all of them have ID, PARENTID and PROCESSDATE columns)

ID NUMBER,
PROCESSDATE DATE,
COL1 NUMBER,
COL2 VARCHAR2(20),
..
..
..
..
COLN .....
PARENTID NUMBER,
KEYCOL NUMBER

The requirement is:

Replicate COL1....COLN values for all the records that has NOT NULL KEYCOL
values to:
IDs that has NULL KEYCOL values and has same PARENTID.

The KEYCOL column for each table is defined in a configuration table.

KEYVALS

SCHEMANAME TABLENAME COLUMNVALUE START_DATE
END_DATE

XYZ TAB1 KEYCOL 07-MAY-2003 31-DEC-9999
XYZ TAB2 SOMEOTHERKEYCOL 08-MAY-2003 31-DEC-9999

And, if a parentId has two Ids that has data for KEYCOL and a third ID that
donot have, then the
third ID should get replicated with the least ID value of the two IDs that
has data.

ID .....PARENTID.....KEYCOL
101 111 0.005
102 111 0.03
103 111 NULL
104 112 0.1
105 112 NULL

then, 103 should get 0.005 least of (101,102). and KEYCOL value for 105
should be 0.1(that of 104).

The procedure should be called with TableName and Date parameters.
With the passed TableName, we select the KeyCol from the KEYVALS table and
then replicate data.

I am in a jinx as to how to solve this. Your help would be very valuable
and appreciated.

thanks again.

This is what i was trying.....

create procedure replicate_data(inTable IN VARCHAR2,
inDate IN VARCHAR2
default TO_CHAR(SYSDATE-1,'YYYYMMDD')
)
AS

theSQL VARCHAR2(2000);
theData NUMBER;
theSchema VARCHAR2(30);
theTable VARCHAR2(30);
theColumn VARCHAR2(30);

BEGIN
FOR i IN (SELECT schemaName, TABLENAME, COLUMNVALUE FROM KEYVALS
WHERE TABLENAME = UPPER(inTable) AND start_date <=
TO_DATE(inDate,'YYYYMMDD') AND end_date > TO_DATE(inDate,'YYYYMMDD') )
LOOP
/** Do you want me to use bind variables for the following
statement? I didnot use them as the table name itself is not known..so
probably no use to use them **/
theSQL := 'SELECT 1 FROM
'||i.schemaName||'.'||i.tableName||' WHERE '||i.columnName||' IS NOT NULL
AND processdate = '||to_date(inDate,'YYYYMMDD') ||' AND ROWNUM = 1';
/** storing the following values into variables for use of
them outside the loop **/
theSchema := i.schemaName;
theTable := i.tableName;
theColumn := i.columnName;
execute immediate theSQL INTO theData;
END LOOP;

IF theData = 1 /** then the data table has data for the date **/
Now, I have Table and KEYCol.....But as I donot know the
number of columns...how to select the records that
has KEYCOL values and replicate them?
I am stuck here....please help.

END IF;

END;
/

May 11, 2003 - 11:33 am UTC

given:

TABLE t

ID .....PARENTID.....KEYCOL
101 111 0.005
102 111 0.03
103 111 NULL
104 112 0.1
105 112 NULL

you want to generate an update statement that looks like this:

update t
set (c1,c2...) =
(select c1,c2....
from t t2
where t2.parentid = t.parentid
and id = ( select min(id)
from t t3
where t3.parentid = t2.parentid
and keycol is not null ) )
where keycol is not null
/

just generate that statement for each table you need using USER_TAB_COLUMNS to find the column names you need to update.

Thanks !

rama, May 11, 2003 - 2:24 pm UTC

Thanks tom....that works..just a small typo there..
last line to where keycol is null

..
again thanks much.

May 11, 2003 - 2:41 pm UTC

right, you want to update the rows where keycol IS null, not is not null ;)

Problem again !

Rama, May 11, 2003 - 4:48 pm UTC

Tom,

Sorry. I am encountering this error message from the following procedure.

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "REPLICATE_FACTORS", line 41
ORA-06512: at line 1

Here is the procedure:

create or replace procedure replicate_factors
( inTable IN VARCHAR2,
inDate IN VARCHAR2 default TO_CHAR(SYSDATE-1,'YYYYMMDD')
)
AS

theSQL VARCHAR2(4000);
theData NUMBER;
theSchema VARCHAR2(30);
theTable VARCHAR2(30);
theColumn VARCHAR2(30);
theColSQL VARCHAR2(10000);
theCount NUMBER := 0;
BEGIN
FOR i IN (SELECT schemaName, tableName, columnName FROM replicate_qa WHERE tableName = UPPER(inTable) AND start_date <= TO_DATE(inDate,'YYYYMMDD') AND end_date > TO_DATE(inDate,'YYYYMMDD') AND replicate = 'Y')
LOOP
theSQL := 'SELECT 1 FROM '||i.schemaName||'.'||i.tableName||' WHERE '||i.columnName||' IS NOT NULL AND ROWNUM = 1';
dbms_output.put_line(theSQL);
theSchema := i.schemaName;
theTable := i.tableName;
theColumn := i.columnName;
execute immediate theSQL INTO theData;
END LOOP;
dbms_output.put_line('theData count is: '|| theData ||' - '|| theSchema ||' - '||theTable|| ' - ' || theColumn );
IF theData = 1 THEN

FOR i IN (SELECT column_name FROM ALL_TAB_COLUMNS WHERE owner = theSchema AND table_name = theTable and Column_name NOT IN ( 'CAMO_ID', 'PARENT_CAMO_ID','COMPANY_NAME','FACTSETID','CUSIPSEDOL','EFFECTIVE_DATE','REPLICATION_FG'))
LOOP
IF theCount = 0 THEN
theCOLSQL := i.column_name;
theCount := theCount + 1;
ELSE
theCOLSQL := theColSQL || ',' ||i.column_name;
END IF;
END LOOP;

theSQL := 'UPDATE '||theSchema||'.'||theTable ||' t ';
theSQL := theSQL || 'SET ('||theCOLSQL || ',REPLICATION_FG) = (SELECT '|| theCOLSQL || ',''Y'' FROM ' || theSchema||'.'||theTable ||' t1 ';
theSQL := theSQL ||' WHERE t.parent_camo_id = t1.parent_camo_id AND t.fs_date_downloading = t1.fs_date_downloading AND camo_id IN ';
theSQL := theSQL ||' (SELECT MIN(camo_id) FROM '|| theSchema||'.'||theTable ||' t2 WHERE ';
theSQL := theSQL ||' t2.parent_camo_id = t1.parent_camo_id AND '|| theColumn || ' IS NOT NULL AND t2.fs_date_downloading = t1.fs_date_downloading ))';
theSQL := theSQL ||' WHERE fs_date_downloading = ''' || inDate ||''' AND '|| theColumn || ' IS NULL';

EXECUTE IMMEDIATE theSQL;

commit;

END IF;
END;
/

Now, if I execute the UPDATE SQL at command prompt, it runs fine.
Here is my table structure:

Name Null? Type
----------------------------------------- -------- ----------------------------
FACTSETID VARCHAR2(100)
DATE_POPULATION VARCHAR2(100)
CUSIPSEDOL VARCHAR2(100)
CAMOID VARCHAR2(100)
PARENT_CAMO_ID VARCHAR2(100)
COMPANY_NAME VARCHAR2(100)
FS_DATE_DOWNLOADING VARCHAR2(100)
CAM_COUNTRY_CD VARCHAR2(100)
CAM_COUNTRY VARCHAR2(100)
CAM_SECTOR VARCHAR2(100)
CAM_INDUSTRY_GROUP VARCHAR2(100)
CAM_INDUSTRY VARCHAR2(100)
CAM_SUB_INDUSTRY VARCHAR2(100)
GICS_SECTOR VARCHAR2(100)
GICS_INDGRP VARCHAR2(100)
GICS_INDUSTRY VARCHAR2(100)
GICS_SUB_INDUSTRY VARCHAR2(100)
MSCI_COUNTRY VARCHAR2(100)
MSCI_REGION VARCHAR2(100)
CW_CAP_LOCAL_CO_0 VARCHAR2(100)
CW_CAP_USD_CO_0 VARCHAR2(100)
CW_CAP_USD_SEC_0 VARCHAR2(100)
CW_CAP_LOCAL_SEC_0 VARCHAR2(100)
WS_CURRENCY VARCHAR2(100)
IB_DATE_FY0_0 VARCHAR2(100)
IB_DATE_FY1_0 VARCHAR2(100)
IB_DATE_0 VARCHAR2(100)
IB_MEDIAN_FY1__0 VARCHAR2(100)
IB_MEDIAN_FY2__0 VARCHAR2(100)
IB_MEAN_FY1__0 VARCHAR2(100)
IB_MEAN_FY2__0 VARCHAR2(100)
IB_A_EPS_ACTUAL_0 VARCHAR2(100)
FS_PRICE_IBESCURR VARCHAR2(100)
CP_PE_1Y_MAVG_0 VARCHAR2(100)
CP_PE_3Y_MAVG_0 VARCHAR2(100)
CP_PE_5Y_MAVG_0 VARCHAR2(100)
CP_PE_7Y_MAVG_0 VARCHAR2(100)
WS_PE_1Y_MAVG_0 VARCHAR2(100)
WS_PE_3Y_MAVG_0 VARCHAR2(100)
WS_PE_5Y_MAVG_0 VARCHAR2(100)
WS_PE_7Y_MAVG_0 VARCHAR2(100)
FILING_DAYS NUMBER
MOD_ID NUMBER
CAMO_ID VARCHAR2(100)
EFFECTIVE_DATE_SAS VARCHAR2(9)
CW_EPS_0 VARCHAR2(100)
CW_EPS_1 VARCHAR2(100)
CW_EPS_2 VARCHAR2(100)
CW_EPS_3 VARCHAR2(100)
CW_EPS_4 VARCHAR2(100)
CW_EPS_5 VARCHAR2(100)
EPSG_5TO4 NUMBER
EPSG_4TO3 NUMBER
EPSG_3TO2 NUMBER
EPSG_2TO1 NUMBER
EPSG_1TO0 NUMBER
EPSG_0TOFY1 NUMBER
EPSG_FY1TOFY2 NUMBER
EPSG_MEDIAN_5TOFY2 NUMBER
EPSG_MEAN_5TOFY2 NUMBER
EPSG_MEDIAN_5TO0 NUMBER
EPSG_MEAN_5TO0 NUMBER
EPSG_MEDIAN_0TOFY2 NUMBER
EPSG_ACCELERATION_5TOFY2 NUMBER
FY0_ACTUAL_DATE DATE
DAYS_TO_REPORT NUMBER
TIME_WEIGHT NUMBER
EPSG_LTM NUMBER
EPSG_NTM NUMBER
EPSG_ACCELERATION_NTM NUMBER
PE_ACTUAL NUMBER
PE_MEDIAN_FY1 NUMBER
PE_MEDIAN_FY2 NUMBER
PE_MEDIAN_LTM NUMBER
PE_MEDIAN_NTM NUMBER
PE_MEAN_FY1 NUMBER
PE_MEAN_FY2 NUMBER
PE_MEAN_LTM NUMBER
PE_MEAN_NTM NUMBER
CW_PE_1Y_MAVG_0 VARCHAR2(100)
CW_PE_3Y_MAVG_0 VARCHAR2(100)
CW_PE_5Y_MAVG_0 VARCHAR2(100)
CW_PE_7Y_MAVG_0 VARCHAR2(100)
PE_ACTUAL_REL_1Y NUMBER
PE_ACTUAL_REL_3Y NUMBER
PE_ACTUAL_REL_5Y NUMBER
PE_ACTUAL_REL_7Y NUMBER
PE_MEDIAN_FY1_REL_1Y NUMBER
PE_MEDIAN_FY1_REL_3Y NUMBER
PE_MEDIAN_FY1_REL_5Y NUMBER
PE_MEDIAN_FY1_REL_7Y NUMBER
PE_MEDIAN_FY2_REL_1Y NUMBER
PE_MEDIAN_FY2_REL_3Y NUMBER
PE_MEDIAN_FY2_REL_5Y NUMBER
PE_MEDIAN_FY2_REL_7Y NUMBER
PE_MEDIAN_LTM_REL_1Y NUMBER
PE_MEDIAN_LTM_REL_3Y NUMBER
PE_MEDIAN_LTM_REL_5Y NUMBER
PE_MEDIAN_LTM_REL_7Y NUMBER
PE_MEDIAN_NTM_REL_1Y NUMBER
PE_MEDIAN_NTM_REL_3Y NUMBER
PE_MEDIAN_NTM_REL_5Y NUMBER
PE_MEDIAN_NTM_REL_7Y NUMBER
PE_MEAN_FY1_REL_1Y NUMBER
PE_MEAN_FY1_REL_3Y NUMBER
PE_MEAN_FY1_REL_5Y NUMBER
PE_MEAN_FY1_REL_7Y NUMBER
PE_MEAN_FY2_REL_1Y NUMBER
PE_MEAN_FY2_REL_3Y NUMBER
PE_MEAN_FY2_REL_5Y NUMBER
PE_MEAN_FY2_REL_7Y NUMBER
PE_MEAN_LTM_REL_1Y NUMBER
PE_MEAN_LTM_REL_3Y NUMBER
PE_MEAN_LTM_REL_5Y NUMBER
PE_MEAN_LTM_REL_7Y NUMBER
PE_MEAN_NTM_REL_1Y NUMBER
PE_MEAN_NTM_REL_3Y NUMBER
PE_MEAN_NTM_REL_5Y NUMBER
PE_MEAN_NTM_REL_7Y NUMBER
PEG_MEDIAN_ACTUAL NUMBER
PEG_MEDIAN_FY1 NUMBER
PEG_MEDIAN_FY2 NUMBER
PEG_MEDIAN_LTM NUMBER
PEG_MEDIAN_NTM NUMBER
PEG_MEAN_ACTUAL NUMBER
PEG_MEAN_FY1 NUMBER
PEG_MEAN_FY2 NUMBER
PEG_MEAN_LTM NUMBER
PEG_MEAN_NTM NUMBER
EFFECTIVE_DATE DATE
REPLICATION_FG CHAR(1)

Can you help me...please.

May 11, 2003 - 4:58 pm UTC

print out the update statement -- you'll see your bug then.

i'll guess it is around this:

theSQL := theSQL ||' WHERE fs_date_downloading = ''' || inDate
||''' AND

but instead of building and executing the statment, just print it out -- you'll see what you are building that way:

dbms_output.put_line( 'UPDATE '||theSchema||'.'||theTable ||' t ' );
dbms_output.put_line( 'SET ('||theCOLSQL || ',REPLICATION_FG) =
(SELECT '|| theCOLSQL || ',''Y'' FROM ' || theSchema||'.'||theTable ||' t1 ');
....

Tried that !

Rama, May 11, 2003 - 5:29 pm UTC

tom,

I am sorry I didnot mention it earlier. the update (copied from dbms output from the procedure) statement when executed from SQL*PLUS works fine. But, when the statement is executed dynamically, it gives this error.

Here is samle output:

-- LINE 1
UPDATE EQMODEL.TEST_PE_EPSG t
-- LINE 2
SET
(DATE_POPULATION,CAMOID,FS_DATE_DOWNLOADING,CAM_COUNTRY_CD,CAM_COUNTRY,CAM_SECTOR,CAM_INDUSTRY_GROUP,CAM_INDUSTRY,CAM_SUB_INDUSTRY
,GICS_SECTOR,GICS_INDGRP,GICS_INDUSTRY,GICS_SUB_INDUSTRY,MSCI_COUNTRY,MSCI_REGION,CW_CAP_LOCAL_CO_0,CW_CAP_USD_CO_0,
CW_CAP_USD_SEC_0,CW_CAP_LOCAL_SEC_0,WS_CURRENCY,IB_DATE_FY0_0,IB_DATE_FY1_0,IB_DATE_0,IB_MEDIAN_FY1__0,IB_MEDIAN_FY2__0,
IB_MEAN_FY1__0,IB_MEAN_FY2__0,IB_A_EPS_ACTUAL_0,FS_PRICE_IBESCURR,CP_PE_1Y_MAVG_0,CP_PE_3Y_MAVG_0,CP_PE_5Y_MAVG_0,CP_PE_7Y_MAVG_0,
WS_PE_1Y_MAVG_0,WS_PE_3Y_MAVG_0,WS_PE_5Y_MAVG_0,WS_PE_7Y_MAVG_0,FILING_DAYS,MOD_ID,EFFECTIVE_DATE_SAS,CW_EPS_0,CW_EPS_1,CW_EPS_2,
CW_EPS_3,CW_EPS_4,CW_EPS_5,EPSG_5TO4,EPSG_4TO3,EPSG_3TO2,EPSG_2TO1,EPSG_1TO0,EPSG_0TOFY1,EPSG_FY1TOFY2,
EPSG_MEDIAN_5TOFY2,EPSG_MEAN_5TOFY2,EPSG_MEDIAN_5TO0,EPSG_MEAN_5TO0,EPSG_MEDIAN_0TOFY2,EPSG_ACCELERATION_5TOFY2,FY0_ACTUAL_DATE,DAYS_TO_REPORT,
TIME_WEIGHT,EPSG_LTM,EPSG_NTM,EPSG_ACCELERATION_NTM,PE_ACTUAL,PE_MEDIAN_FY1,PE_MEDIAN_FY2,PE_MEDIAN_LTM,PE_MEDIAN_NTM,
PE_MEAN_FY1,PE_MEAN_FY2,PE_MEAN_LTM,PE_MEAN_NTM,CW_PE_1Y_MAVG_0,CW_PE_3Y_MAVG_0,CW_PE_5Y_MAVG_0,CW_PE_7Y_MAVG_0,PE_ACTUAL_REL_1Y,
PE_ACTUAL_REL_3Y,PE_ACTUAL_REL_5Y,PE_ACTUAL_REL_7Y,PE_MEDIAN_FY1_REL_1Y,PE_MEDIAN_FY1_REL_3Y,PE_MEDIAN_FY1_REL_5Y,
PE_MEDIAN_FY1_REL_7Y,PE_MEDIAN_FY2_REL_1Y,PE_MEDIAN_FY2_REL_3Y,PE_MEDIAN_FY2_REL_5Y,PE_MEDIAN_FY2_REL_7Y,PE_MEDIAN_LTM_REL_1Y,
PE_MEDIAN_LTM_REL_3Y,PE_MEDIAN_LTM_REL_5Y,PE_MEDIAN_LTM_REL_7Y,PE_MEDIAN_NTM_REL_1Y,PE_MEDIAN_NTM_REL_3Y,PE_MEDIAN_NTM_REL_5Y,
PE_MEDIAN_NTM_REL_7Y,PE_MEAN_FY1_REL_1Y,PE_MEAN_FY1_REL_3Y,PE_MEAN_FY1_REL_5Y,PE_MEAN_FY1_REL_7Y,PE_MEAN_FY2_REL_1Y,PE_MEAN_FY2_REL_3Y,
PE_MEAN_FY2_REL_5Y,PE_MEAN_FY2_REL_7Y,PE_MEAN_LTM_REL_1Y,PE_MEAN_LTM_REL_3Y,PE_MEAN_LTM_REL_5Y,PE_MEAN_LTM_REL_7Y,
PE_MEAN_NTM_REL_1Y,PE_MEAN_NTM_REL_3Y,PE_MEAN_NTM_REL_5Y,PE_MEAN_NTM_REL_7Y,PEG_MEDIAN_ACTUAL,PEG_MEDIAN_FY1,PEG_MEDIAN_FY2,
PEG_MEDIAN_LTM,PEG_MEDIAN_NTM,PEG_MEAN_ACTUAL,PEG_MEAN_FY1,PEG_MEAN_FY2,PEG_MEAN_LTM,PEG_MEAN_NTM,REPLICATION_FG) =
(SELECT DATE_POPULATION,CAMOID,FS_DATE_DOWNLOADING,CAM_COUNTRY_CD,CAM_COUNTRY,CAM_SECTOR,CAM_INDUSTRY_GROUP,CAM_INDUSTRY,CAM_SUB_INDUSTRY,GICS_SECTOR,
GICS_INDGRP,GICS_INDUSTRY,GICS_SUB_INDUSTRY,MSCI_COUNTRY,MSCI_REGION,CW_CAP_LOCAL_CO_0,CW_CAP_USD_CO_0,CW_CAP_USD_SEC_0,
CW_CAP_LOCAL_SEC_0,WS_CURRENCY,IB_DATE_FY0_0,IB_DATE_FY1_0,IB_DATE_0,IB_MEDIAN_FY1__0,IB_MEDIAN_FY2__0,IB_MEAN_FY1__0,IB_MEAN_FY2__0,
IB_A_EPS_ACTUAL_0,FS_PRICE_IBESCURR,CP_PE_1Y_MAVG_0,CP_PE_3Y_MAVG_0,CP_PE_5Y_MAVG_0,CP_PE_7Y_MAVG_0,WS_PE_1Y_MAVG_0,
WS_PE_3Y_MAVG_0,WS_PE_5Y_MAVG_0,WS_PE_7Y_MAVG_0,FILING_DAYS,MOD_ID,EFFECTIVE_DATE_SAS,CW_EPS_0,CW_EPS_1,CW_EPS_2,CW_EPS_3,CW_EPS_4,
CW_EPS_5,EPSG_5TO4,EPSG_4TO3,EPSG_3TO2,EPSG_2TO1,EPSG_1TO0,EPSG_0TOFY1,EPSG_FY1TOFY2,EPSG_MEDIAN_5TOFY2,EPSG_MEAN_5TOFY2,
EPSG_MEDIAN_5TO0,EPSG_MEAN_5TO0,EPSG_MEDIAN_0TOFY2,EPSG_ACCELERATION_5TOFY2,FY0_ACTUAL_DATE,DAYS_TO_REPORT,TIME_WEIGHT,EPSG_LTM,
EPSG_NTM,EPSG_ACCELERATION_NTM,PE_ACTUAL,PE_MEDIAN_FY1,PE_MEDIAN_FY2,PE_MEDIAN_LTM,PE_MEDIAN_NTM,PE_MEAN_FY1,PE_MEAN_FY2,
PE_MEAN_LTM,PE_MEAN_NTM,CW_PE_1Y_MAVG_0,CW_PE_3Y_MAVG_0,CW_PE_5Y_MAVG_0,CW_PE_7Y_MAVG_0,PE_ACTUAL_REL_1Y,PE_ACTUAL_REL_3Y,
PE_ACTUAL_REL_5Y,PE_ACTUAL_REL_7Y,PE_MEDIAN_FY1_REL_1Y,PE_MEDIAN_FY1_REL_3Y,PE_MEDIAN_FY1_REL_5Y,PE_MEDIAN_FY1_REL_7Y,
PE_MEDIAN_FY2_REL_1Y,PE_MEDIAN_FY2_REL_3Y,PE_MEDIAN_FY2_REL_5Y,PE_MEDIAN_FY2_REL_7Y,PE_MEDIAN_LTM_REL_1Y,PE_MEDIAN_LTM_REL_3Y,
PE_MEDIAN_LTM_REL_5Y,PE_MEDIAN_LTM_REL_7Y,PE_MEDIAN_NTM_REL_1Y,PE_MEDIAN_NTM_REL_3Y,PE_MEDIAN_NTM_REL_5Y,PE_MEDIAN_NTM_REL_7Y,
PE_MEAN_FY1_REL_1Y,PE_MEAN_FY1_REL_3Y,PE_MEAN_FY1_REL_5Y,PE_MEAN_FY1_REL_7Y,PE_MEAN_FY2_REL_1Y,PE_MEAN_FY2_REL_3Y,PE_MEAN_FY2_REL_5Y,
PE_MEAN_FY2_REL_7Y,PE_MEAN_LTM_REL_1Y,PE_MEAN_LTM_REL_3Y,PE_MEAN_LTM_REL_5Y,PE_MEAN_LTM_REL_7Y,PE_MEAN_NTM_REL_1Y,
PE_MEAN_NTM_REL_3Y,PE_MEAN_NTM_REL_5Y,PE_MEAN_NTM_REL_7Y,PEG_MEDIAN_ACTUAL,PEG_MEDIAN_FY1,PEG_MEDIAN_FY2,PEG_MEDIAN_LTM,PEG_MEDIAN_NTM,
PEG_MEAN_ACTUAL,PEG_MEAN_FY1,PEG_MEAN_FY2,PEG_MEAN_LTM,PEG_MEAN_NTM,'Y' FROM EQMODEL.TEST_PE_EPSG t1
-- LINE 3
WHERE t.parent_camo_id = t1.parent_camo_id AND t.fs_date_downloading = t1.fs_date_downloading AND camo_id IN
-- LINE 4
(SELECT MIN(camo_id) FROM EQMODEL.TEST_PE_EPSG t2 WHERE
-- LINE 5
t2.parent_camo_id = t1.parent_camo_id AND EPSG_MEDIAN_0TOFY2 IS NOT NULL AND t2.fs_date_downloading = t1.fs_date_downloading ))
-- LINE 6
WHERE fs_date_downloading = '20030507' AND EPSG_MEDIAN_0TOFY2 IS NULL

But, the same statement with Execute Immediate fails. However, this is working dynamically for this table structure.

Name Null? Type
----------------------------------------- -------- ---------------------
FACTSETID VARCHAR2(100)
DATE_POPULATION VARCHAR2(100)
CUSIPSEDOL VARCHAR2(100)
CAMOID VARCHAR2(100)
PARENT_CAMO_ID VARCHAR2(100)
COMPANY_NAME VARCHAR2(100)
FS_DATE_DOWNLOADING VARCHAR2(100)
CAM_COUNTRY_CD VARCHAR2(100)
CAM_COUNTRY VARCHAR2(100)
CAM_SECTOR VARCHAR2(100)
CAM_INDUSTRY_GROUP VARCHAR2(100)
CAM_INDUSTRY VARCHAR2(100)
CAM_SUB_INDUSTRY VARCHAR2(100)
GICS_SECTOR VARCHAR2(100)
GICS_INDGRP VARCHAR2(100)
GICS_INDUSTRY VARCHAR2(100)
GICS_SUB_INDUSTRY VARCHAR2(100)
MSCI_COUNTRY VARCHAR2(100)
MSCI_REGION VARCHAR2(100)
CW_CAP_LOCAL_CO_0 VARCHAR2(100)
CW_CAP_USD_CO_0 VARCHAR2(100)
CW_CAP_USD_SEC_0 VARCHAR2(100)
CW_CAP_LOCAL_SEC_0 VARCHAR2(100)
WS_CURRENCY VARCHAR2(100)
IB_DATE_FY0_0 VARCHAR2(100)
IB_DATE_FY1_0 VARCHAR2(100)
IB_DATE_0 VARCHAR2(100)
IB_DATE_A_EPS_ACTUAL VARCHAR2(100)
IB_MEDIAN_FY1__0 VARCHAR2(100)
IB_MEDIAN_FY1__1 VARCHAR2(100)
IB_MEDIAN_FY1__3 VARCHAR2(100)
IB_MEDIAN_FY1__6 VARCHAR2(100)
IB_MEDIAN_FY2__0 VARCHAR2(100)
IB_MEDIAN_FY2__1 VARCHAR2(100)
IB_MEDIAN_FY2__3 VARCHAR2(100)
IB_MEDIAN_FY2__6 VARCHAR2(100)
IB_MEDIAN_FY3__0 VARCHAR2(100)
IB_MEDIAN_FY3__1 VARCHAR2(100)
IB_MEDIAN_FY3__3 VARCHAR2(100)
IB_MEDIAN_FY3__6 VARCHAR2(100)
IB_FY1_NUM__0 VARCHAR2(100)
IB_FY1_NUM__1M VARCHAR2(100)
IB_FY1_NUM__3M VARCHAR2(100)
IB_FY1_NUM__6M VARCHAR2(100)
IB_FY1_UP__1M VARCHAR2(100)
IB_FY1_UP__3M VARCHAR2(100)
IB_FY1_UP__6M VARCHAR2(100)
IB_FY1_DOWN__1M VARCHAR2(100)
IB_FY1_DOWN__3M VARCHAR2(100)
IB_FY1_DOWN__6M VARCHAR2(100)
IB_FY2_NUM__0 VARCHAR2(100)
IB_FY2_NUM__1M VARCHAR2(100)
IB_FY2_NUM__3M VARCHAR2(100)
IB_FY2_NUM__6M VARCHAR2(100)
IB_FY2_UP__1M VARCHAR2(100)
IB_FY2_UP__3M VARCHAR2(100)
IB_FY2_UP__6M VARCHAR2(100)
IB_FY2_DOWN__1M VARCHAR2(100)
IB_FY2_DOWN__3M VARCHAR2(100)
IB_FY2_DOWN__6M VARCHAR2(100)
IB_FY3_NUM__0 VARCHAR2(100)
IB_FY3_NUM__1M VARCHAR2(100)
IB_FY3_NUM__3M VARCHAR2(100)
IB_FY3_NUM__6M VARCHAR2(100)
IB_FY3_UP__1M VARCHAR2(100)
IB_FY3_UP__3M VARCHAR2(100)
IB_FY3_DOWN__1M VARCHAR2(100)
IB_FY3_DOWN__3M VARCHAR2(100)
IB_FY3_DOWN__6M VARCHAR2(100)
IB_SHARES_0M VARCHAR2(100)
IB_SHARES_3M VARCHAR2(100)
FILING_DAYS NUMBER
MOD_ID NUMBER
CAMO_ID VARCHAR2(100)
EFFECTIVE_DATE_SAS VARCHAR2(9)
DAYS_TO_REPORT NUMBER
TIME_WEIGHT NUMBER
DEPTH_FY1_1M NUMBER
DEPTH_FY1_3M NUMBER
DEPTH_FY1_6M NUMBER
DEPTH_FY2_1M NUMBER
DEPTH_FY2_3M NUMBER
DEPTH_FY2_6M NUMBER
DEPTH_FY3_1M NUMBER
DEPTH_FY3_3M NUMBER
DEPTH_FY3_6M NUMBER
DEPTH_NTM_1M NUMBER
DEPTH_NTM_3M NUMBER
DEPTH_NTM_6M NUMBER
BREADTH_FY1_1M NUMBER
BREADTH_FY1_3M NUMBER
BREADTH_FY1_6M NUMBER
BREADTH_FY2_1M NUMBER
BREADTH_FY2_3M NUMBER
BREADTH_FY2_6M NUMBER
BREADTH_FY3_1M NUMBER
BREADTH_FY3_3M NUMBER
BREADTH_FY3_6M NUMBER
BREADTH_NTM_1M NUMBER
BREADTH_NTM_3M NUMBER
BREADTH_NTM_6M NUMBER
DEPTH_NTM_1M_RANK NUMBER
DEPTH_NTM_3M_RANK NUMBER
BREADTH_NTM_1M_RANK NUMBER
BREADTH_NTM_3M_RANK NUMBER
ESTIMATE_REVISION NUMBER
ESTIMATE_REVISION_RANK NUMBER
ESTIMATE_REVISION_PRCTL NUMBER
EFFECTIVE_DATE DATE
REPLICATION_FG CHAR(1)

May 11, 2003 - 7:02 pm UTC

look at line 41 of your code, I'd bet it is NOT execute immediate.

I'd bet it is theSql := theSql || ......

I'll bet varchar2(4000) isn't big enough to hold that update which appears to be about 4400 characters.

External Table

A reader, May 12, 2003 - 6:00 am UTC

Hi Tom,
I have problem when I want to query data from external table,
From [Query3]. ,It shows me that no data found, but I sure for that join have data.
Then ,I try to findout ,It seem like that problem is some field in query makes this query no data found
{ from this query is ATPT_MT_BATCH_DATE} .[Query2 ]

In the past, I ever found this problem when I first to implement External Table.
By that case, It cause some filed in DataType Number(10,2) as usually in SQL*Loader use statement
==> FIELD_A position(M:N) decimal external ":FIELD_A/100",
I resolve by use statement FIELD_A position(M:N) decimal external , then devide to(10,2) in view (FIELD_A/100) .

I think It should have better way to solve this problem , Pls suggest it to me too.

But, Now , This Problem is from Join query .While it can direct query from External Table. [Query1]

please correct me for this example..

Thank a lot.

[Query1]

16:19:36 40DWH>select ATPT_MT_BATCH_DATE
16:19:54 2 from V_GAIN_SALE_MASTER
16:20:00 3 where ATPT_ACCT = '0002299119998261006';

ATPT_MT_BAT
-----------
28-APR-2003
01-MAY-2003
02-MAY-2003
02-MAY-2003
06-MAY-2003

[Query2]

16:32:57 40DWH>(SELECT
16:33:12 2 ATPT_ORG,
16:33:12 3 ATPT_ACCT,
16:33:12 4 ATPT_LOGO,
16:33:12 5 ATPT_MT_EFF_DATE,
16:33:12 6 ATPT_MT_TXN_CODE,
16:33:12 7 ATPT_MT_AMOUNT,
16:33:12 8 -- ATPT_MT_BATCH_DATE,
16:33:12 9 ATPT_MT_POSTING_DATE,
16:33:12 10 ATPT_MT_SI_NBR,
16:33:12 11 ATPT_MT_PMT_REFERENCE_NBR,
16:33:12 12 ATPT_MT_DEPT,
16:33:12 13 GAIN_PRI_NAME_LINE_1,
16:33:12 14 GAIN_PRI_NAME_LINE_2,
16:33:12 15 GAIN_CYCLE,
16:33:12 16 GAIN_FRAUD_IND,
16:33:12 17 GAIN_BLK_CODE_1_2
16:33:12 18 FROM
16:33:13 19 V_GAIN_SALE_MASTER, V_GAIN_MASTER
16:33:13 20 WHERE ATPT_MT_TXN_CODE in ('651','051','151','152') AND
16:33:13 21 ( ATPT_ORG = GAIN_ORG (+) ) AND
16:33:13 22 ( ltrim ( ATPT_ACCT , '000' ) = GAIN_ACCT_NMBR (+) ) AND
16:33:13 23 ( ATPT_LOGO = GAIN_LOGO (+) )
16:33:13 24 and ATPT_ACCT = '0002299119998261006'
16:33:13 25 );

ATP ATPT_ACCT AT ATPT_MT_EFF ATPT_MT_TXN_CODE ATPT_MT_AMOUNT ATPT_MT_POS ATPT_MT_SI_NBR AT
--- ------------------- -- ----------- ---------------- --------------- ----------- --------------- --
201 0002299119998261006 11 09-MAY-2003 651 106.15 09-MAY-2003 9024001

16:34:04 40DWH>ed
Wrote file editsql.buf

[Query3]

1 (SELECT
2 ATPT_ORG,
3 ATPT_ACCT,
4 ATPT_LOGO,
5 ATPT_MT_EFF_DATE,
6 ATPT_MT_TXN_CODE,
7 ATPT_MT_AMOUNT,
8 ATPT_MT_BATCH_DATE,
9 ATPT_MT_POSTING_DATE,
10 ATPT_MT_SI_NBR,
11 ATPT_MT_PMT_REFERENCE_NBR,
12 ATPT_MT_DEPT,
13 GAIN_PRI_NAME_LINE_1,
14 GAIN_PRI_NAME_LINE_2,
15 GAIN_CYCLE,
16 GAIN_FRAUD_IND,
17 GAIN_BLK_CODE_1_2
18 FROM
19 V_GAIN_SALE_MASTER, V_GAIN_MASTER
20 WHERE ATPT_MT_TXN_CODE in ('651','051','151','152') AND
21 ( ATPT_ORG = GAIN_ORG (+) ) AND
22 ( ltrim ( ATPT_ACCT , '000' ) = GAIN_ACCT_NMBR (+) ) AND
23 ( ATPT_LOGO = GAIN_LOGO (+) )
24 and ATPT_ACCT = '0002299119998261006'
25* )
16:40:04 40DWH>/

no rows selected

May 12, 2003 - 8:02 am UTC

you would really have to give me a nice concise testcase with which to reproduce. for example, something like:

I've got a file, it has data like this:

.......<actual data would be here>....

I'm using this external table definition:

.....<actual create table here>......

If I select on it using <query here>, I see this <data here>

but when I create this table < table create here> and insert this stuff in it <inserts here -- actual inserts so I can run them> and join <actual query here> I get nothing....

(many times, most times in fact, in the setup of this small test case -- people find their problem!)

Figured it out !

Rama, May 12, 2003 - 11:59 am UTC

Figured it out....thanks.

V$SESSION.SCHEMANAME

Shalini, August 10, 2005 - 12:32 am UTC

Tom,

I'm having a small problem and that is
the v$schemaname and v$username are different for a particular user.
I can not give create table etc statements as this is very peculiar situation.
But I have created the user and very sure,there is nothing *Strange* I've done.
If I say
show user
user1

select schemaname from v$session is
user2
while
select username from v$session is
user1

Can you throw some light on this ?

Thanx a lot in advance,
Shalini

August 10, 2005 - 10:08 am UTC

if it happens automagically, look for a logon trigger


ops$tkyte@ORA10GR1> select schemaname, username from v$session where sid = (select sid from v$mystat where rownum=1);

SCHEMANAME                     USERNAME
------------------------------ ------------------------------
OPS$TKYTE                      OPS$TKYTE

ops$tkyte@ORA10GR1> alter session set current_schema=scott;

Session altered.

ops$tkyte@ORA10GR1> select schemaname, username from v$session where sid = (select sid from v$mystat where rownum=1);

SCHEMANAME                     USERNAME
------------------------------ ------------------------------
SCOTT                          OPS$TKYTE

V$Schemaname

Shalini, August 11, 2005 - 2:56 am UTC

Hi Tom,

Nothing of that sort is happening,there are NO logon triggers like that.
We are just totally confused what it can *mean*
and basically,from where does this v$session is getting the schemaname ?
These two users user1,user2 are both application users and this is pretty un-expected behaviour.
One more thing,when I drop user user2(its not being used) then I get expected behaviour as both schemaname and username being same = user1.
But then I recreated the user2 and again back to square 1.

Totally confused why this can be happening.

Thank you again!
-Shalini

August 11, 2005 - 9:39 am UTC

please verify that there are no logon triggers, I see it happen all of the time "oh, i didn't know you did that, added that trigger"

No,there are no logon triggers

Shalini, August 12, 2005 - 2:25 am UTC

:-(( Sadly,it would have made it easier for me to understand this.....

-Shalini

August 12, 2005 - 8:39 am UTC

are you allowed to turn sqltrace on the instance.

Yeah,tracing is possible

Shalini, August 17, 2005 - 3:00 am UTC

Tom,

are you suggesting me to turn tracing on ?
anything special you will be looking for in the tracing ?

With Regards,
Shalini

August 17, 2005 - 1:14 pm UTC

turn it on, log in as that user, read resulting trace file to look at the sql being executed. look for the alter session set current_schema.

When is invoker rights not used

Matt, March 29, 2007 - 3:39 am UTC

Tom,

We've been doing some work with an invoker rights package in our MAIN schema, its responsible for running dynamic sql and returning results. All APPLICATION schemas grant to the main schema. Our problem is we have another TOOLS schema, which has its own set of Business API's some of which call the MAIN schemas SQL Api (invoker rights) - it appears that if you call an invoker rights package from within a standard definer rights package then the definer becomes the invoker.

Schema MAIN calls package TOOLS.PKG.DO_PROCESS which includes a call to MAIN.PKG.DO_INVOKER_PROCESS then the INVOKER in this case is NOT MAIN its TOOLS.

Our issue now is as well as granting application objects (grants and syns) to our MAIN schema, we have to grant to the TOOLS schema also.

Is there a way around this type of issue?

March 30, 2007 - 12:01 pm UTC

do not use the definers rights routine in TOOLS?

I mean - what else can we say - once you hit that definers rights routine, the security domain changes - roles, gone, only the base set of privileges of the definer are in place. That is precisely the way they work - the only way they work.

Invokers rights is always used, when you use it. Just that the invoker is defined by the current security domain and once you hit that definers rights routine - that becomes the security domain.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Is this possible ??

Thanks !

Problem again !

Tried that !

External Table

Figured it out !

V$SESSION.SCHEMANAME

V$Schemaname

No,there are no logon triggers

Yeah,tracing is possible

When is invoker rights not used

More to Explore

PL/SQL demos

PL/SQL docs

Invoker rights question & privileges in dynamic sql

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Is this possible ??

Thanks !

Problem again !

Tried that !

External Table

Figured it out !

V$SESSION.SCHEMANAME

V$Schemaname

No,there are no logon triggers

Yeah,tracing is possible

When is invoker rights not used

More to Explore

PL/SQL demos

PL/SQL docs