Main Goal: Query the database to determine what the lab technician should do next. What I am trying to accomplish I am designing a laboratory database where each of the following entities requires exactly one parent (to the left) and at least one child (to the right): request (REQ) -> sample (SAM) -> test (TST) -> measurement (MEA). ...
I often write queries wherein I pivot data and end up with NULL values that I want to collapse. E.g. data like the following: id time_in time_out 1 2009-11-01 1 2009-10-30 2 2008-12-15 2 2009-02-03 I then do an outer query like so: SELECT id, MIN(time_in) AS time_in, MIN(time_out) A...
Working with Oracle 11g here. I'm trying to figure out how to write a specific query against a sample table below: ID TYPE PRIORITY STATUS DATE ---------------------------------------------------- 1 Q A NEW 01-OCT-2009 1 Q A NEW 01-OCT-2009 1 Q A NEW 01-OCT-2009...
I have a String stored in a table in the following key-value format: "Key1☺Value1☺Key2☺Value2☺KeyN☺ValueN☺". Given a Key how can I extract the Value? Is regex the easiest way to handle this? I am new to PL/SQL as well as Regex. ...
Has anyone else noticed this phenomenon where dbms_output.put_line is unable to print more than 2000 characters at a time? Script is: set serveroutput on size 100000; declare big_str varchar2(2009); begin for i in 1..2009 loop big_str := big_str||'x'; end loop; dbms_output.put_line(length(big_str)); dbms_o...
I have a fairly complex query that will be referencing a single date as a start or stop date multiple times throughout. I'm going to have to run this query for 3 different fiscal years, and don't want to have to hunt down the date 17 times in order to change it throughout my query. Is there a way to set a variable at the beginning of my...
Hi, i'm new to pl/sql and trying to print even numbers up till 100 using the following code: Declare i number; sum number:=0; x number:=2; n number; Begin for i in 1..100 loop if (i%x=0) then n:=i; sum:=sum+n; end if; end loop; dbms_output.put_line(sum); end; i get this error ERROR ...
I need to get the result of concatenating 2 similar querys' resulsets. For some reason had to split the original query in 2, both with their corresponding order by clause. Should be something like (this is an oversimplification of the original queries) Query1: Select name, age from person where age=10 Resultset1: Person1, 10 Person3, 1...
I am currently trying to populate a cursor in the procedure. like that : Function notImportantFunction variable nothing(20); Cursor notImportantCursor Is select...; Cursor THEcursor; begin open notImportantCursor; open THEcursor; LOOP FETCH notImportantCursor variable; EXIT WHEN notImportantCursor%NOTFO...
I have the PL/SQL code that is similar to the following snippet: create or replace package body MY_PACKAGE as type array_type is table of char index by varchar2(1); lookup_array array_type; function DO_SOMETHING(input nvarchar2) return varchar2 as begin -- Do something here with lookup_array end DO_SOMETHI...
Dear All, I am trying to use the following procedure as a datasource for my crystal report. The query works as I expected but the problem is I can't figure out how to fetch back the data from those dummy tables - IFS_PR_DUMMY_TAB and IFS_PR_DUMMY2_TAB CREATE OR REPLACE procedure dummy9_IFS_FR2_Sales (cdate IN date) as acontract custo...
Friends, I'm working on a requirement where I need to call a Java API from Apex. The solution I put forward was to create a Java class, store it the database and add a PL/SQL wrapper and then use it. Which is pretty much what is described here. Whilst I am happy with this, I am interested to know if this is the only method? are ther...
I'm pretty new when it comes to PL/SQL, and I have to modify someone else's stored procedure. Is it possible to add an if statement in the middle of a select? The procedure I have tries to open a cursor and select a bunch of stuff from different tables into it (don't ask me why they didn't use a join) and return the cursor. The thing ...
Hi all, I would like to generate 16 char length hex decimal value as sequence. My database is Oracle and would like to know is it possible to do so? I checked the SEQUENCE in Oracle but I think its only for numericals. Any idea would be great help. Thanks in advance, Abdel Olakara ...
I'm comparing three Oracle schemas. I want to get a list of all the functions and procedures used in each database. Is this possible via a query? (preferably including a flag as to whether they compile or not) Ideally it would be great to have a single query with a flag that states whether the function/procedure is in each schema. But ...
In which case we should use table partitioning? ...
I have to generate XML documents in PL/SQL from some tables in Oracle. I have never done this before, and I have found there seem to be a few main ways to do it: xmldom API xml functions (such as xmlelement, xmlagg, xmlroot) dbms_xmlgen functions There are 65 tables that will be referenced to generate a single document, and I will ha...
Hi I have an Oracle PL / SQL script. It processes about 51 millon registers, and writes results to 5 different tables. The problem is that I left the process running last night, and apparently there was an overflow in the UNDO logs. Particularly, we are not interested in Rollbacking this script, if it fails, we can run it again. Is t...
I have a view that is returning four columns of data to be pushed to an external program. When I simply query the view ("Select * from schema.view_name") I get 10353 rows. When I run the actual SQL that created the view (I literally copied and pasted what Oracle had stored, minus the "Create or Replace" statement), I get 238745 rows. An...
I have a table that contains a history of costs by location. These are updated on a monthly basis. For example Location1, $500, 01-JAN-2009 Location1, $650, 01-FEB-2009 Location1, $2000, 01-APR-2009 if I query for March 1, I want to return the value for Feb 1, since March 1 does not exist. I've written a query using an oracle analyti...