How can I insert into table with different input using /   ,with **date datatype**?

    insert into run(id,name,dob)values(&amp;id,&#39;&amp;name&#39;,[what should I write here?]);

I&#39;m using  oracle 10g.
 


How to insert date values into table

I am using oracle database. i want to execute one query to check the data between two dates.


    NAME               START_DATE    
    -------------    ------------- 
    Small Widget       15-JAN-10 04.25.32.000000 PM      
    Product 1          17-JAN-10 04.31.32.000000 PM  

  

    select * from &lt;TABLENAME&gt; where start_date  
    BETWEEN &#39;15-JAN-10&#39; AND &#39;17-JAN-10&#39;

But I dont get any results from above query. I think I have to use &quot;like&quot; and &quot;%&quot;. But I dont know where to use them. Please throw some lights on this. 

thanks in advance.

Oracle date &quot;Between&quot; Query

Hi I have simply select and works great:


    select &#39;CARAT Issue Open&#39; issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,
    gcrs.Area_name, gcrs.sector_name,
 
    substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,&#39; (&#39;)=0 then 100 else instr(gcrs.stream_name,&#39; (&#39;)-1 end) ISSUE_DIVISION,
 
    case when gcrs.STREAM_NAME like &#39;NON-GT%&#39; THEN &#39;NON-GT&#39; ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
 
 
    from table(f_carat_issues_as_of(&#39;31/MAR/2013&#39;)) i
    inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
 
    where UPPER(ISSUE_STATUS) like &#39;%OPEN%&#39;

Now I want to call two columns:
ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,

how can I do it ? 

---

my full code:

    select &#39;CARAT Issue Open&#39; issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,
    gcrs.Area_name, gcrs.sector_name,

    substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,&#39; (&#39;)=0 then 100 else instr(gcrs.stream_name,&#39; (&#39;)-1 end) ISSUE_DIVISION,

    case when gcrs.STREAM_NAME like &#39;NON-GT%&#39; THEN &#39;NON-GT&#39; ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2


    from table(f_carat_issues_as_of(&#39;31/MAR/2013&#39;)) i
    inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

    where UPPER(ISSUE_STATUS) like &#39;%OPEN%&#39; and 

    CASE WHEN ISSUE_DIVISION = ISSUE_DIVISION_2 THEN 
         CASE WHEN  ISSUE_DIVISION is null then &quot;Null Value found&quot;
         Else 1 End
    ELSE 0 END As Issue_Division_Result

but I get error on line:
    ELSE 0 END As Issue_Division_Result

ORA-00920: invalid relational operator :(
     
       

If statement in select (ORACLE)

How can I stop a running SELECT statement by killing the session?

The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

How to kill a running SELECT statement

I can understand that many years ago there would be this kind of limitation, but nowadays surely this limit could easily be increased. We have naming conventions for objects, but there is always a case that turns up where we hit this limit - especially in naming foreign keys.

Does anybody actually know why this isn&#39;t a bigger size - or is it bigger in 11g?

---

Apparently the answer is that it will break currently scripts that aren&#39;t defensively coded. I say that is a very worrying thing, Oracle is trying to be **the** database, surely this is the kind of thing that you must constantly improve, otherwise your product will die the death of a thousand cuts.

Whenever I see this kind of objection in-house, I think it is time to bite the bullet and sort it out. If people are running scripts that they do not check or maintain when they upgrade Oracle versions, then let them suffer the consequences of that choice. Provide them a compatibility flag, up the size to 4000, then save me the wasted time when I&#39;m creating objects of having to constantly count to 30 to check the name is &#39;OK&#39;.  

Why are Oracle table/column/index names limited to 30 characters?

I want to check SID and current database name.

I am using following query for checking oracle SID

    select instance from v$thread;

but table or view does not exist error is coming.

I am using following query for checking current database name

    select name from v$database;

but table or view does not exist error is coming.

Any idea for above two problems?






Checking oracle sid and database name

When executing the following code, it just says the procedure is completed and doesn&#39;t print the infomation i want it to (firstName, lastName) and then the other values from the select query in a table below.

     CREATE OR REPLACE PROCEDURE PRINT_ACTOR_QUOTES (id_actor char)
    AS
    CURSOR quote_recs IS
    SELECT a.firstName,a.lastName, m.title, m.year, r.roleName ,q.quotechar from quote q, role r,   
    rolequote rq, actor a, movie m
    where
    rq.quoteID = q.quoteID
    AND
    rq.roleID = r.roleID
     AND
    r.actorID = a.actorID
    AND
    r.movieID = m.movieID
    AND
     a.actorID = id_actor;
    BEGIN
    FOR row IN quote_recs LOOP
    DBMS_OUTPUT.PUT_LINE(&#39;a.firstName&#39; || &#39;a.lastName&#39;);

    end loop;
    END PRINT_ACTOR_QUOTES;
    / 
When setting server output on, I get 

    a.firstNamea.lastName
    a.firstNamea.lastName
    a.firstNamea.lastName
    a.firstNamea.lastName
multiple times!

DBMS_OUTPUT.PUT_LINE not printing

Am I correct in understanding that CREATE OR REPLACE basically means &quot;if the object exists, drop it, then create it either way?&quot;  

If so, what am I doing wrong?  This works:

    CREATE TABLE foo (id NUMBER,
    title VARCHAR2(4000) DEFAULT &#39;Default Title&#39;)

And this doesn&#39;t (ORA-00922: missing or invalid option):

    CREATE OR REPLACE TABLE foo (id NUMBER,
    title VARCHAR2(4000) DEFAULT &#39;Default Title&#39;)

Am I doing something stupid?  I don&#39;t seem to be able to find much documentation about this syntax.

How do I use CREATE OR REPLACE?

I&#39;d like to write a SELECT statement that uses just one test to return columns with no value (null, empty, or all spaces).

I thought this would work:

    SELECT column_name from table_name WHERE column_name NOT LIKE &#39;%_%&#39;;

But this does not work for NULL values.

Of course I can add

    OR column_name IS NULL

and it will work, but I&#39;d like a way that uses a single test.

How to check for null/empty/whitespace values with a single test?

&gt; **Possible Duplicate:**  
&gt; [Why does Oracle 9i treat an empty string as NULL?](https://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null)  

&lt;!-- End of automatically inserted text --&gt;

I have a table in Oracle 10g named `TEMP_TABLE` with only two columns - `id` and `description` just for the sake of demonstration.

The column `id` is a sequence generated primary key of type `NUMBER(35, 0) not null` and the column `DESCRIPTION` is a type of `VARCHAR2(4000) not null`.

The basic table structure in this case would look something like the following.

    +--------------+-----------+---------------+
    |Name          | Null?     | Type          |
    +--------------+-----------+---------------+
    |ID            | NOT NULL  | NUMBER(35)    |
    |DESCRIPTION   | NOT NULL  | VARCHAR2(4000)|
    +--------------+-----------+---------------+

After creating this table, I&#39;m trying to insert the following `INSERT` commands alternatively.

    INSERT INTO temp_table (id, description) VALUES (1, null); -&gt;unsuccessful
    INSERT INTO temp_table (id, description) VALUES (2, &#39;&#39;);   -&gt;unsuccessful

Both of them are unsuccessful as obvious because the `not null` constraint is enforced on the `DESCRIPTION` column.

In both of the cases, Oracle complains

    ORA-01400: cannot insert NULL into (&quot;WAGAFASHIONDB&quot;.&quot;TEMP_TABLE&quot;.&quot;DESCRIPTION&quot;)
 

**An empty string is treated as a `NULL` value in Oracle.**


----------

If I dropped the `not null` constraint on the `DESCRIPTION` column then the basic table structure would look like the following

    +--------------+-----------+---------------+
    |Name          | Null?     | Type          |
    +--------------+-----------+---------------+
    |ID            | NOT NULL  | NUMBER(35)    |
    |DESCRIPTION   |           | VARCHAR2(4000)|
    +--------------+-----------+---------------+

and both of the `INSERT` commands as specified would be successful. They would create two rows one with a `null` value and another with an empty string `&#39;&#39;` in the `DESCRIPTION` column of the `TEMP_TABLE`.

Now, if I issue the following `SELECT` command,

    SELECT * FROM temp_table WHERE description IS NULL;

then it fetches both the rows in which one has a `null` value and the other has an empty string `&#39;&#39;` in the `DESCRIPTION` column.

The following `SELECT` statement however retrieves no rows from the `TEMP_TABLE`

    SELECT * FROM temp_table WHERE description=&#39;&#39;;

It doesn&#39;t even retrieve the row which has an empty string in the `DESCRIPTION` column.


----------


Presumably, it appears that Oracle treats a `null` value and an empty string `&#39;&#39;` differently here which however doesn&#39;t appear to be the case with the `INSERT` statement in which both a `null` value and an empty string `&#39;&#39;` are prevented from being inserted into a column with a `not null` constraint. Why is it so?

null vs empty string in Oracle

I am using Hibernate 3.5.6 with Oracle 10g. I am seeing the below exception during initialization but the application itself is working fine. What is the cause for this exception? and how it can be corrected?

**Exception**  
Disabling contextual LOB creation as `createClob()` method threw error : `java.lang.reflect.InvocationTargetException`

**Info**  
Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
JDBC driver: Oracle JDBC driver, version: 11.1.0.7.0

Disabling contextual LOB creation as createClob() method threw error

I have the table with 1 column and has following data

    Status
    a1
    i
    t
    a2
    a3

I want to display the following result in my select query

    Status| STATUSTEXT
    a1    | Active
    i     | Inactive
    t     | Terminated
    a2    | Active
    a3    | Active

One way I could think was using a Switch When expression in select query

    SELECT
    status,
    CASE status 
    WHEN &#39;a1&#39; THEN &#39;Active&#39;
    WHEN &#39;a2&#39; THEN &#39;Active&#39;
    WHEN &#39;a3&#39; THEN &#39;Active&#39;
    WHEN &#39;i&#39; THEN &#39;Inactive&#39;
    WHEN &#39;t&#39; THEN &#39;Terminated&#39;
    END AS StatusText
    FROM stage.tst


Is there any other way of doing this where I don&#39;t need to write &lt;i&gt;&lt;B&gt;When expression 3 times for Active Status &lt;/B&gt;&lt;/i&gt; and the entire active status can be checked in one single expression?


CASE .. WHEN expression in Oracle SQL

I am using Linux, Oracle10g.
I have created one user called test. and granted create session and select any dictionary permission to the same user.

i also granted sysdba and sysoper roles to the same users.

Now i want to display all the privileges and roles granted to the user. 
I found following query but it shows only create session and select dictionary privileges.

    select privilege 
    from dba_sys_privs 
    where grantee=&#39;SAMPLE&#39; 
    order by 1;

please help to resolve the issue.

Thanks

 

How to find the privileges and roles granted to a user in Oracle?

I would like to know:
How to rename a table column in Oracle 10g?

How to rename a table column in Oracle 10g

How can I get a PL/SQL block to output the results of a `SELECT` statement the same way as if I had done a plain `SELECT`?

For example how to do a `SELECT` like:

    SELECT foo, bar FROM foobar;
 

Hint :

    BEGIN
    SELECT foo, bar FROM foobar;
    END;

doesn&#39;t work.

Is it possible to output a SELECT statement from a PL/SQL block?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. 

Procedure:

    create or replace procedure temp_proc is
    begin
      DBMS_OUTPUT.PUT_LINE(&#39;Test&#39;);
    end

it is created successfully. But when i execute:

`execute temp_proc;`

it shows **ORA-00900: invalid SQL statement** 

So help needed here

How to execute an oracle stored procedure?

After executing this SQL in oracle 10g:

    SELECT SYSDATE, CURRENT_TIMESTAMP  FROM DUAL
I receive this strange output:
![Toad output for query][1]


  [1]: http://i.stack.imgur.com/FiPgB.png

What is cause of the difference in time? 
The server time is equal of SYSDATE value

Different CURRENT_TIMESTAMP and SYSDATE in oracle

I&#39;ve seen some examples in which `decimal` is used in NHibernate projects for mapping to whole number columns in Oracle. Right now I&#39;m using `int` and `long` in my program. 

What are the advantages of `decimal` over `int`/`long`? Does it perform better?

Which .NET data type is best for mapping the NUMBER Oracle data type in NHibernate?

For some reason, people in the past have inserted data without using sequence.NEXTVAL.  So when I go to use sequence.NEXTVAL in order to populate a table, I get a PK violation, since that number is already in use in the table.

How can I update the next value so that it is usable?  Right now, I&#39;m just inserting over and over until it&#39;s successful (`INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)`), and that syncs up the nextval.

Best way to reset an Oracle sequence to the next value in an existing column?

I am getting the error :

     ORA-28000: the account is locked 

**Is this a DB Issue?** When I unlock user account using the command `ALTER USER username ACCOUNT UNLOCK` temporarily it will be OK. Then after some time the same account lock happens again.

The database using is oracle `XE`

**Does anybody else have the same issue?** 





ORA-28000: the account is locked error getting frequently

I have a PL/SQL function with BOOLEAN in parameter:

    function get_something(name in varchar2, ignore_notfound in boolean);

This function is a part of 3rd party tool, I cannot change this.

I would like to use this function inside a SELECT statement like this:

     select get_something(&#39;NAME&#39;, TRUE) from dual;

This does not work, I get this exception:

&gt; ORA-00904: &quot;TRUE&quot;: invalid identifier


As I understand it, keyword `TRUE` is not recognized.

How can I make this work?

How to use BOOLEAN type in SELECT statement

    SET SERVEROUTPUT ON
    DECLARE
		v_student_id NUMBER := &amp;sv_student_id;
		v_section_id NUMBER := 89;
		v_final_grade NUMBER;
		v_letter_grade CHAR(1);
    BEGIN
		SELECT final_grade
		INTO v_final_grade
		FROM enrollment
		WHERE student_id = v_student_id
		AND section_id = v_section_id;
		
		CASE -- outer CASE
			WHEN v_final_grade IS NULL THEN
				DBMS_OUTPUT.PUT_LINE (&#39;There is no final grade.&#39;);
			ELSE
				CASE -- inner CASE
					WHEN v_final_grade &gt;= 90 THEN v_letter_grade := &#39;A&#39;;
					WHEN v_final_grade &gt;= 80 THEN v_letter_grade := &#39;B&#39;;
					WHEN v_final_grade &gt;= 70 THEN v_letter_grade := &#39;C&#39;;
					WHEN v_final_grade &gt;= 60 THEN v_letter_grade := &#39;D&#39;;
					ELSE v_letter_grade := &#39;F&#39;;
				END CASE;
				
				-- control resumes here after inner CASE terminates
				DBMS_OUTPUT.PUT_LINE (&#39;Letter grade is: &#39;||v_letter_grade);
		END CASE;
		-- control resumes here after outer CASE terminates
    END;

the above code I have taken from the book *&quot;Oracle PL/SQL by Example, 4th Edition 2009&quot;* my problem is when I enter a `student_id` not present in the table it returns me the following error

&lt;pre&gt;
Error report: ORA-01403: no data found
ORA-06512: at line 7
01403. 00000 -  &quot;no data found&quot;
*Cause:    
*Action:
&lt;/pre&gt;

but according to the book it should have returned a null value and then follow the case flow.

All Oracle10g Solutions on Gang of Coders

Total of 22 Oracle10g Solutions