Home » SQL & PL/SQL » SQL & PL/SQL » Create and Select Statement inside a function
Create and Select Statement inside a function [message #357613] Thu, 06 November 2008 00:59 Go to next message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Hi,
I need a help on the below requirement:
In the database there is NO table 1 but there IS table 2
In a procedure we have to create a table 1 as select item from table 2. For all the values inserted into table 1 we need to do some validation and return the item_name. I created a procedure as follows:
Create or replace procedure <proc name> as (item_name out varchar2)
cursor c1 as select <col names>
from <table name>,<table 1>
where <conditions>;
begin
execute immediate 'Drop table table1';
execute immediate 'create table table1 as select item from table 2';

For r_c1 in c1 loop
validate the r_c1.item;
item_name := item_name ||' , '|| r_c1.item;
end loop;
end;

If I compile this program I get an error message that table or view doesnot exist. I guess the error occur because the table name is not already available in the data base. Can anyone suggest a best way so that I can have both the create statement and select statement in the same procedure?


Re: Create and Select Statement inside a function [message #357622 is a reply to message #357613] Thu, 06 November 2008 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can anyone suggest a best way

Surely to not use such stuff.
What is the business need?

Regards
Michel
Re: Create and Select Statement inside a function [message #357633 is a reply to message #357613] Thu, 06 November 2008 01:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do not DROP and CREATE "table_1"; create it once (in SQL*Plus, before running any other procedure), and - if necessary - make it empty (DELETE or TRUNCATE) in the procedure.

Or, you might consider use of a Global temporary table (if possible).
Re: Create and Select Statement inside a function [message #357638 is a reply to message #357633] Thu, 06 November 2008 02:05 Go to previous message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Thank You for the reply.
Previous Topic: send email to multiple reciepient using utl_smtp
Next Topic: recover tables (urgent)
Goto Forum:
  


Current Time: Thu Dec 08 16:19:25 CST 2016

Total time taken to generate the page: 0.16566 seconds