Results 1 to 2 of 2

Thread: PL/SQL Help

  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: PL/SQL Help

    I have this script that it supposed to copy data from one location (&1) to another (&2). I need to check to see if the &2 location exists first. If not, then I need to bail. This script is not working and I don't know why.

    Code:
    set echo on
    set feedback on
    set termout on
    set verify on
    
    spool &3
    
    WHENEVER SQLERROR CONTINUE
    
    --
    -- Drop table if it exists. It should not exist, but just in case
    --
    
    DROP TABLE sku_temp;
    
    COMMIT;
    
    
    WHENEVER SQLERROR EXIT SQL.SQLCODE
    
    declare
    
    	new_loc		varchar2(10)	:= '';
    	
    begin
    	
    	SELECT loc 
    	  INTO new_loc
    	  FROM loc 
    	 WHERE loc = '&2';
    
      -- new location exists, so it's OK to proceed. If not, then bail
    	
    	IF new_loc = '&2' THEN
    
              --
              -- Create temp table
              --
              
              CREATE TABLE stsc.sku_temp      AS SELECT * from sku      WHERE ROWNUM < 1;
              
              COMMIT;
              
              
              -----------------------------
              -- Clone SKU
              -----------------------------
              
              INSERT INTO stsc.sku_temp
                 SELECT *
                   FROM sku
                  WHERE loc = '&1';
              
              COMMIT;
              
              
              UPDATE stsc.sku_temp
                 SET loc = '&2';
                 
              COMMIT;
              
              
              INSERT INTO stsc.sku
                 SELECT *
                   FROM stsc.sku_temp;
              
              COMMIT;
                        
              --
              -- Drop temp table
              --
              
              DROP TABLE stsc.sku_temp;
              
              COMMIT;
        
    	END IF;
    	
    END;
    
    	     
    EXIT

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    DDL cannot be executed in-line in PL/SQL, so in the anonymous block you'll need to use:
    Code:
      EXECUTE IMMEDIATE ('CREATE TABLE stsc.sku_temp      AS SELECT * from sku      WHERE ROWNUM < 1);
    Also, there's an implied COMMIT; after any DDL, so you don't need it after your CREATE TABLE statements. And, instead of using a temporary table as you've defined, you might want to look into GLOBAL TEMPORARY TABLES.

    That being said, you could just rewrite all of this as:
    Code:
      INSERT INTO stsc.sku (f1, f2, loc)
      SELECT field1, field2, '&2'
      FROM loc 
      WHERE loc = '&2';
    It won't insert anything if no records qualify, and you avoid an intermediate table.

    -Chuck
    Last edited by chuck_forbes; 11-04-05 at 19:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •