OraFAQ Forum: SQL & PL/SQL » Does this exist in sql

Home » SQL & PL/SQL » SQL & PL/SQL » Does this exist in sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Does this exist in sql [message #248079]

Thu, 28 June 2007 00:39

srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA

Member

Hi gurus,

Does something like this exist in Oracle SQL.

SELECT * INTO Persons_backup
FROM Persons

I found this particular thing

Quote:

http://www.w3schools.com/sql/sql_select_into.asp

I tried this in SQL*plus,but it shows the following error.

SELECT * INTO emp1 from emp
              *
ERROR at line 1:
ORA-00905: missing keyword

Please advice.

Thanks in advance...

Srini...

Report message to a moderator

Re: Does this exist in sql [message #248086 is a reply to message #248079]

Thu, 28 June 2007 01:00

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Simply avoid the INTO clause to make it SQL statement.
Or call it in the PL/SQL anonymous block.
Hard to tell as I do not know what do you want to do with persons_backup variable.
Also SELECT * is not a bad practice, you should list all columns you want.

Report message to a moderator

Re: Does this exist in sql [message #248087 is a reply to message #248079]

Thu, 28 June 2007 01:01

Frank
Messages: 7901
Registered: March 2000

Senior Member

This is not Oracle syntax.
In Oracle, select into is used in plsql, to store the result of a query in a variable.
The described functionality would be done in Oracle by using a
create table <newtab> as select ....

Report message to a moderator

Re: Does this exist in sql [message #248088 is a reply to message #248079]

Thu, 28 June 2007 01:04

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

you will select into in PLSQL constructs.

A simple construct using select into can be,

declare
v1 emp.ename%type;
v2 emp.sal%type;
Begin
select ename,sal into v1,v2 from emp where ename= 'SCOTT';
dbms_output.put_line(v1||' '||v2);
end;

Report message to a moderator

Re: Does this exist in sql [message #248091 is a reply to message #248079]

Thu, 28 June 2007 01:05

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

You have 2 choices with Oracle:
1. The backup table does NOT exists, so you create it and backup your data (Frank already mentioned it):

CREATE TABLE person_backup  NOLOGGING AS SELECT * FROM person;

2. The empty backup table already defined.

INSERT INTO person_backup SELECT * FROM person;

HTH.
Michael

Report message to a moderator

Re: Does this exist in sql [message #248352 is a reply to message #248091]

Thu, 28 June 2007 13:17

srinivasocp
Messages: 91
Registered: December 2005
Location: INDIA

Member

Thanks a lot everyone.Just got confused with the select statements mentioned in the W3schools website.

http://www.w3schools.com/sql/sql_select_into.asp

Regards
Srini...

Report message to a moderator

Previous Topic:	help with clob output
Next Topic:	getting the values from a cursor on different tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 13:08:08 CST 2024