Rectangle 27 90

There are a few different limits that might come in to play in determining the number of connections an Oracle database supports. The simplest approach would be to use the SESSIONS parameter and V$SESSION, i.e.

The number of sessions the database was configured to allow

SELECT name, value 
  FROM v$parameter
 WHERE name = 'sessions'

The number of sessions currently active

SELECT COUNT(*)
  FROM v$session

As I said, though, there are other potential limits both at the database level and at the operating system level and depending on whether shared server has been configured. If shared server is ignored, you may well hit the limit of the PROCESSES parameter before you hit the limit of the SESSIONS parameter. And you may hit operating system limits because each session requires a certain amount of RAM.

sorry, how can we run this query? I'm receiving "table or view not exist" for "SELECT COUNT(*) FROM v$session"

@yin03 - That implies that whatever Oracle user you are using doesn't have privileges on the v$session view. You'd need to ask your DBA to grant you that privilege. Most likely, you'd want the select any dictionary privilege though the select_catalog_role role or a direct grant on just that object would also work.

sql - How to check the maximum number of allowed connections to an Ora...

sql oracle
Rectangle 27 157

Not only is the boolean datatype missing in Oracle's SQL (not PL/SQL), but they also have no clear recommendation about what to use instead. See this thread on asktom. From recommending CHAR(1) 'Y'/'N' they switch to NUMBER(1) 0/1 when someone points out that 'Y'/'N' depends on the English language, while e.g. German programmers might use 'J'/'N' instead.

The worst thing is that they defend this stupid decision just like they defend the ''=NULL stupidity.

But ''=NULL is false! '' IS NULL is true. :)

Michael-O: I have seen that several times. For me, it's 0/1 all the time, but other programmers prefer J/N. (I live in a German speaking country)

@Irfy Recently, I saw N and F being used, because ON and OFF begin with the same letter...

one might argue that as a replacement for a boolean, 'T'/'F' makes even more sense

sql - Is there a boolean type in oracle databases? - Stack Overflow

sql database oracle boolean
Rectangle 27 154

Not only is the boolean datatype missing in Oracle's SQL (not PL/SQL), but they also have no clear recommendation about what to use instead. See this thread on asktom. From recommending CHAR(1) 'Y'/'N' they switch to NUMBER(1) 0/1 when someone points out that 'Y'/'N' depends on the English language, while e.g. German programmers might use 'J'/'N' instead.

The worst thing is that they defend this stupid decision just like they defend the ''=NULL stupidity.

But ''=NULL is false! '' IS NULL is true. :)

Michael-O: I have seen that several times. For me, it's 0/1 all the time, but other programmers prefer J/N. (I live in a German speaking country)

@Irfy Recently, I saw N and F being used, because ON and OFF begin with the same letter...

one might argue that as a replacement for a boolean, 'T'/'F' makes even more sense

sql - Is there a boolean type in oracle databases? - Stack Overflow

sql database oracle boolean
Rectangle 27 14

You shouldn't use to_date on a date, To_date is for casting a varchar to date, not a date. If you do use the function to_date on a date, then oracle will refer to it as a string according to nls_date_format which may vary in different environments. As @jonearles said, if you want to remove the time in sysdate then use TRUNC

+1 for "do not use to_date on date columns".

sql - Comparing date with sysdate in oracle - Stack Overflow

sql oracle date-arithmetic
Rectangle 27 14

You shouldn't use to_date on a date, To_date is for casting a varchar to date, not a date. If you do use the function to_date on a date, then oracle will refer to it as a string according to nls_date_format which may vary in different environments. As @jonearles said, if you want to remove the time in sysdate then use TRUNC

+1 for "do not use to_date on date columns".

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

sql - Comparing date with sysdate in oracle - Stack Overflow

sql oracle date-arithmetic
Rectangle 27 6

If I remember Oracle correct the to_datefunction accepts a format_mask and if you use this mask: 'DD/MM/RRRR' values between 0-49 will return a 20xx year and values between 50-99 will return a 19xx year. So try this:

to_date('19/08/66', 'DD/MM/RRRR')

sql - Oracle varchar2 to date conversion - Stack Overflow

sql oracle
Rectangle 27 8

if you use 'Month' in to_char it right pads to 9 characters; you have to use the abbreviated 'MON', or to_char then trim and concatenate it to avoid this. See, http://www.techonthenet.com/oracle/functions/to_char.php

select trim(to_char(date_field, 'month')) || ' ' || to_char(date_field,'dd, yyyy')
  from ...
select to_char(date_field,'mon dd, yyyy')
  from ...

I guess I see why Oracle does this, however it is not ideal for all scenarios :|

@contactmatt, I think it's solely because September is 9 characters, that doesn't stop it being ridiculous.

@Ben - It's not ridiculous, it just dates from a time when it made perfect sense for the default to be to ensure that columnar data came out in fixed width because it was being written to reports that relied on that behavior. NullUserException's suggestion to use the 'fm' modifier is the proper way to indicate to Oracle that you don't want the default blank-padding behavior.

I think that's widely acknowledged @fasttrainofthoughts... Have you seen the vote totals? This was written by me a very long time ago when I didn't know much. Unfortunately I can't delete it because the OP accepted it. It's obvious that you should use Nulls, so I don't think it matters...

sql - Oracle Date TO_CHAR('Month DD, YYYY') has extra spaces in it - S...

sql oracle oracle10g
Rectangle 27 11

As of oracle 12c, you could use the top N queries.

SELECT fieldA,fieldB 
FROM table 
ORDER BY fieldA 
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY;
OFFSET

sql - Alternatives to LIMIT and OFFSET for paging in Oracle - Stack Ov...

sql oracle
Rectangle 27 2

I only use the forward slash once at the end of each script, to tell sqlplus that there is not more lines of code. In the middle of a script, I do not use a slash.

So do you order things that require the / (such as subprograms and triggers) at the end? What if you have multiple triggers? I ran a test and only the first one executes unless it has a / between each one. Am I missing something?

I try to avoid it (if possible), but if i can't (like in triggers), I use the semicolons and slashs exactly as used in the official scripts that generate the samples schemas of oracle: download.oracle.com/docs/cd/B19306_01/server.102/b14198/ For the inserts problem, I try to separate the scripts that create objects from those who populate the tables.

When do I need to use a semicolon vs a slash in Oracle SQL? - Stack Ov...

sql oracle
Rectangle 27 2

I only use the forward slash once at the end of each script, to tell sqlplus that there is not more lines of code. In the middle of a script, I do not use a slash.

So do you order things that require the / (such as subprograms and triggers) at the end? What if you have multiple triggers? I ran a test and only the first one executes unless it has a / between each one. Am I missing something?

I try to avoid it (if possible), but if i can't (like in triggers), I use the semicolons and slashs exactly as used in the official scripts that generate the samples schemas of oracle: download.oracle.com/docs/cd/B19306_01/server.102/b14198/ For the inserts problem, I try to separate the scripts that create objects from those who populate the tables.

When do I need to use a semicolon vs a slash in Oracle SQL? - Stack Ov...

sql oracle
Rectangle 27 1753

Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE):

update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid = ud.id
     )
 where exists (
      select * 
      from sale 
      where sale.udid = ud.id
 );
update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid
update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid
update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid
set assid = s.assid
set u.assid = s.assid

In the ANSI syntax, what happens if the SELECT after the = returns more than one row?

@ThrowawayAccount3Million It would probably fail. AFAIK, this kind of operation would expect a scalar value and will throw an error if given a result set instead.

sql server - How can I do an UPDATE statement with JOIN in SQL? - Stac...

sql sql-server sql-server-2005 tsql
Rectangle 27 1734

Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE):

update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid = ud.id
     )
 where exists (
      select * 
      from sale 
      where sale.udid = ud.id
 );
update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid
update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid
update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid
set assid = s.assid
set u.assid = s.assid

In the ANSI syntax, what happens if the SELECT after the = returns more than one row?

@ThrowawayAccount3Million It would probably fail. AFAIK, this kind of operation would expect a scalar value and will throw an error if given a result set instead.

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

sql server - How can I do an UPDATE statement with JOIN in SQL? - Stac...

sql sql-server sql-server-2005 tsql
Rectangle 27 1729

Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE):

update ud 
     set assid = (
          select sale.assid 
          from sale 
          where sale.udid = ud.id
     )
 where exists (
      select * 
      from sale 
      where sale.udid = ud.id
 );
update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid
update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid
update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid
set assid = s.assid
set u.assid = s.assid

In the ANSI syntax, what happens if the SELECT after the = returns more than one row?

@ThrowawayAccount3Million It would probably fail. AFAIK, this kind of operation would expect a scalar value and will throw an error if given a result set instead.

sql server - How can I do an UPDATE statement with JOIN in SQL? - Stac...

sql sql-server sql-server-2005 tsql
Rectangle 27 8

If you are using Oracle 12c, you can use:

SELECT DISTINCT 
  APP_ID, 
  NAME, 
  STORAGE_GB, 
  HISTORY_CREATED, 
  TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE  
  FROM HISTORY WHERE 
    STORAGE_GB IS NOT NULL AND 
      APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009')
  ORDER BY STORAGE_GB DESC
FETCH NEXT 10 ROWS ONLY

sql - Oracle SELECT TOP 10 records - Stack Overflow

sql oracle top-n
Rectangle 27 8

If you are using Oracle 12c, you can use:

SELECT DISTINCT 
  APP_ID, 
  NAME, 
  STORAGE_GB, 
  HISTORY_CREATED, 
  TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') AS HISTORY_DATE  
  FROM HISTORY WHERE 
    STORAGE_GB IS NOT NULL AND 
      APP_ID NOT IN (SELECT APP_ID FROM HISTORY WHERE TO_CHAR(HISTORY_DATE, 'DD.MM.YYYY') ='06.02.2009')
  ORDER BY STORAGE_GB DESC
FETCH NEXT 10 ROWS ONLY

sql - Oracle SELECT TOP 10 records - Stack Overflow

sql oracle top-n
Rectangle 27 9

You can rewrite it to use the ELSE condition of a CASE:

SELECT status,
       CASE status
         WHEN 'i' THEN 'Inactive'
         WHEN 't' THEN 'Terminated'
         ELSE 'Active'
       END AS StatusText
FROM   stage.tst

CASE .. WHEN expression in Oracle SQL - Stack Overflow

sql oracle oracle10g
Rectangle 27 64

DATE is a reserved keyword in Oracle, so I'm using column-name your_date instead.

If you have an index on your_date, I would use

WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
  AND your_date <  TO_DATE('2010-08-04', 'YYYY-MM-DD')
BETWEEN
WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
                    AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

If there is no index or if there are not too many records

WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')

should be sufficient. TRUNC without parameter removes hours, minutes and seconds from a DATE.

If performance really matters, consider putting a Function Based Index on that column:

CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));

trunk() worked like a charm, sweet like chocolate! I should have read your post early.

sql - Select from table by knowing only date without time (ORACLE) - S...

sql oracle date
Rectangle 27 64

DATE is a reserved keyword in Oracle, so I'm using column-name your_date instead.

If you have an index on your_date, I would use

WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
  AND your_date <  TO_DATE('2010-08-04', 'YYYY-MM-DD')
BETWEEN
WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
                    AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

If there is no index or if there are not too many records

WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')

should be sufficient. TRUNC without parameter removes hours, minutes and seconds from a DATE.

If performance really matters, consider putting a Function Based Index on that column:

CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));

trunk() worked like a charm, sweet like chocolate! I should have read your post early.

sql - Select from table by knowing only date without time (ORACLE) - S...

sql oracle date
Rectangle 27 29

The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.

Some Differences between Functions and Procedures

More Information on Functions Vs. Procedures here and here.

Ok. If function can do everything that procedures can, why I need functions? :) btw function can also return multiple values via OUT parameters.

I have mentioned already in the answer:) You can use a function in a normal SQL where as you cannot use a procedure in SQL statements. So in a normal SQL you need to use functions. Functions can be used in select or update or delete statement while procedure can't.

Sorry I will have to correct you there. A function may return only one value but you can also use OUT parameters to return multiple values from a function although you won't be able to use that function from SQL statement. Also not sure what you mean by "Stored procedure is precompiled execution plan where as functions are not", compilation of both procedures and functions operate the same way. Also, did you know you can create Function based indexes but you can't create Procedure based index? You say "there is no Difference" and then "there difference is" isn't this contradicting?

Functions vs procedures in Oracle - Stack Overflow

oracle stored-procedures plsql oracle11g stored-functions
Rectangle 27 28

The difference is- A function must return a value (of any type) by default definition of it, whereas in case of a procedure you need to use parameters like OUT or IN OUT parameters to get the results. You can use a function in a normal SQL where as you cannot use a procedure in SQL statements.

Some Differences between Functions and Procedures

More Information on Functions Vs. Procedures here and here.

Ok. If function can do everything that procedures can, why I need functions? :) btw function can also return multiple values via OUT parameters.

I have mentioned already in the answer:) You can use a function in a normal SQL where as you cannot use a procedure in SQL statements. So in a normal SQL you need to use functions. Functions can be used in select or update or delete statement while procedure can't.

Sorry I will have to correct you there. A function may return only one value but you can also use OUT parameters to return multiple values from a function although you won't be able to use that function from SQL statement. Also not sure what you mean by "Stored procedure is precompiled execution plan where as functions are not", compilation of both procedures and functions operate the same way. Also, did you know you can create Function based indexes but you can't create Procedure based index? You say "there is no Difference" and then "there difference is" isn't this contradicting?

Functions vs procedures in Oracle - Stack Overflow

oracle stored-procedures plsql oracle11g stored-functions