Home » SQL & PL/SQL » SQL & PL/SQL » Help Required in Procedure
Help Required in Procedure [message #228279] Mon, 02 April 2007 06:54 Go to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
Hello ,

I am facing some problem in procedure compilation. Please help me to solve this error. I am new to Dynamic SQL and hence facing this issue.Please refer below

SQL> create or replace procedure p1
as
begin
execute immediate 'create table s_contact_1
as
select * from s_contact';
execute immediate 'truncate table s_contact';
insert into s_contact(select * from s_contact_1 where rownum<=5000);
end;
/

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3 PL/SQL: SQL Statement ignored
9/39 PL/SQL: ORA-00942: table or view does not exist
SQL> spool off

Kindly tell me how can i overcome this issue. Any kind of help will be highly appreciated.

Thanks in advance.
Re: Help Required in Procedure [message #228281 is a reply to message #228279] Mon, 02 April 2007 07:12 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Post Removed.

[Updated on: Mon, 02 April 2007 11:32]

Report message to a moderator

Re: Help Required in Procedure [message #228282 is a reply to message #228279] Mon, 02 April 2007 07:23 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You are trying to insert into s_contact_1 before it even exists from the compilers perspective.
Re: Help Required in Procedure [message #228292 is a reply to message #228282] Mon, 02 April 2007 07:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is exactly why you should do DML once. Do it outside your procedures.
Repeat after me:
Oracle is NOT SQLServer/Sybase
Oracle is NOT SQLServer/Sybase
Oracle is NOT SQLServer/Sybase
Oracle is NOT SQLServer/Sybase
Oracle is NOT SQLServer/Sybase

[Updated on: Mon, 02 April 2007 07:56]

Report message to a moderator

Re: Help Required in Procedure [message #228308 is a reply to message #228279] Mon, 02 April 2007 09:02 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Also I notice that you are using a CTAS to build your new table and then you are truncating to throw away the rows. There is a little trick to grab the structure of a table, but not it's data.


create table my_table
as select *
from the_other_table
where 1=2;

Re: Help Required in Procedure [message #228342 is a reply to message #228279] Mon, 02 April 2007 11:09 Go to previous messageGo to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
yeah i know this. But this is my requirement. Could you kindly tell me how to modify my codes so that the procedure could run successfuly.

Kindly help.

[Updated on: Mon, 02 April 2007 11:11]

Report message to a moderator

Re: Help Required in Procedure [message #228348 is a reply to message #228342] Mon, 02 April 2007 11:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Could you kindly tell me how to modify my codes so that the procedure could run successfuly.


If the requirement is to create the table in the procedure and then use it in the same procedure ( without coding EVERYTHING in dynamic SQL ) it's not possible. Change the requirements.
Re: Help Required in Procedure [message #228361 is a reply to message #228342] Mon, 02 April 2007 12:49 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
It seems ultimately you just want to keep 5000 row in the table, or stated differently "Delete any row which would make the count over 5000."

delete from s_contact where rownum <= 
 (select a - b from (select count(*) a from s_contact),
 (select count(*) b from s_contact where rownum <= 5000)
)
/

Previous Topic: function based index
Next Topic: which is execute first
Goto Forum:
  


Current Time: Sun Dec 11 02:22:24 CST 2016

Total time taken to generate the page: 0.08805 seconds