I tried to grant CONNECT to a user through a role: CREATE ROLE my_role IDENTIFIED BY "passwd"; GRANT CONNECT TO my_role; CREATE USER my_user IDENTIFIED BY "passwd"; GRANT my_role TO my_user; When I try this in 10g it works fine, while in 11g login is rejected: ORA-01045:user MY_USER lacks CREATE SESSION privilege; logon denied Gran...
I am trying to connect to oracle 11g installed on Linux EL 5 and and getting the following error SQL> connect sys/password@ud06 as sysdba ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor my listener.ora under network/admin is as follows LISTENER= (DESCRIPTION= (ADDRESS_LIST= ...
I have a type called budget defined as create type budget as object ( year number, amount number, member function left_over (year in number) return number ) Body: create type body budget as member function left_over(year in number) return number is begin return amount; end left_over; end; And an obj...
I'm trying to use Oracle's Function Result Cache on 11.2.0, so I did the following test: CREATE OR REPLACE FUNCTION get_test_value RETURN NUMBER RESULT_CACHE AS BEGIN dbms_output.put_line( 'Called' ); RETURN 0; END; SELECT get_test_value FROM dual; My example prints Called every time, though.I also tried some other examples f...
For example, given the following code, how is it possible to make the string comparison case-insensitive? var query = from employeeGroup in _session.Linq<EmployeeGroup>() from employee in employeeGroup.Employee where employee.UserName == username select employeeGroup.Em...
I have the following: create type customer as object ( id number, name varchar2(10), points number, member procedure add_points(num_points number) ) not final; / create type body customer as member procedure add_points(num_points number) is begin points := points + num_points; commit; end add_points; end; / create table custome...
According to the Oracle docs, i can use TREAT to return a specific subtype in a query, see Example 2-38 However, the docs also state that for any subtypes that aren't students (in the example), it returns NULL. Since i am using PUT_LINE to output a report, with headings "STUDENT NAME: " etc, it is outputting rows of empty headings. H...
I have a type myType declared with a member procedure insert_obj. When i try this code, i get the following error: declare v_obj myType; begin v_obj.insert_obj(1,2,3); end; ORA-30625: method dispatch on NULL SELF argument is disallowed I am assuming this is because i have no object on which to call the method... but i cannot ...
I have this running in SQLplus set feedback off set pagesize 0 spool TypeDrop.sql select distinct 'drop type '||object_name|| ';' from user_objects where object_type='TYPE'; spool off It prints out to TypeDrop.sql: SQL> select distinct 'drop type '||object_name||';' from user_objects where object_type='TYPE'; drop type ADDRESS_OBJTY...
I want to be able to update a subtype attribute, and some research has led me to believe it can be done with TREAT. I have tried a couple of variations on this syntax: UPDATE table1 t SET TREAT(VALUE(t) AS myType).attributeToUpdate = 10 WHERE id=1; myType is a subtype of the type of table1. Keep getting the error ORA-00927 missing eq...
We have a table which has a DATE column d. I'd like to get all the rows where the d column is greater / lower than some value, regardless of the date. For example | d | ------------------- |2009/11/1 15:55:23| -------------------- |2009/11/2 15:55:23| -------------------- |2009/11/3 15:55:23| -------------------- |2009/...
This is a follow up question to http://stackoverflow.com/questions/2344403/transpose-one-row-into-many-rows-oracle I want to be able to unpivot an arbitrary query result. To unpivot a table manually, I would do: select value_type, value from ( ( -- query to be unpivoted -- EG: select col1, col2, col3, col4, col5 from table ) ...
I'm mostly an oracle novice, so forgive me if this is a stupid question... I have a schema called 'CODE' with a stored proc that executes arbitrary SQL (for now, please ignore the potential security issues associated with that). The SQL that is passed in will select data; but all of the data resides in either schema A, B, or C - but th...
How can create a New user in ORACLE with full access (alter, delete, select, inset, debug, etc) to an only one specific SCHEMA. ...
Wich role is recommended for an ORACLE user used internally by an desktop or web application? this application makes querys, updates and inserts over only one schema. Exist a default o recommended role for this task? ...
Hey guys, I was wondering which tool in ORACLE 11g (their latest DBMS) can be used for publishing XML. I am about to download it but would first like to know the name of the XML publisher and where I could get more information about it from. Thanks in advance. S ...
Hi I wanted to do secure communication between Oracle client and Oracle server. Oracle version: 11g Whether anyone know how to configure secure communication with Oracle server and client? What are the configuration need to be done? Thanks Santhosh ...
Does anyone have experience with database links between 10g and 11g? Is this a supported setup/operation? Is it possible? Are there any problems? Caveats? ...
Does anyone know where i can find such a AMI? Oracle database Enterprise Edition 11g release 2? Has oracle supplied it somewhere or has someone else created one i can use? ...
I use Oracle's specific data provider (11g), not the Microsoft provider that is being discontinued. The thing I've found about ODP.NET is how picky it is with data types. Where JDBC and other ADO providers just convert and make things work, ODP.NET will throw an invalid cast exception unless you get it exactly right. Consider this code:...