Creating a procedure to create a table 1

[Welshbird]

I'm trying to create a table within a procedure. This is a table I'm going to want to re-create on a regular basis which is why I thought a procedure would be the best way to go.

My code looks like this:

create or replace procedure proc_locale
as
begin
begin
  execute immediate 'drop table Locale';
exception
  when others then
    if sqlcode = -942 then
      null;  -- table does not exist
    else
      raise;
    end if;
end;
CREATE TABLE Locale
as
SELECT
 d.ONEKEY_TYPE_ETA as DestETA,
 d.ONEKEY_PHARMACY_CODE as DestCODE,
 d.Address1 as DestAdd,
 d.Address2 as DestAdd2,
 d.Address3 as DestAdd3,
 d.OUTER_POSTCODE as DestOUTER,
 d.INNER_POSTCODE as DestINNER,
 d.GEO_X as DX,
 d.GEO_Y as DY,
 o.ONEKEY_TYPE_ETA as OrigETA,
 o.ONEKEY_PHARMACY_CODE as OrigCODE,
 o.Address1 as OrigAdd,
 o.Address2 as OrigAdd2,
 o.Address3 as OrigAdd3,
 o.OUTER_POSTCODE as OrigOUTER,
 o.INNER_POSTCODE as OrigINNER,
 o.GEO_X as OX,
 o.GEO_Y as OY,
 (SQRT ((POWER ((o.GEO_X - d.GEO_X),2)) + (POWER ((o.GEO_Y - d.GEO_Y),2)))/1000)*0.621 as Miles,
 (SQRT ((POWER ((o.GEO_X - d.GEO_X),2)) + (POWER ((o.GEO_Y - d.GEO_Y),2)))/1000) as Km
FROM
 dol.pharmacy d, dol.pharmacy o
WHERE
 (SQRT ((POWER ((o.GEO_X - d.GEO_X),2)) + (POWER((o.GEO_Y - d.GEO_Y),2)) )/1000)*0.621 < 10
AND
 (d.Status = '3')
AND
 (o.Status = '3')
AND
 (d.TypETA = N'RCH')
AND
 (o.TypETA = N'SUR')
AND
 (o.ONEKEY_PHARMACY_CODE = o.MANAGING_INSTITUTION)
AND
 (o.GEO_X + o.GEO_Y <> '0')
AND
 (o.GEO_X is not null)
AND
 (o.GEO_Y is not null)
end proc_locale;

Any help would be really hugely appreciated.

Thanks in advance....

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Dagon]

Are you saying there is a problem with this or just asking if it is a good idea ? Wouldn't you need to do the "create table" dynamically in the same way as the "drop table" ?

 

[Welshbird]

Both I guess! I'm really not sure of the way to go with it...

When I run this I get a 'procedure creted with compliation errors' message, but I don't know why.

I think what I really want to end up with is a list of SQL queries that will run as a package (like I used to do with a DTS package in MS-SQL). I (probably foolishly) thought that I could 'save' the queries as procedures and then have a package of all the procedures. I guess I'm really just trying to automate it all a little bit.

Any advice whatsoever would be really appreciated!

Thanks

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Welshbird]

If I'm trying to create these tables in order to select bits of them and then analyse them would I be better to be creating views instead?

(I wish I knew what I was trying to do here! And also that I had someone in my office to bounce ideas off. Really appreciate you guys being there to help in thier place...!)

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Dagon]

I don't think there are any hard and fast answers. You just do whatever works and gives reasonable performance. You could use views just as easily. The only drawback of the view is that it would have to execute the query every time you did a select from it. If you are selecting from the table you've created only once, it view would serve equally well. If you are selecting lots of times, it might be better to create it as a table.

As to why your procedure doesn't compile, you need to execute the create table dynamically. This means that all the quoted text inside the statement will need two sets of quotes so that Oracle can differentiate them from a string terminating quote:

string_variable := 
'CREATE TABLE Locale
as
SELECT
 d.ONEKEY_TYPE_ETA as DestETA,
 d.ONEKEY_PHARMACY_CODE as DestCODE,
 d.Address1 as DestAdd,
 d.Address2 as DestAdd2,
 d.Address3 as DestAdd3,
 d.OUTER_POSTCODE as DestOUTER,
 d.INNER_POSTCODE as DestINNER,
 d.GEO_X as DX,
 d.GEO_Y as DY,
 o.ONEKEY_TYPE_ETA as OrigETA,
 o.ONEKEY_PHARMACY_CODE as OrigCODE,
 o.Address1 as OrigAdd,
 o.Address2 as OrigAdd2,
 o.Address3 as OrigAdd3,
 o.OUTER_POSTCODE as OrigOUTER,
 o.INNER_POSTCODE as OrigINNER,
 o.GEO_X as OX,
 o.GEO_Y as OY,
 (SQRT ((POWER ((o.GEO_X - d.GEO_X),2)) + (POWER ((o.GEO_Y - d.GEO_Y),2)))/1000)*0.621 as Miles,
 (SQRT ((POWER ((o.GEO_X - d.GEO_X),2)) + (POWER ((o.GEO_Y - d.GEO_Y),2)))/1000) as Km
FROM
 dol.pharmacy d, dol.pharmacy o
WHERE
 (SQRT ((POWER ((o.GEO_X - d.GEO_X),2)) + (POWER((o.GEO_Y - d.GEO_Y),2)) )/1000)*0.621 < 10
AND
 (d.Status = ''3'')  <-------------
AND
 (o.Status = ''3'')
AND
 (d.TypETA = N''RCH'')
AND
 (o.TypETA = N''SUR'')
AND
 (o.ONEKEY_PHARMACY_CODE = o.MANAGING_INSTITUTION)
AND
 (o.GEO_X + o.GEO_Y <> ''0'')
AND
 (o.GEO_X is not null)
AND
 (o.GEO_Y is not null)';
execute immediately string_variable:
[\code]

I am a bit confused by what you are trying to do with:

AND
 (d.TypETA = N'RCH')
AND
 (o.TypETA = N'SUR')

This doesn't make any sense.

 

[Welshbird]

OK - I missed that - thank you.

The odd bit of

AND
 (d.TypETA = N'RCH')
AND
 (o.TypETA = N'SUR')

is because the original table has two types of data - and I'm just calcuating the distance between each type of institution.

I know hte underlying query works though cos I've used it before as a 'create table as' query.

Thanks so much for your help - will go and see the difference in performance from views to tables and change my create procedure stuff too!



Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[sem]

BTW you may consider creating materialized view (aka snapshot)
To use quotes in string literal just double them (don't use double quote symbols):

'(d.TypETA = N''RCH'')'

Regards, Dima

http://seminihin.narod.ru

 

[Welshbird]

Wow - I didn't realise that option existed.

I can already see countless uses for it.

Thank you for the help - and have a star on me!



Fi.

"The question should be, is it worth trying to do, not can it be done"