I have a query with a where clause that looks like: WHERE field IN ( 1, 2, 3 ) Is possible to parametrize this so it looks something like: WHERE field in ( :list )? How do you create the :list parameter, assuming it's a list of ints, that could be anywhere from 1 to 10 ints? ASP.net, webforms if that makes a difference. ...
I have a parent/child relationship defined which, unfortunately, cannot be maintained via a foreign key. Parents and children are stored in the same table. The parent/child relationship is identified by a column "ITEM_ID". A child ITEM_ID consists of its parent ITEM_ID and then what we can effectively think of as the child's unique id...
I am attempting to reverse engineer a Database that is in Oracle. I have been able to find the main keys and the referential integrity rules that correspond to the Foreign Keys but cannot locate where the modality/cardinality rules are stored. Any help regarding to which table to look in or a sample query would be greatly appreciated. ...
I am using Oracle's to_char() function to convert a date to a week number (1-53): select pat_id, pat_enc_csn_id, contact_date, to_char(contact_date,'ww') week, ... the 'ww' switch gives me these values for dates in January of this year: Date Week 1-Jan-10 1 2-Jan-10 1 3-Jan-10 1 4-Jan-10 1 5-Jan...
I am taking a database class and at the beginning of the lab section of the class we usually have to drop all the tables in the database created previously. I wanted to be able to run a script that does this dynamically, but cannot seem to get it to work. Here is the code I have so far. declare tname string(50); cursor ctable is select ...
Are there any known issues of EF usage with Oracle database? Are there any key points I should specially pay attention? Share your toughts and experience please? ...
I mostly work with sql-server (when I do work with databases) and I am trying to learn pl-sql. Is there any equivalent to sql-server's auto-generated Guid as primary keys in Oracle? ...
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 table APP_REQ_APPROVE_COMPARE with following fields: "ID" NUMBER NOT NULL ENABLE, "TRACK_NO" VARCHAR2(20 BYTE) NOT NULL ENABLE, "REQ_DATE" DATE NOT NULL ENABLE, "OFFCODE" CHAR(6 BYTE) NOT NULL ENABLE, "COMPARE_CASE_ID" NUMBER NOT NULL ENABLE, "VEHICLE_NAME" VARCHAR2(100 BYTE), "ENGINE_NO" ...
In phpmyadmin, it's able to see database disk usage. I was wondering if there's such thing in Oracle SQL developer. Thanks! ...
when trying to view 'Reports > Data Modeler Reports > Design Contents > Relational Model > Relational Designs' in Oracle SQL developer. It gives "Data Modeler reporting repository required." Can anyone see what's wrong with it? Thanks a lot! ...
IR.DOC_ID D.DOC_ID D.WORK_ID I.WORK_ID I.DATE_SUB IR.DATE_SUB 1812450 1812450 8521 8521 11-AUG-09 1812301 1812301 8521 8521 11-AUG-09 1812400 1812400 8521 8521 11-AUG-09 1814250 1814250 8521 8521 12-AUG-09 1822300 1822300 8521 8521 18-AUG-09 18143...
I have to modify an existing table in a Oracle 10g DB with a few thousand records to add a surrogate autonumber key. One way that comes to my mind is to Create a new sequence Create the id column, allowing null values Updating the id column with the sequence Alter table to add "not null" and "primary key" for the new id column Is th...
Why doesn't Oracle have foreign keys in Oracle Apps/ E-Business Suite? I observed that Oracle doesn't have/use referential constraints on the Oracle Apps 11i Schema. All the logic is contained in PL/SQL packages. This seems odd coming from a RDBMS major. ...
For some reason my LDAP search doesn't seem to be returning all the attributes available for a given DN. Using the folling code: DirContext ctx = new InitialDirContext(mEnv); DirContext obj = (DirContext)ctx.lookup(dn); Attributes attrs = obj.getAttributes(new CompositeName("")); (Where mEnv is a valid Properties class, and dn is a v...
We have a legacy/production database. It has been in continuous development and use backing a website that has evolved over 10 years. The database is accessed by multiple technologies - perl cgi, php and java webapps. Lots of cruft has accumulated. I wish to undertake a major refactoring. I am sure some parts are completely legacy (the...
Hi. When executing pl/sql im obtaining an error : ORA-06550: line 1, column 316: PLS-00103: Encountered the symbol "/" The symbol "/" was ignored. PLSQL example: DECLARE SQL1 VARCHAR2 (1500); SQL2 VARCHAR2 (1500); BEGIN SQL1 := 'INSERT INTO das_html_caption VALUES (''test_test'')'; SQL2 := 'DELETE FROM das_html...
When I try to pass an array of numbers to a stored procedure, the stored procedure is not able to get the array contents. Array.count = 0 in the stored procedure! The stored procedure is pretty simple. It just has to insert the parameters into a table. code looks like : TYPE arr_parmid IS TABLE OF testtable.UNID%TYPE INDEX BY BINARY_IN...
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...