CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
TontoDiablo
TontoDiablo

Reputation: 43

Combining Multiple Statements in a Stored Procedure

I'm new to Oracle, what I'm trying to achieve I could in SQL but I'm having a tough time in doing it in Oracle.

So within a Stored Procedure, I'm trying to truncate a table, then insert values, lastly run a SELECT statement against the table.

Here is what I have but it doesn't work, when I run this script, it runs with no error but it seems it only goes through the first (TRUNCATE) statement and that's it.

I would like it create the Store Procedure (which it does) and then show me the contents of the table from the SELECT statement.

CREATE OR REPLACE procedure MYSTOREDPROCEDURE is
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE MYTABLE';
INSERT INTO MYTABLE
(COL1,
COL2,
COL3)

SELECT COL1, COL2, COL3 FROM MYOTHERTABLE;
end ;
/

SELECT * FROM MYTABLE

END MYSTOREDPROCEDURE;

Upvotes: 0

Views: 2633

Answers (3)

Greg
Greg

Reputation: 233

My DBA won't let me have TRUNCATE privileges even though truncate removes high water marks compared to delete MYTABLE; Your code will fail without these privileges. Both @Ben and @Bob Jarvis provide sound advice with the missing INTO and nicer form. One addition to Bob Jarvis' answer is that you need to start the command with SET SERVEROUTPUT ON SIZE 100000;. Otherwise, his nicely crafted error message will not be displayed. The serveroutput command is used for DBMS_OUTPUT and only lasts as long as your database session lasts.

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

I suspect you intended to execute the SELECT * FROM MYTABLE after the procedure was compiled and executed. The above could be restrucured as:

CREATE OR REPLACE procedure MYSTOREDPROCEDURE is
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE MYTABLE';

  INSERT INTO MYTABLE (COL1, COL2, COL3)
    SELECT COL1, COL2, COL3
      FROM MYOTHERTABLE;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in MYSTOREDPROCEDURE : ' || SQLCODE || ' - ' || SQLERRM);
    RAISE;
END MYSTOREDPROCEDURE;
/

EXECUTE MYSTOREDPROCEDURE
/

SELECT * FROM MYTABLE
/

Share and enjoy.

Upvotes: 0

Ben
Ben

Reputation: 52883

For clarification SQL is a language implmemented by many RDBMS including SQL Server, PostgreSQL etc. If you're doing this in Oracle you are using SQL. However, most RDBMS have also added a procedural extension to SQL such as T-SQL (SQL Server), pgPL/SQL (PostgreSQL) and PL/SQL (Oracle). In this case you're attempting to use PL/SQL.

From what you're attempting to do I assume you're used to SQL Server and temporary tables. It is less common and less necessary to use temporary tables in Oracle.

Firstly, EXECUTE IMMEDIATE 'TRUNCATE TABLE MYTABLE';. It should rarely be necessary to perform DDL in a stored procedure in Oracle; it would normally be indicative of a flaw in the data-model. In this case it appears as though you're using an actual table as a temporary table. I wouldn't do this. If you need a temporary table use a global temporary table.

Secondly, SELECT * FROM MYTABLE. You can't do this in PL/SQL. If you need to select some data you have to use SELECT <columns> INTO <variables> FROM .... If you do this it won't show you the contents of the table.

From your description of what you're attempting you only need to do the following:

SELECT COL1, COL2, COL3 FROM MYOTHERTABLE;

There is no need for PL/SQL (stored procedures) at all.

Upvotes: 2

Related Questions