Hello all. I'm using the Oracle 10g Database. i'm trying to figure out how to write a simple sql query to: find the missing numbers in a table between say 86002895 and 86005197 (inclusive), There are 1955 rows between 86002895 and 86005197. Ex: Current Scenario : table_1 : tracking_no | id_value 86002895 | 10 86002896 | 10 86002899 |...
I am trying to run the following code within SQLPlus: exec lbacsys.sa_sysdba.create_policy(policy_name => 'ACCESS_LOCATIONS', column_name => 'OLS_COLUMN', default_options => 'READ_CONTROL,INSERT_CONTROL,UPDATE_CONTROL,DELETE_CONTROL,LABEL_DEFAULT,LABEL_UPDATE,CH...
EDIT 9-3-10: I found this blog entry recently that was very enlightening. http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html There are times when one or the other join syntax may in fact perform better. I have also found times when a have noticed a slight performance increase (only noticeable in VLDBs) when choosing ...
I have a column in my oracle database which due reasons beyond my control contains a CSV string e.g. Item a,Item b,Item c,Item d I want to run an UPDATE statement to get rid of item c. Thus ending up with Item a,Item b,Item d How can I achieve this ...
I have the following code: begin for i in 1..2 loop insert into dba_xy.despatch select desp_id_seq.nextval, dbms_random.string('U',5), trunc(dbms_random.value(0000,9999)), prod_id from dba_xy.product prod_name from dba_xy.product; end loop; end; When I run it, oracle gives me the following error messag...
can anyone help me write a trigger to disallow particular entry into a table (for e.g. location = 'chicago' not allowed).The table schema is as follows department(deptno,deptname,location).I am using oracle 10g. ...
I have a simple parent/child type view with two columns: MYID and MYPARENTID. In PL/SQL, getting a list of all of the children of a parent is simple enough: SELECT MYID FROM MYVIEW START WITH MYID = 'TargetId1' CONNECT BY PRIOR MYID = MYPARENTID And I would get something back like this: MYID ----------- TargetId1 TargetId1Child1 T...
SELECT pd_end_dt,nrx_cnt FROM wkly_lnd.lnd_wkly_plan_rx_summary WHERE pd_end_dt >TO_DATE('01/01/2009') It is giving error ORA-01843: not a valid month i ran the following it did fine SELECT pd_end_dt,nrx_cnt FROM wkly_lnd.lnd_wkly_plan_rx_summary WHERE pd_end_dt > '01-Jan-09' but if i want to have week wise data how to do dat ...
I have two tables say (FCT_SALES_SUMMARY_A and FCT_SALES_SUMMARY_B). If we assume that table A has be generated on every monday than table B will be generated on next monday i.e after 1 week.like that there will be data for 104 weeks.But the as the weeks increase the previous data will be lost in FCT_SALES_SUMMARY_A as shown below i.e...
I need to maintain state in a PL/SQL application. It needs to hold a small table during the session. As I understand it, this is accomplished via a package variable, but I don't know how to create a table as a package variable. Anyone explain how to do this or alternatives? Expansion of Problem I have a WHERE IN condition that I must ...
If I have table with a Date column called "myDate", with values like "2009-08-25 09:00:09.0". I want to select all rows for Aug 25, from 12:00:01 AM until 11:59:59PM and NOTHING for Aug 26. Is it sufficient to simply use the condition: where myDate between Date '2009-08-25' and Date '2009-08-26' And I want to select all rows BEFORE Au...
I was thinking of using AUTONOMOUS_TRANSACTION Pragma for some logging in a batch process. Does anyone have any experience with this ? If so any pros and cons would be appreciated. ...
I am attempting to more fully document our database packages as an API. What we would like is something like JavaDocs for PL/SQL. I have seen a couple tools out there (pldoc, plsqldoc) but would like to know from people who use them how they compare. ...
On Oracle 10g we need to insert records from a view into a table to support a dumb client application that does not have sort or ORDER options itself. Is there any way to control the order in which our INSERT statement adds records to the destination table? ...
Here's the sample SQL: SELECT xml_data.field FROM (SELECT sys.XMLType(source_table.data).extract('//source_node/text()') AS field FROM source_table )xml_data GROUP BY to_clob(xml_data.field) The error only appears when I use GROUP BY to do a count. When it is removed I get the error ORA-22806: not an object or REF 22806. 00000 ...
I created a simple Oracle type: create or replace TYPE MY_TYPE AS OBJECT (ID NUMBER(30), NAME VARCHAR2(20)); Then, I created a second table type: create or replace TYPE MY_TYPE_TABLE AS TABLE OF MY_TYPE; Finally, I created a simply function: create or replace FUNCTION my_function(line_id IN NUMBER) RETURN MY_TYPE_TABLE AS retu...
I have a table that I need to insert a set of records into based on user input. IE: Selection from a list of items to choose from. For this example, let's just say it's a collection of int's. Not a lot of good examples on the internet. There's only one related question here on SO, but it lends no answers either (both answers are dead en...
I occasionally do something like.... IF very-likely-condition THEN NULL; ELSE <<code to deal with the unlikely condition>> END IF; Which gives a PLW-06002 unreachable code warning from the PL/SQL compiler on the NULL line atfer the IF. Now whilst I can clearly ignore the warning and/or refactor the IF statement to be a NOT, I...
I am using the REGEXP_LIKE function in Oracle 10g to find values in a column with a suffix of _#(like _1, _2 etc). I can find _# in any part of the value with the query below but can I return only values with _# at the end ? SELECT * FROM Table WHERE REGEXP_LIKE (COLUMN,'_[[:digit:]]') ...
what is the default value of a variable (VARCHAR2) at the time of declaration in PL SQL? Can i check it against NULL once after i declare a variable? ...