For some reason, the files in the database won't open if they have the # character in them... One thing i noticed was in the file name, the link doesn't have %20 for the space characters after the # sign, but even when i replace the rest of the number characters with the %20 character, the link still doesn't work.. Any ideas why it cou...
I have the follwoing query which works but I'm wondering if it could be more efficient. I need the first and last name of the 4 employees from the phonebook table (pb) who's badges (the employees ID) are stored in the Commitment table SELECT Originator_ID, (SELECT pb.lname FROM pb WHERE pb.badge = Commitment.Originator_ID) AS Origina...
Suppose you have a table (in Oracle): CREATE TABLE CUSTOMER ( customer_id NUMBER, gender CHAR(1) ); And suppose you have a table type: CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER; Is it possible to write a GROUP BY query such that, for each group, that group's primary key fields are stored in a NUMBER_TABLE_TYPE? For ...
I want to return rows from a select statement within a declare/begin/end block. I can do this in T-SQL but I would like to know how to do it in PL/SQL. The code looks a bit like the following: declare blah number := 42; begin select * from x where x.value = blah; end; ...
I have two date strings, in the format MM/DD/YYYY and would like to query an Oracle database for any records between those two dates. How do I do this succinctly and simply? ...
There's two options for dealing with LINQ to Collections that are populated with SQL (I'm using an Oracle provider, so no LINQ without an ORM). 1) Do one big SQL query, dump the results into some sort of collection and do LINQ queries on the collection, so you have one big draw on the database, but not much slowdown after that. 2) Do s...
I'm trying to figure out a way to implement paging via stored procedure calls. For example, I have an 'Images' table that has say 100 rows. A website is going to make a request for the 'first' 12 then when the user 'goes to the next page' the site will make a request for the next 12. I'll be getting 2 in params (p_Offset and p_RecordCo...
Need to rank the below by salary, with highest salary having rank 1. RANK column shown is what I'm after. Empname sal address RANK Ram 3411 45,east road 2 Anirban 2311 34,west wind 4 Sagor 10000 34,south 1 Manisha 3111 12,d.h road 3 ...
I created a table named dual2. I've a rows there, and can select from it. When attempting to drop it, it produces this error: ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist However, the table still exists! It returns from dba_tables and user_tables. Any ideas on wha...
What are some of the pl/sql tricky interview questions. I guess I have one interview tomorrow... ...
Hello All, Can you please guide me to the equivalent to this query in Oracle: SELECT SQL_CALC_FOUND_ROWS * FROM tableName SELECT FOUND_ROWS() as cnt Thanks ...
Hi al, I need to optimize the following query but 'm not able to wonder how. select distinct v.codvia, v.codproine, v.codmunine, tv.SIMBOLO as SIMBOLO_TIPO_VIA, tv.NOMBRE as TIPO_VIA, c.nombrevia as NOMBRE_VIA, v.cp, m.nombre as NOMBRE_MUNICIPIO , pr.nombre as NOMBRE_PROVINCIA from tdinumvias v, ...
I have Oracle 10gR2. I am trying to create autoincrement trigger. Here is the sample: CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE / CREATE TABLE TESTER ( ID_TESTER INTEGER NOT NULL, VAL VARCHAR2(20) NOT NULL ) / CREATE OR REPLACE TRIGGER TIB_TESTER BEFORE INSERT ON TESTER FOR EACH ROW BEGIN SELECT TEST_SEQ.NEXTV...
I am new to Oracle, I have two tablespaces one for dev and one for live. Is there a way that I can have a variable on a Package header that contains a tablespace name, which can then be used from within the procedures defined in the package header? I need to do this as I have one tableSpace (TableSpaceA) that needs to query tables in T...
I'm trying to create a user defined function in Oracle that will return a DATE when given a text argument containing a date substring. I've tried a couple ways of writing this, and all seem to throw the same error: CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2(50)) RETURN DATE DETERMINISTIC IS BEGIN RETURN(TO_D...
I'm using the code from this link to send attachments in the email notifications of our system; http://www.builderau.com.au/program/oracle/soa/Sending-blob-attachments-in-e-mail-with-utl-smtp/0,339028441,339284536,00.htm The problem is that the second attachment has encoding issue. So if i send in a text file, the text in the txt file ...
In the following example, variable recordId number; BEGIN SELECT MAX(recordvalue) INTO recordId FROM sometable; END; PRINT recordid; SELECT * FROM someothertable WHERE recordkey = &recordId; The select statement on the last line cannot access the value of recordId. I know i can access recordId inside the pl/sql bl...
I'm attempting to write a simple query where I declare some variables and then use them in a select statement in Oracle. I've been able to do this before in SQL Server with the following: DECLARE @date1 DATETIME SET @date1 = '03-AUG-2010' SELECT U.VisualID FROM Usage u WITH(NOLOCK) WHERE U.UseTime > @Date1 From the searching I've ...
Hey folks, I've got this procedure: CREATE OR REPLACE PROCEDURE CONV1( pDate IN VARCHAR2, pYear OUT number, pMonth OUT number, pDay OUT number ) AS lDate DATE; BEGIN lDate := to_date(pDate, 'DD.MM.YYYY HH24:MI:SS'); pYear := to_number(to_char(lDate, 'YYYY')); pMonth := to_number(to_char(lDate, 'M...
I have PL/SQL function that returns cursor that holds 28 columns and 8100 rows. When I execute that function from SQL Plus I got the results right away and in SQL Developer I'm running script that takes looong time (about 80 seconds). The same happen from Java code. When number of columns reduced to 2 then I got response in less than 4...