Dynamic SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Dynamic SQL

  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Dynamic SQL

    Hi All,
    Please help me in pointing the mistake in the following procedure. It compiles and meets the condition but does not perform any thing.
    CREATE OR REPLACE PROCEDURE SP_DEL_ARCHIVE_TARGET ( archtable VARCHAR2)

    IS
    arch_count Number;
    pversion Number;
    tname_arch VARCHAR2(200):=archtable||' p,D_version V';
    Condition Varchar2(200):='AND D_VERSION.VERSION_STATUS=ARCHIVE AND D_VERSION.ARCHIVE_STATUS IS NULL';
    tCommitLimit NUMBER := 10000;
    tCommitCount NUMBER := 0;
    TYPE tCursorType IS REF CURSOR;
    tDeleteCursor tCursorType;
    tRowID ROWID;
    tsql varchar2(2000);

    --PROCEDURE: SP_DEL_ARCHIVE
    --
    --ENVIRONMENT/LANGUAGE: PL/SQL


    --
    -----------------------------------------------------------------------
    --PURPOSE: This procedure deletes data from ARCHIVE tables
    -- before loading the production data to this table to clean-up table
    -- ensure that data loaded by a prevous imcomplete runs is cleaned-up.
    --
    -----------------------------------------------------------------------
    --VERSION HISTORY:
    --------------------------------------------------------------------------
    -- Version 1.0.0
    -- Date: Dec. 16, 2002
    -- Author: Vikram Jit Sharma
    -----------------------------------------------------------------------------

    cursor C1 is SELECT VERSION_KEY FROM D_VERSION
    WHERE D_VERSION.VERSION_STATUS='ARCHIVE' and
    D_VERSION.ARCHIVE_STATUS is NULL;


    BEGIN

    Open C1;
    Loop
    FETCH C1 into pversion;
    exit when C1%notfound;


    tsql:= 'select count (*) from '||tname_arch||' WHERE p.version_key = V.version_key '||CONDITION;

    EXECUTE IMMEDIATE tsql INTO arch_count;
    IF arch_count > 0 THEN


    OPEN tDeleteCursor FOR

    'SELECT ROWID FROM ' || archtable || ' WHERE VERSION_KEY =:tversion_key' USING pversion;

    LOOP
    FETCH tDeleteCursor INTO tRowID;
    EXIT WHEN tDeleteCursor%NOTFOUND;

    EXECUTE IMMEDIATE 'DELETE ' || archtable || ' WHERE ROWID = :vrowid' USING tRowID ;

    IF tCommitCount > tCommitLimit
    THEN
    COMMIT;
    tCommitCount :=0;
    ELSE
    tCommitCount := tCommitCount + 1;
    END IF;
    END LOOP;
    END IF;
    CLOSE tDeleteCursor;

    COMMIT;
    end Loop;
    Close C1;
    EXCEPTION
    WHEN OTHERS THEN
    NULL ;
    END;
    /
    Regards and Thanks,
    Vikram

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How do you expect to find any problems when you catch all errors and ignore them? Take out the exception clause -- your use of it here is bad programming practice.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2002
    Posts
    2,645

    Re: Dynamic SQL

    CREATE OR REPLACE PROCEDURE SP_DEL_ARCHIVE_TARGET ( archtable VARCHAR2)
    First, you should specify what type of parameter you are passing in. IN is the default, so if you don't specify IN, then IN is what it is. Challenge: find any book on PL/SQL that doesn't recommend always specifying the type of parameter. However, that is not your error here.

    IS


    Convention is to use AS, not IS, but this is not the error either

    EXECUTE IMMEDIATE 'DELETE ' || archtable || ' WHERE ROWID = :vrowid' USING tRowID ;


    How is :vrowid being recognized by the execute immediate command? Doesn't that make :vrowid a string value, and not a bind variable?

  4. #4
    Join Date
    Dec 2002
    Posts
    3

    Dynamic SQL

    Hi Slimdave and stecal,
    Thanks for your reply. The procedure is being called by informatica mapping where the parameter is passed.
    The error is in the following line

    EXECUTE IMMEDIATE tsql INTO arch_count;

    Any comments and suggestions.
    Regards and Thanks,
    Vikram

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What error is being raised?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    Executing a single row select with a bind variable, fetching into a single define variable:

    Code:
    DECLARE   
      str varchar2(200);     
      val varchar2(20);     
      ret varchar2(20); 
    BEGIN    
      str := 'select msg from msg where msg = :b1';     
      val := 'Hello';     
      EXECUTE IMMEDIATE str INTO ret USING val;     
      dbms_output.put_line('Value fetched from table: '||ret); 
    END;
    Is tsql a number or varchar2? You declare it as a varchar2, but do a select count(*) - which would be a number.
    Last edited by stecal; 12-17-2002 at 01:57 PM.

  7. #7
    Join Date
    Dec 2002
    Posts
    3

    DYNAMIC SQL

    Hi Slimdave and Stecal,
    Thanks for your help. There was no syntex error. The following was the error.

    old
    Condition Varchar2(200):='AND D_VERSION.VERSION_STATUS=ARCHIVE AND D_VERSION.ARCHIVE_STATUS IS NULL';

    New
    Condition Varchar2(200):='AND D_VERSION.VERSION_STATUS=''ARCHIVE'' AND D_VERSION.ARCHIVE_STATUS IS NULL';

    Thanks slimdave for pointing the exception error.
    Regards and Thanks,
    Vikram

Posting Permissions

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



Click Here to Expand Forum to Full Width