Determine if Oracle date is on a weekend?
Is this the best way to determine if an Oracle date is on a weekend? select * from mytable where TO_CHAR (my_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN'); ...
plsql
Using For loop to retrieve multiple rows in Oracle procedure
Hi all, Im working on stored procedure where I need to retrieve a set of results and process each element individually and then return the entire result.(using 3 different tables) Im not too familiar with databases, but heres what I was able to come up with.. create or replace procedure GET_EMP_RSLT IS CURSOR ecursor IS select emp...
How to do top 1 in Oracle ?
hi how to do this: select top 1 Fname from MyTbl in oracle 11g ? thank's in advance ...
Create a Sequence with START WITH from Query
How can I create a Sequence where my START WITH value comes from a query? I'm trying this way: CREATE SEQUENCE "Seq" INCREMENT BY 1 START WITH (SELECT MAX("ID") FROM "Table"); But, I get the ORA-01722 error ...
How do I check for a IN condition against a dynamic list in Oracle?
EDIT: changed the title to fit the code below. I'm trying to retrieve a list of acceptable values from an Oracle table, then performing a SELECT against another while comparing some fields against said list. I was trying to do this with cursors (like below), but this fails. DECLARE TYPE gcur IS REF CURSOR; TYPE list_record IS ...
Create a temp table in PL/SQL
I'm working with an Oracle 10g database, and I want to extract a group of records from one table, and then use that for pulling records out of a bunch of related tables. If this were T-SQL, I'd do it something like this: CREATE TABLE #PatientIDs ( pId int ) INSERT INTO #PatientIDs select distinct pId from appointments SELECT * fr...
What is the maximum of the number-format in Oracle?
Heyho, I want to know what is the maximum value the number-format? For example int32 can handle 4.294.967.296 values. Greetz to the world! =) ...
Regarding date in sql
Here's my query: select * from test n WHERE lower(process_name) like 'test%' AND ( test_id is NULL OR TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE),-6),'YYYYMM') > TO_CHAR(n.process_date,'YYYYMM') I want check whether date field process_date is greater than 6 months in the query. ...
SQL query to get group by and distinct values at the same time
Hello, I'm having trouble trying to define the SQL query for this table: There's a table of patients and their weight readings recorded on visits with the following columns: patient ID weight reading visit ID (one per visit) In other words, if in two records two visit IDs are the same, then two weight readings have been taken on ...
ORACLE SQL Date range intersections
I have a table T1, it contains a NAME value (not unique), and a date range (D1 and D2 which are dates) When NAME are the same, we make a union of the date ranges (e.g. B). But as a result (X), we need to make intersection of all the date ranges Edit: Table T1 NAME | D1 | D2 A | 20100101 | 20101211 B | 20100120 | 20100415 ...
How to elmininate values in an Oracle-SQL-Table which mean the same?
I've got a table like this: ID | Val1 | Val2 --------------------- 1 | 1 | 2 2 | 1 | 3 3 | 2 | 1 4 | 2 | 3 5 | 3 | 1 6 | 3 | 2 now my problem is, that 1 - 2 means the same like 2 - 1 (look @ ID 1 and ID 3 for example) and I want to eliminate all entries where value 1 - value 2 ...
In PL/sql oracle stored procedure,trying to use analytical functions
Is it better to use cursor or analytical functions in a stored procedure for performance improvement? ...
Return 2 values from a PL-SQL function
How can i return 2 values from a PL-SQL function? ...
ORA-06502: PL/SQL: numeric or value error: character string buffer too small error
I have an oracle function which is given below. when i run this in sql-developer it gives an error ORA-06502: PL/SQL: numeric or value error: character string buffer too small error. However the DBMS_OUTPUT.PUT_LINE(FINAL_RESULT) line in the function is printing the expected output in the output window. can any help me in this?? create ...
Inconsistent datatypes in Oracle
i have the following function: create or replace FUNCTION "MXUPGKEYVAL"(tbname varchar2,colname varchar2) return number is val number; BEGIN EXECUTE IMMEDIATE 'select sum(length('||colname||')) from '||tbname into val; return val; END; and the following update: update ANINTEGDATA set val1=to_char(nvl(MXUPGKEYVAL(MX5T,MX5C),0)) whe...
What is the Oracle equivalent of SQL Server's IsNull() function?
In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL? ...
Insert into Table select result set from stored procedure but column count is not same.
I need something like that which is of course not working. insert into Table1 ( Id, Value ) select Id, value from ( exec MySPReturning10Columns ) I wanted to populate Table1 from result set returned by MySPReturning10Columns. Here the SP is returning 10 columns and the table has just 2 columns. The following way works as long...
Check if table exists in the database - PL SQL
Hello, I'm new in PL SQL, and I need to check if table exist on server and drop it. Thanks in advance, Goran ...
How to find character code in PL/SQL?
I want to find character code of particular character in string. For instance if I have a string "Hello" How do i find the character code of all or particular characters in the string. I see that PL/SQL has a ASCII() and ASCIISTR() functions but I could not find any character related functions. ...
PL/SQL Oracle regular expression doesn't work for occurencce of zero
Hi everyone, I have a problem in matching regular expression in Oracle PL/SQL. To be more specific, the problem is that regex doesn't want to match any zero occurrence. For example, I have something like: select * from dual where regexp_like('', '[[:alpha:]]*'); and this doesn't work. But if I put space in this statement: select * ...