Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

show prior calendar month up to 5th working day of the current month

Joe Choueiri-OracleMay 21 2016 — edited May 21 2016

Hi

If you can help please.

I want to have a formula that allows me to do the following- image attached.

Any day I run the report within the period of time between May 09 and June 08 then I get data for May 01 until June 06 ( 5th working day of the month) and so on...so if I run the report on June 07 then I get From June first until July 8 (5th day of the month).

Thanks

Joe Timeline for SCA.png

Comments

618702
Dear 846417,

Please refer to http://tahiti.oracle.com for the correct argument structure of DBMS_METADATA.GET_DDL objects.

You have probably misplaced the arguments in it.

Regards.

Ogan
1 person found this helpful
dario-2280378
It's all ok when I'm running it for objects within the same schema, but when they are in another one, I got this error
Sunny kichloo
Try to give select_catalog_role to the user and also this oracle document is helpful


http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1016867


Also from what I've read and searched it seems like it is not a good idea to give this role as it gives them access to SYS schema objects

Edited by: Sunny kichloo on May 2, 2011 3:19 AM

Edited by: Sunny kichloo on May 2, 2011 3:22 AM
Kamran Agayev A.
846417 wrote:
In Oracle 11g R2, I use dbms_metadata.get_ddl to get DDL from another schema, but I got an error:

ORA-31603: object "IS_SNMP_DATA" of type TABLE not found in schema "TXV"

which grant is needed for this package to get ddl from another schema?
I tried with authid current_user (as I found on google), but no result...
What grants to you have on the object IS_SNMP_DATA that resides under the schema TXV?
dario-2280378
select & alter.

Also, invoker account has following rights:
SELECT_CATALOG_ROLE
ALTER ANY TABLE
DEBUG ANY PROCEDURE
SELECT ANY TABLE
1 person found this helpful
Kamran Agayev A.
Answer
Check the following example: 
SQL> create user us_one identified by us1;
User created.

SQL> grant connect, resource to us_one;
Grant succeeded.

SQL> create user us_two identified by us2;
User created.

SQL> grant connect, resource to us_two;
Grant succeeded.

SQL> conn us_one/us1
Connected.
SQL> create table t (id number);

Table created.

SQL> grant select on t to us_two;
Grant succeeded.

SQL> conn / as sysdba
Connected.

SQL> grant execute on dbms_metadata to us_two;

Grant succeeded.

SQL> conn us_two/us2
Connected.

SQL> select dbms_metadata.get_ddl('TABLE','T','US_ONE') from dual;
ERROR:
ORA-31603: object "T" of type TABLE not found in schema "US_ONE"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

SQL> conn / as sysdba
Connected.
SQL> grant select_catalog_role to us_two;

Grant succeeded.

SQL> conn us_two/us2
Connected.
SQL> select dbms_metadata.get_ddl('TABLE','T','US_ONE') from dual;

DBMS_METADATA.GET_DDL('TABLE','T','US_ONE')
--------------------------------------------------------------------------------

  CREATE TABLE "US_ONE"."T"
   (    "ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRA


SQL>
Marked as Answer by dario-2280378 · Sep 27 2020
Saubhik
Just to make sure, after granting SELECT_CATALOG_ROLE to your user, You need to exit and reconnect sqlplus to that specified user ;)
1 person found this helpful
Vivek
hmmm need time 2 answer

Edited by: 855956 on May 2, 2011 5:49 AM
dario-2280378
It's working when I'm invoking an sql (select from dual), but not when I'm doing it through pl/sql (package)
Kamran Agayev A.
846417 wrote:
It's working when I'm invoking an sql (select from dual), but not when I'm doing it through pl/sql (package)
Because SELECT_CATALOG_ROLE is a "role"

Check the following blog post:
http://kamranagayev.wordpress.com/2010/06/11/getting-ora-01031-being-granted-dba-role/
1 person found this helpful
dario-2280378
ok, now I copied my code to a 'target' schema, and there's the same error.... so, it's not beetween schemas...
sb92075
Privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
Kamran Agayev A.
846417 wrote:
ok, now I copied my code to a 'target' schema, and there's the same error.... so, it's not beetween schemas...
You need to grant all privileges of SELECT_CATALOG_ROLE directly to the user
1 person found this helpful
dario-2280378
I did
Kamran Agayev A.
846417 wrote:
I did
Show what you did and show the result please
1 person found this helpful
dario-2280378
this is user's script:
CREATE USER TXV
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE TXV
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 3 Roles for TXV 
  GRANT RESOURCE TO TXV;
  GRANT CONNECT TO TXV;
  GRANT SELECT_CATALOG_ROLE TO TXV;
  ALTER USER TXV DEFAULT ROLE ALL;
  -- 17 System Privileges for TXV 
  GRANT DEBUG CONNECT SESSION TO TXV;
  GRANT DEBUG ANY PROCEDURE TO TXV;
  GRANT CREATE ANY DIRECTORY TO TXV;
  GRANT CREATE MATERIALIZED VIEW TO TXV;
  GRANT CREATE SYNONYM TO TXV;
  GRANT CREATE VIEW TO TXV;
  GRANT UNLIMITED TABLESPACE TO TXV;
  GRANT ADMINISTER DATABASE TRIGGER TO TXV;
  GRANT CREATE TRIGGER TO TXV;
  GRANT CREATE ANY TABLE TO TXV;
  GRANT CREATE TABLE TO TXV;
  GRANT CREATE PROCEDURE TO TXV;
  GRANT CREATE SEQUENCE TO TXV;
  GRANT CREATE SESSION TO TXV;
  GRANT CREATE TYPE TO TXV;
  GRANT SELECT ANY TABLE TO TXV;
  GRANT ALTER TABLESPACE TO TXV;
  -- 2 Tablespace Quotas for TXV 
  ALTER USER TXV QUOTA UNLIMITED ON TXV;
  ALTER USER TXV QUOTA UNLIMITED ON TXV_ARCHIVE;
  -- 42 Object Privileges for TXV 
    GRANT DELETE, INDEX, INSERT, SELECT, UPDATE ON COMMON.ARCHIVE_TABLES TO TXV;
    GRANT ALTER, SELECT ON COMMON.ARC_SEQ TO TXV;
    GRANT ALTER, SELECT ON COMMON.CHANGE_SEQ TO TXV;
    GRANT ALTER, SELECT ON COMMON.ERROR_SEQ TO TXV;
    GRANT ALTER, SELECT ON COMMON.JOB_SEQ TO TXV;
    GRANT EXECUTE ON COMMON.RMAN_MANAGER TO TXV;
    GRANT SELECT, UPDATE ON COMMON.SETTINGS TO TXV;
    GRANT ALTER, SELECT ON COMMON.SQL_SEQ TO TXV;
    GRANT DELETE, INSERT, SELECT, UPDATE ON COMMON.T$ARCHIVE_LOGS TO TXV;
    GRANT DELETE, INSERT, SELECT, UPDATE ON COMMON.T$ARCHIVE_LOGS_DETAIL TO TXV;
    GRANT SELECT ON COMMON.T$CHANGE_LOG TO TXV;
    GRANT SELECT ON COMMON.T$DBA_INDEXES TO TXV;
    GRANT SELECT ON COMMON.T$DBA_PART_TABLES TO TXV;
    GRANT SELECT ON COMMON.T$DBA_TABLES TO TXV;
    GRANT SELECT ON COMMON.T$DBA_TAB_PARTITIONS TO TXV;
    GRANT SELECT ON COMMON.T$DBA_USERS TO TXV;
    GRANT INSERT, SELECT, UPDATE ON COMMON.T$ERRORS TO TXV;
    GRANT SELECT ON COMMON.T$SESSION_LOG TO TXV;
    GRANT DELETE, INSERT, SELECT, UPDATE ON COMMON.T$SQL_LOG TO TXV;
    GRANT EXECUTE ON COMMON.UTILS TO TXV;
    GRANT SELECT ON SYS.DBA_CONSTRAINTS TO TXV;
    GRANT SELECT ON SYS.DBA_DATA_FILES TO TXV;
    GRANT SELECT ON SYS.DBA_FREE_SPACE TO TXV;
    GRANT SELECT ON SYS.DBA_IND_COLUMNS TO TXV;
    GRANT SELECT ON SYS.DBA_INDEXES TO TXV;
    GRANT SELECT ON SYS.DBA_PART_TABLES TO TXV;
    GRANT SELECT ON SYS.DBA_TAB_COLUMNS TO TXV;
    GRANT SELECT ON SYS.DBA_TABLES TO TXV;
    GRANT SELECT ON SYS.DBA_TABLESPACES TO TXV;
    GRANT SELECT ON SYS.DBA_TAB_PARTITIONS TO TXV;
    GRANT SELECT ON SYS.DBA_USERS TO TXV;
    GRANT SELECT ON SYS.T$DBA_CONSTRAINTS TO TXV;
    GRANT EXECUTE, READ, WRITE ON DIRECTORY SYS.TPS_SAVE TO TXV WITH GRANT OPTION;
    GRANT SELECT ON SYS.V_$BACKUP_DATAFILE TO TXV;
    GRANT SELECT ON SYS.V_$DATABASE TO TXV;
    GRANT SELECT ON SYS.V_$DATABASE_BLOCK_CORRUPTION TO TXV;
    GRANT SELECT ON SYS.V_$DATAFILE TO TXV;
    GRANT SELECT ON SYS.V_$DATAFILE_HEADER TO TXV;
    GRANT SELECT ON SYS.V_$INSTANCE TO TXV;
    GRANT SELECT ON SYS.V_$PARAMETER TO TXV;
    GRANT SELECT ON SYS.V_$SESSION TO TXV;
    GRANT SELECT ON SYS.V_$TABLESPACE TO TXV;
I got:
ORA-01403: no data found

I don't understand why
sb92075
ORA-01403: no data found
I don't understand why
I don't understand either.
too bad you did not show whole session.
1 person found this helpful
Kamran Agayev A.
Could you please try my scenario, grant all privileges instead of role and try it (and post all output here)?
dario-2280378
ok, there was a bug, but on a different side :mad:

it's after getting ddl...
tnx to everyone for your efforts
jan.matusiewicz

I also receive no_data_found error when calling from a named procedure. I added all grants from the role SELECT_CATALOG_ROLE and its subrole HS_ADMIN_SELECT_ROLE

select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to <schema name>;' from role_tab_privs

where role = 'SELECT_CATALOG_ROLE' or role = 'HS_ADMIN_SELECT_ROLE';

But it didn't help (I won't paste the result of this query, because it returns >2000 lines).

What else could be necessary?

1 - 20
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 18 2016
Added on May 21 2016
#business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee
0 comments
422 views