Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL for this SQL? (oracle 9i, unix)
Dynamic SQL for this SQL? [message #315932] Wed, 23 April 2008 04:58 Go to next message
bapalu
Messages: 5
Registered: April 2008
Junior Member
I am a bit confused in where clause part on how to write it and to bring the output to a variable say 'i' so that i can use that variable value to check some condition in the stored procedure. Any help will be appreciated.

Thanks in advance
Venkat

SELECT COUNT(* )
INTO i
FROM dba_Tab_privs
WHERE Grantee NOT IN (SELECT ROLE
FROM dba_Roles)
AND Grantee NOT IN ('ORDSYS',
'OUTLN',
'PUBLIC',
'SYS',
'SYSTEM',
'CTXSYS',
'MDSYS',
'ODM',
'OLAPSYS',
'WKSYS',
'WMSYS',
'XDB',
'LBACSYS',
'OSE$HTTP$ADMIN',
'AURORA$JIS$UTILITY$',
'REPADMIN',
'PERFSTAT',
'SYSMAN',
'DMSYS',
'MGMT_VIEW',
'EXFSYS')
AND Table_Name <> 'DBMS_REPCAT_INTERNAL_PACKAGE'
AND Table_Name NOT LIKE '%RP'
AND Grantee NOT IN (SELECT Grantee
FROM dba_Tab_privs
WHERE Table_Name IN ('DBMS_DEFER',
'DEFLOB'))
AND Grantee NOT IN (SELECT Grantee
FROM dba_Role_privs
WHERE Granted_Role = 'DBA');
Re: Dynamic SQL for this SQL? [message #315934 is a reply to message #315932] Wed, 23 April 2008 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said in my answer to your previous topic:
Michel Cadot wrote on Tue, 22 April 2008 13:13
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: Dynamic SQL for this SQL? [message #315937 is a reply to message #315934] Wed, 23 April 2008 05:12 Go to previous messageGo to next message
bapalu
Messages: 5
Registered: April 2008
Junior Member
I am a bit confused in where clause part on how to write it and to bring the output to a variable say 'i' so that i can use that variable value to check some condition in the stored procedure. Any help will be appreciated.

Thanks in advance
Venkat



SELECT COUNT(* )
INTO   i
FROM   dba_Tab_privs
WHERE  Grantee NOT IN (SELECT ROLE
                       FROM   dba_Roles)
       AND Grantee NOT IN ('ORDSYS',
                           'OUTLN',
                           'PUBLIC',
                           'SYS',
                           'SYSTEM',
                           'CTXSYS',
                           'MDSYS',
                           'ODM',
                           'OLAPSYS',
                           'WKSYS',
                           'WMSYS',
                           'XDB',
                           'LBACSYS',
                           'OSE$HTTP$ADMIN',
                           'AURORA$JIS$UTILITY$',
                           'REPADMIN',
                           'PERFSTAT',
                           'SYSMAN',
                           'DMSYS',
                           'MGMT_VIEW',
                           'EXFSYS')
       AND Table_Name <> 'DBMS_REPCAT_INTERNAL_PACKAGE'
       AND Table_Name NOT LIKE '%RP'
       AND Grantee NOT IN (SELECT Grantee
                           FROM   dba_Tab_privs
                           WHERE  Table_Name IN ('DBMS_DEFER',
                                                 'DEFLOB'))
       AND Grantee NOT IN (SELECT Grantee
                           FROM   dba_Role_privs
                           WHERE  Granted_Role = 'DBA');
Re: Dynamic SQL for this SQL? [message #315944 is a reply to message #315937] Wed, 23 April 2008 05:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I don't rally understand the question, but the query seems to works syntax-wise :

SQL> DECLARE
  2
  3      i NUMBER;
  4
  5  begin
  6
  7  SELECT COUNT(* )
  8  INTO   i
  9  FROM   dba_Tab_privs
 10  WHERE  Grantee NOT IN (SELECT ROLE
 11                         FROM   dba_Roles)
 12         AND Grantee NOT IN ('ORDSYS',
 13                             'OUTLN',
 14                             'PUBLIC',
 15                             'SYS',
 16                             'SYSTEM',
 17                             'CTXSYS',
 18                             'MDSYS',
 19                             'ODM',
 20                             'OLAPSYS',
 21                             'WKSYS',
 22                             'WMSYS',
 23                             'XDB',
 24                             'LBACSYS',
 25                             'OSE$HTTP$ADMIN',
 26                             'AURORA$JIS$UTILITY$',
 27                             'REPADMIN',
 28                             'PERFSTAT',
 29                             'SYSMAN',
 30                             'DMSYS',
 31                             'MGMT_VIEW',
 32                             'EXFSYS')
 33         AND Table_Name <> 'DBMS_REPCAT_INTERNAL_PACKAGE'
 34         AND Table_Name NOT LIKE '%RP'
 35         AND Grantee NOT IN (SELECT Grantee
 36                             FROM   dba_Tab_privs
 37                             WHERE  Table_Name IN ('DBMS_DEFER',
 38                                                   'DEFLOB'))
 39         AND Grantee NOT IN (SELECT Grantee
 40                             FROM   dba_Role_privs
 41                             WHERE  Granted_Role = 'DBA');
 42
 43
 44      Dbms_Output.put_line ('Result : ' || i );
 45
 46  END;
 47  /

Result : 222



Re: Dynamic SQL for this SQL? [message #315955 is a reply to message #315944] Wed, 23 April 2008 06:07 Go to previous messageGo to next message
bapalu
Messages: 5
Registered: April 2008
Junior Member
Sorry for not posting the question correctly, I want to execute the query on a remote database through database link so I need to modify the query in order to incude the database link in the execute immediate statement and also when referencing the tables of the remoted database.

It may look something like this:

select count(*) from dba_tab_privs@'||db_link|| ' where grantee not in (''ORDSYS'',''OUTLN'',''PUBLIC'',''SYS'',''SYSTEM'',
               ''CTXSYS'',''MDSYS'',''ODM'',''OLAPSYS'',''WKSYS'',''WMSYS'',''XDB'',''LBACSYS'',
               'OSE$HTTP$ADMIN','AURORA$JIS$UTILITY$','REPADMIN','PERFSTAT','SYSMAN',
               ''DMSYS'',''MGMT_VIEW'',''EXFSYS'')' 'and grantee not in ('select role from dba_roles@'||db_link||)'
               and table_name<>''DBMS_REPCAT_INTERNAL_PACKAGE''
               and table_name not like ''%RP''
               and grantee not in 
               ('select grantee from dba_tab_privs@'||db_link|| ' where table_name in (''DBMS_DEFER'',''DEFLOB''))'
               and grantee not in (select grantee from dba_role_privs@dba2 where granted_role='DBA') into i;
Re: Dynamic SQL for this SQL? [message #315964 is a reply to message #315955] Wed, 23 April 2008 06:20 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why ???

http://forums.oracle.com/forums/thread.jspa?threadID=646824&tstart=0

Regards

Raj
Re: Dynamic SQL for this SQL? [message #315967 is a reply to message #315964] Wed, 23 April 2008 06:33 Go to previous messageGo to next message
bapalu
Messages: 5
Registered: April 2008
Junior Member
I did not understand your "Why ???" But What i want to ask in the forum is the problem i am facing in executing the query. If you can help, Also it is a new problem i am facing in writing the query now and is different from the earlier one.

Regards
Bapalu
Re: Dynamic SQL for this SQL? [message #315984 is a reply to message #315967] Wed, 23 April 2008 07:22 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Tell me in which way it is different from your previous question. Could you please be more precise.

http://forums.oracle.com/forums/thread.jspa?threadID=646279&tstart=45

Regards

Raj
Previous Topic: insert a new column in specific position
Next Topic: recursive date problem (Merged 3)
Goto Forum:
  


Current Time: Fri Dec 09 07:30:30 CST 2016

Total time taken to generate the page: 0.09944 seconds