Results 1 to 2 of 2

Thread: pl/sql script

  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: pl/sql script

    hie all, i have a question here... i have a pl/sql procedure code and its not working but if i convert it into normal sql it works fine.. any idea about this

    begin
    execute immediate 'create table e01_ers_purc_ord_to_dealer_ers (
    select distributor_id, week_no, order_status, nvl(sum(amount_uploaded),0) "AMOUNT_UPLOADED_ERS", nvl(count(amount_uploaded),0) "COUNT_AMOUNT_UPLOADED_ERS"
    from trap_ra_ers_dist
    where order_status = 'ACT'
    group by distributor_id, week_no, order_status)';
    end;
    /

    i think its because of the where clause and definitely there is another way to write this... anyone ??

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Parenthesis are not needed (but it is OK if you have them); you miss the 'AS' keyword while creating table using the SELECT statement; there should be 2 single quotes around ''ACT'' (not double quotes ", but two single ones '') because otherwise Oracle assumes that the quoted string which started with 'create table ... ends at ... where order_status = '.

    Therefore, such a PL/SQL block might work:
    Code:
    BEGIN
       EXECUTE IMMEDIATE 
         'CREATE TABLE e01_ers_purc_ord_to_dealer_ers 
    	  AS
          SELECT distributor_id, week_no, order_status, 
    	         NVL(SUM(amount_uploaded),0) "AMOUNT_UPLOADED_ERS", 
    			 NVL(COUNT(amount_uploaded),0) "COUNT_AMOUNT_UPLOADED_ERS"
          FROM trap_ra_ers_dist
          WHERE order_status = ''ACT''
          GROUP BY distributor_id, week_no, order_status';
    END;
    /

Posting Permissions

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