Create an Oracle Stored Procedure Code from my Insert Into DML Statement

try like:

CREATE OR REPLACE PROCEDURE yourStoreProc
IS
BEGIN
  
INSERT INTO TABLE1
(NAME, NAME_ID, Q004)

SELECT X.NAME, X.NAME_ID,
CASE WHEN X.Q004 IS NOT NULL THEN 1 ELSE 0 END Q004
FROM TABLE2 X;

commit;

END;
/

Select allOpen in new window

I didn't this was going to work but I tried it anyways and it didn't work.

Thanks

any error detected? currently I don't have Oracle with me

It compiles but it's saved as an invalid procedure code.

Then there shoud be some (error) messages!

You can query the user_errrors view or after issuing the create procedure command:  show errors

Any reason you want a simple insert-as-select to be a procedure?

I run this on a monthly basis and I figured I will include this on a package, but right now i'm testing it as a procedure code. Also, as an fyi, I'm still pretty new to stored procedure.

So, I got an error by running select * from user_errors and got the following...
1) "Table or view does not exist" and I believe it's referencing "TABLE2" from my example above.
2) SQL Statement ignored

Also as an fyi.. I tried "Select * FROM TABLE2" and I have access to this table for sure.

If the procedure owner is not the table owner, your user needs explicit grants on table2 to the procedure owner.

Say user1 owns table2 and you are creating the procedure as user2:
as user1 or a privileged account:  grant select on user1.table2 to user2;

Try the procedure again.

Hi slightwv,

I'm not following your suggestions. So here's the situation.. So, my idea is to:
1) Truncate the data from Table1 - which i have a stored procedure to do...
2) Insert new data into Table1 from Table2. **need help in**
3) I currently run a steps 1 and 2 above with a ".sql" code with "TRUNCATE TABLE Table1" statement and then I run the "INSERT INTO" statement, and it works fine....

And now I want to put this on a procedure code so i can run both (truncate and insert) in a package. Not sure if there's a better way but right this is what I can think of...

My current rights...
1) I created "Table1"
2) Table2 I have read only

To perform DDL inside PL/SQL you need to (truncate is considered DDL):
execute immediate 'truncate table table1';

To select from table2 inside a procedure, you need explicit grants on the table not through a ROLE.  There are many papers out there on the "why" so I'll not cover it here.

As for a "better way", I don't know what you are trying to accomplish with the new table.

Difference between table1 and table2 is "CASE WHEN X.Q004 IS NOT NULL THEN 1 ELSE 0 END Q004" where the new field is a data type number where previously was a text.. I did this because there's about 100 of this that I had to convert into data type number. Then my second query would perform additional calculation on the new fields. So, I have a stand alone ".sql" query that does the job (includes "truncate statement", "insert statement", then "update statements". Also, I need use the same updated table for other reports.

So back to my original question and your suggestion as "explicit grant"... I'm the procedure owner and owner of "Table1", but I'm not the owner of "Table2". Any suggestions how I can accomplish?

Have the DBA or owner of table2 issue the grant I posted.

Still not sure why you need a new table just to perform some case statements.

Thanks slightwv for the suggestions...

I'm hoping other experts can have an alternative suggestion...  this seems like harder that i thought. again, I already have the codes that worked but I'm trying to put it in a proc code and this is where I'm having issues.

If you can query table2 from a command prompt and you get  "Table or view does not exist" from inside the procedure, it is a grant issue.

There isn't an alternate suggestion.

The suggestion I hinted at:  Don't create table1 in the first place, then you don't need to do the insert inside a procedure.  The problem magically goes away.

If you want to "mask" a ton of CASE statements by creating table1, create a view based on a select of table2.  Still no need for table1.

Let me read up on creating a view.
So first, create a table view of table2, and then use the table view in my procedure code?

I tried to create "CREATE VIEW TABLE2_v2
AS
Select field1, field2, etc..
from table2...

And I'm getting insufficient privileges...

>>then use the table view in my procedure code?

What procedure code?

From my understanding you are only creating a procedure to load a table you created.  You only created the new table so 'transform' data from another table.

If you create a view, you don't need to load anything so you don't need a procedure.

create or replace view my_view as select * from dual;

Nothing to insert into there.  doing a "select * from my_view" is the same as "select * from dual".

>>And I'm getting insufficient privileges...

Then you need grants.  I assume you were given privs to create a procedure and a table, so now you need grants to create a view.

Can I back up and ask what is the actual problem you are trying to solve by creating table1 in the first place?

"Can I back up and ask what is the actual problem..."  
I created table1 for following reasons:
1) I added additional fields, about just 10 additional fields.
2) I then update these fields base on the "ton of case statement", which are now of numeric type. Basically, from about 100 fields that are now converted into number, I update the 4 additional fields by adding 25 fields at a time.
3) Once the 4 fields are updated, I then update another "new" field from table1 and sum up the 4 fields.
4) from here I update other fields, with other case statements, base on the sum of step 3.
5) Reason I do it this way is that I find that by doing multiple passes, it is a lot faster than putting it all in giant sql query.
6) In short, I created Table1, where I append/insert data from table2. The insert statement doesn't have a where clauses etc, its just take data from table2 and insert into table1. Where table1 I have additional fields.
7) I now want to put this on a Stored Procedure and currently... I have the truncate part in a stored procedure. But I'm having issues with converting my "Insert Into" statement to a Oracle Stored Procedure... hope that make sense..

I would look a Global Temporary Tables.  You create these once and use them over and over again.  Only the session that inserts into them can see the data in them and they clean themselves up automatically when the session ends.  There are many papers out there on them.

As far as your procedure:  It is a grant issue.  I can keep repeating it but so far everything you have provided says it is a grant issue.  Without the grant, no select from table2 inside the procedure.

If the performance of a view isn't acceptable, maybe look at a materialized view.  Then all the work is done for you behind the scenes.

transform data like that is costly

as slightwv said, create a view

create view sample as 
select a, 
  case 
    when a = 'A00' then 1
    when a = 'B11' then 2
  else 999 end b
  other_columns
from tableXYZ;

Select allOpen in new window

you can also create views which work on views
sometimes, helps to simplify items

create view sample_grouped as 
select b, count(*) as count_b
from sample
group by b;

Select allOpen in new window