stored procedures that execute from a select

Database

stored procedures that execute from a select

Hi All,

 

I am trying to create a stored procedure which will create two tables and then delete those data which is coming from above created tables. I am giving an overview how i am trying to create.

create procedure <ABC>

begin

create table <A> AS

(

query which select all duplicate records from staging table.

) with data;

 

create table <B> AS

(

query which select distinct records from table <A>

)with data;

 

delete from <staging_table1>

where var1 in (Select records from table<B>);

delete from <staging_table2>

where var1 in (Select records from table<B>);

 

end;

I have tried creating all types of tables but unfortunately it didn't work for me..and when i m trying to comment our <DELETE> statement SP is getting complied.

Therefore, my doubt whether we can use both DDL and DML statement in one SP or not.

Kindly advice!

Thanks in advance.

5 REPLIES
Supporter

Re: stored procedures that execute from a select

Hi Bhavini,

 

Yes you can use both DDL and DML in a SP.

Your template looks fine to me.

May be you can share more details on what compilation errors you are getting.

 

When you compile a procedure you may get warnings which arent errors.

 

Thanks,

Rohan Sawant

Re: stored procedures that execute from a select

I am getting below errors  and warning: 

SPL5000:W(L25), E(3807):Object '<databasename>.Temp1' does not exist.
SPL1027:E(L33), Missing/Invalid SQL statement'E(3807):Object '<databasename>.Temp2' does not exist.'.
SPL1027:E(L41), Missing/Invalid SQL statement'E(3807):Object '<databasename>.Temp2' does not exist.'.

 

Please advice.

Supporter

Re: stored procedures that execute from a select

Hi Bhavini,

 

The error is because the tables are created when the procedure runs and while compiling the compiler evaluates the drop statement it's not able to find the table.

 

SOLUTION: So just for compilation you may create the tables externally and then compile the procedure. It should successfully compile and then again drop the tables externally.

 

 

Suggestion for improvement: As a good practice add a continue handler to drop the tables if already present

 

Thanks,

Rohan Sawant

Re: stored procedures that execute from a select

Hi Rohan,

Actually my goal is to automate this process..that's why i am creating a table , doing rest of the stuff and droppping at the end.

Exception handler is already is there..i have just given for what i am facing issue.

Is there any other way so that i can implement the same logic.Please advice.

 

Regards,

Bhavini.

Supporter

Re: stored procedures that execute from a select

Hi Bhavini,

As mentioned in my previous post. Just for compiling the procedure -
1. Create the 2 tables externally
2. Compile the procedure (it should be successful)
3. Drop the tables again

Thanks,
Rohan Sawant