Converting sql code with select and insert statement into procedure

Question

Could anybody help me to convert the following sql code into procedure? I've read some www sources and made a conclusion (may be) that it should be package?

TRUNCATE TABLE MY_SCHEME.MAYA;

INSERT INTO MY_SCHEME.MAYA (ID_TEST,
                                  IQ,
                                  DATE_,
                                  COMMENT1)
   SELECT   ID_TEST,
            IQ,
            DATE_,
            COMMENT1
     FROM   MY_SCHEME.STAGE_MAYA
     where STAGE_MAYA.ID_TEST=(select max (ID_TEST) from MY_SCHEME.STAGE_MAYA)

Thanks a lot!

Put CREATE OR REPLACE PROCEDURE MAY12_PROC IS BEGIN EXECUTE IMMEDIATE ' before your TRUNCATE statement. Put ' between the MAYA and ; at the end of the TRUNCATE statement. Add ; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' ' || SQLERRM); END MAY12_PROC; after your last line. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Apr 27, 2012 at 11:05
well said. I like the way you "fixed" the syntax error of missing ";". — Roger Cornejo
– Roger Cornejo, Commented Apr 27, 2012 at 11:42

Ollie · Accepted Answer · 2012-04-27 12:56:44Z

2

You could use either a PROCEDURE or a FUNCTION depending upon your ultimate requirements.

A procedure would get the job done but you'd have to then query the MAYA table to see how many records you inserted.

i.e.

CREATE OR REPLACE
PROCEDURE maya_insert
IS
BEGIN
   -- Truncate the maya table
   EXECUTE IMMEDIATE 'TRUNCATE TABLE MY_SCHEME.MAYA';
   --
   -- Insert records into maya
   INSERT INTO MY_SCHEME.MAYA 
   (
    ID_TEST,
    IQ,
    DATE_,
    COMMENT1
   )
   SELECT ID_TEST,
          IQ,
          DATE_,
          COMMENT1
     FROM MY_SCHEME.STAGE_MAYA
    WHERE STAGE_MAYA.ID_TEST = (SELECT MAX(ID_TEST)
                                  FROM MY_SCHEME.STAGE_MAYA);
EXCEPTION
   WHEN others
   THEN
      DBMS_OUTPUT.put_line('MAYA_INSERT error: '||sqlerrm);
END maya_insert;

You may want to put a COMMIT; in there too unless you are committing the transaction outside of this procedure, if you do add the COMMIT; then also add a ROLLBACK; in the exception section. Be aware though that the TRUNCATE statement cannot be rolled back as it's DDL. If you need the ability to roll it back then you'll have to use the slower DELETE command which is DML.

If you needed to know how many records were inserted into MAYA then use a function:

i.e.

CREATE OR REPLACE
FUNCTION maya_insert
   RETURN NUMBER
IS
BEGIN
   -- Truncate the maya table
   EXECUTE IMMEDIATE 'TRUNCATE TABLE MY_SCHEME.MAYA';
   --
   -- Insert records into maya
   INSERT INTO MY_SCHEME.MAYA 
   (
    ID_TEST,
    IQ,
    DATE_,
    COMMENT1
   )
   SELECT ID_TEST,
          IQ,
          DATE_,
          COMMENT1
     FROM MY_SCHEME.STAGE_MAYA
    WHERE STAGE_MAYA.ID_TEST = (SELECT MAX(ID_TEST)
                                  FROM MY_SCHEME.STAGE_MAYA);

   -- Return the number of records inserted
   RETURN SQL%ROWCOUNT;
EXCEPTION
   WHEN others
   THEN
      DBMS_OUTPUT.put_line('MAYA_INSERT error: '||sqlerrm);
      RETURN -1;
END maya_insert;

Here you will get the count of records inserted and if there was an error then you'll get -1 returned. See above for my comments on COMMIT; and ROLLBACK; being added if you need them.

As regards a package. Packages are used to group logically related functions procedures and other processing together in the database. As you only have one procedure or function there is no need to wrap it in a package at this stage. See: http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/09_packs.htm#362

Hope it helps...

edited Apr 27, 2012 at 12:56

answered Apr 27, 2012 at 12:46

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

May12 Over a year ago

May I ask one more question? The idea of creation a procedure was that i will call procedure from unix batch file (now i don't know how). On other hand i can execute my sql script from unix batch file: i just place my_sql.sql file near batch file and call it from it (code from .sh file: sqlplus userid=myscheme/0611@TEST_DB 10.33.19.13/test/DATA_UPLOAD/mysql_script.sql). What is the right way: call stored procedure or call external .sql file?

Ollie Over a year ago

If you can call SQL*Plus then once you have called it simply run EXECUTE maya_insert; to run your stored procedure. You can put the execute statement in a .sql file and call that.

buzznfrog · Accepted Answer · 2012-04-27 08:53:47Z

0

A package can contain multiple procedures, functions, variables, and definitions. You issue grants on the package not on the functions it contains. A major difference is that when the code of a function in a package needs to change, you can replace the package body (create or replace syntax) and this will not cause invalidation of dependant code. A change to a function outside a package will always cause invalidation of dependant code.

In your case you'll need a procedure

answered Apr 27, 2012 at 8:53

buzznfrog

1004 bronze badges

Collectives™ on Stack Overflow

Converting sql code with select and insert statement into procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related