OraFAQ Forum: SQL & PL/SQL » Help required in DYNAMIC SQL coding (Merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Help required in DYNAMIC SQL coding (Merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help required in DYNAMIC SQL coding (Merged) [message #200877]

Wed, 01 November 2006 14:24

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Hello,

I am facing a typical issue with one of the procedures in my package in PL/SQL.
This procedure uses Dynamic SQL to fetch values in runtime.
This procedure;s functionality is as below:
It inserts data into a master table by selecting values from another table.
In runtime it fetches values like
1. from_table name (table from which the values to be inserted are fetched
2. to_table name (table into which the values are inserted)
3. p_pdegree parameter the parallel degree parameter
4. p_begin_date, p_end_date (the specific dates between which the data is to be loaded)

Here SQL_STMT1 can be build successfully
SQL_STMT2 can also be build successfully
My Problem is that when I try to concatenate SQL_STMT1 and SQL_STMT2 into FINAL_STMT, then these 2 strings are not getting concatenated. When running the procedure, it does not display any error message, but simply I do not get any result, and the execution terminates.
I have tested the SQL statements seperately, with the date ranges as desired, in that case it works well, so no issues with the SQL coding....that I am confident....
But I am not able to get the resultant FINAL_STMT string.

The code for the procedure described above is as below:

      PROCEDURE scls_wk_rebuild (
      p_pdegree      NUMBER,
      p_begin_date   NUMBER,
      p_end_date     NUMBER
   )
   IS
p_fiscal_week_end_date     NUMBER;
to_table VARCHAR2 (300)   := 'agg.sale_inv_org_ven_sclass_week';
from_table1 VARCHAR(300)   := 'agg.sale_inv_org_ven_sclass_day';
sql_stmt1      VARCHAR2 (3000);
sql_stmt2      VARCHAR2 (5000);
final_stmt     VARCHAR2 (32760);
p_error_code   NUMBER;
p_error_msg    VARCHAR2 (255);
v_begin_date   NUMBER           := p_begin_date;
v_end_date     NUMBER           := p_end_date;
v_debug        VARCHAR2 (1)     := 'Y';
   BEGIN
      v_begin_date := 20060402;
      v_end_date := 20060408;

sql_stmt1 :=
            'INSERT INTO '
         || to_table
         || ' ( 
  organization_hierarchy_id
    ,product_hierarchy_id
    ,fiscal_year_week
    ,vendor_id
    ,net_sales_rtl
    ,net_sales_units
    ,net_sales_cst
    ,oh_units
    ,oh_rtl
    ,oh_cst
    ,oo_units
    ,oo_rtl
    ,oo_cst
    ,it_units
    ,it_rtl
    ,it_cst
    ,receipt_units
    ,receipt_rtl
    ,receipt_cst
    ,merch_sale_units
    ,merch_sale_rtl
    ,merch_sale_cst
    ,pos_md_rtl
    ,md_units
    ,md_rtl
    ,return_units
    ,return_rtl
    ,oh_clr_units
    ,oh_clr_rtl
    ,oh_clr_cst
    ,rtv_units
    ,rtv_rtl
    ,rtv_cst
    ,shrink_units
    ,shrink_rtl
    ,shrink_cst
    ,inv_adj_units
    ,inv_adj_rtl
    ,inv_adj_cst
    ,ship_trf_units
    ,ship_trf_rtl
    ,ship_trf_cst
    ,emp_net_sales_rtl
    ,emp_net_sales_units
    ,emp_md_rtl
    ,emp_return_rtl
    ,emp_return_units
    ,reg_net_sales_rtl
    ,reg_net_sales_units
    ,reg_md_rtl
    ,reg_return_rtl
    ,reg_return_units
    ,clr_net_sales_rtl
    ,clr_net_sales_units
    ,clr_md_rtl
    ,clr_return_units
    ,clr_return_rtl
    ,promotion_rtl
    ,other_discount_net_sales
    ,other_discount_units
    ,vendor_freight_rtl
    ,emp_net_sales_cst
    ,reg_net_sales_cst
    ,clr_net_sales_cst
    ,date_added
    ,added_by
    ,date_last_modified
    ,last_modified_by
    ) ';

         sql_stmt2 :=
            ' SELECT /*+ index(A), parallel(a,'
         || p_pdegree
         || ' )*/
organization_hierarchy_id
       ,product_hierarchy_id
       ,p_fiscal_week_end_date
       ,vendor_id
       ,SUM(net_sales_rtl)net_sales_rtl
       ,SUM(net_sales_units)net_sales_units
       ,SUM(net_sales_cst)net_sales_cst
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_units, 0))oh_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_rtl  , 0))oh_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_cst  , 0))oh_cst
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_units, 0))oo_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_rtl  , 0))oo_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_cst  , 0))oo_cst
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_units, 0))it_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_rtl  , 0))it_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_cst  , 0))it_cst
       ,SUM(receipt_units)receipt_units
       ,SUM(receipt_rtl)receipt_rtl
       ,SUM(receipt_cst)receipt_cst
       ,SUM(merch_sale_units)merch_sale_units
       ,SUM(merch_sale_rtl)merch_sale_rtl
       ,SUM(merch_sale_cst)merch_sale_cst
       ,SUM(pos_md_rtl)pos_md_rtl
       ,SUM(md_units)md_units
       ,SUM(md_rtl)md_rtl
       ,SUM(return_units)return_units
       ,SUM(return_rtl)return_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_clr_units,0))oh_clr_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_clr_rtl  ,0))oh_clr_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_clr_cst  ,0))oh_clr_cst
       ,SUM(rtv_units)rtv_units
       ,SUM(rtv_rtl)rtv_rtl
       ,SUM(rtv_cst)rtv_cst
       ,SUM(shrink_units)shrink_units
       ,SUM(shrink_rtl)shrink_rtl
       ,SUM(shrink_cst)shrink_cst
       ,SUM(inv_adj_units)inv_adj_units
       ,SUM(inv_adj_rtl)inv_adj_rtl
       ,SUM(inv_adj_cst)inv_adj_cst
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, ship_trf_units,0))ship_trf_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, ship_trf_rtl  ,0))ship_trf_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, ship_trf_cst  ,0))ship_trf_cst
       ,SUM(emp_net_sales_rtl)emp_net_sales_rtl
       ,SUM(emp_net_sales_units)emp_net_sales_units
       ,SUM(emp_md_rtl)emp_md_rtl
       ,SUM(emp_return_rtl)emp_return_rtl
       ,SUM(emp_return_units)emp_return_units
       ,SUM(reg_net_sales_rtl)reg_net_sales_rtl
       ,SUM(reg_net_sales_units)reg_net_sales_units
       ,SUM(reg_md_rtl)reg_md_rtl
       ,SUM(reg_return_rtl)reg_return_rtl
       ,SUM(reg_return_units)reg_return_units
       ,SUM(clr_net_sales_rtl)clr_net_sales_rtl
       ,SUM(clr_net_sales_units)clr_net_sales_units
       ,SUM(clr_md_rtl)clr_md_rtl
       ,SUM(clr_return_units)clr_return_units
       ,SUM(clr_return_rtl)clr_return_rtl
       ,SUM(promotion_rtl)promotion_rtl
       ,SUM(other_discount_net_sales)other_discount_net_sales
       ,SUM(other_discount_units)other_discount_units
       ,SUM(vendor_freight_rtl)vendor_freight_rtl
       ,SUM(emp_net_sales_cst)emp_net_sales_cst
       ,SUM(reg_net_sales_cst)reg_net_sales_cst
       ,SUM(clr_net_sales_cst)clr_net_sales_cst
       ,SYSDATE
       ,SUBSTR(USER,1,8)
       ,SYSDATE
       ,SUBSTR(USER,1,8)
      FROM '
         || from_table1
         || ' a WHERE a.date_id between '
         || v_begin_date
         || ' and '
         || v_end_date
         || ' GROUP BY organization_hierarchy_id
         ,product_hierarchy_id
         ,vendor_id; ';


final_stmt := sql_stmt1 || sql_stmt2;

      IF v_debug = 'Y'
      THEN
	     DBMS_OUTPUT.ENABLE (1000000);
             DBMS_OUTPUT.put_line (final_stmt);
      ELSE

         EXECUTE IMMEDIATE final_stmt;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_code := SQLCODE;
         p_error_msg := SQLERRM;
         DBMS_OUTPUT.put_line ('Error occured while loading' || SQLERRM);
         RAISE;
         COMMIT;
   END scls_wk_rebuild;
END;
/

Please help me out, it's really urgent...
Thanks a lot in advance.

Regards,
Suranjita

code tags added my moderator

[Updated on: Wed, 01 November 2006 16:30] by Moderator

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200878 is a reply to message #200877]

Wed, 01 November 2006 14:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Please help me out, it's really urgent...
With advice, you can have it good, fast or cheap. Pick any two.

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200881 is a reply to message #200878]

Wed, 01 November 2006 14:34

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Hello,

I require the desired result by executing my procedure that is I require to build the FINAL_STMT string so that it executes and gives the desired result.

Please help me out urgently....

Thanks a lot in advance.

Regards,
Suranjita

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200882 is a reply to message #200877]

Wed, 01 November 2006 14:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

EXECUTE IMMEDIATE sql_stmt1;
EXECUTE IMMEDIATE sql_stmt2;

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200886 is a reply to message #200877]

Wed, 01 November 2006 15:10

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Hello,

when I try to do

EXECUTE IMMEDIATE sql_stmt1;
EXECUTE IMMEDIATE sql_stmt2;

I get the following error:

ORA-00926 "Missing Values Keyword"

Please help me out...

Thanks a lot in advance.

Regards,
Suranjita

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200890 is a reply to message #200877]

Wed, 01 November 2006 15:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What part of "ORA-00926 "Missing Values Keyword" do you NOT understand?
The INSERT statement has invalid syntax.

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200892 is a reply to message #200890]

Wed, 01 November 2006 15:33

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Which part of INSERT statement has error?
Normally this particular INSERT statement ran fine...so where is the error?
Can you please point out...
Thanks in advance

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200899 is a reply to message #200892]

Wed, 01 November 2006 16:42

andrew again
Messages: 2577
Registered: March 2000

Senior Member

1.) comment out your execption handling - it isn't helping. Not sure why you have a commit there rather than rollback.
2.) if you're using sqlplus, you need to set serveroutput on <size>; instead of just dbms_output.enable();
3.) to print long strings (pre-10g) see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:146412348066
4.) print some debug like length(final_stmt) and sql%rowcount after the insert
5.) commit after the insert

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #200900 is a reply to message #200892]

Wed, 01 November 2006 16:45

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You should not execute the two "statements" separately as anacedent suggested. Each "statement" is only half of the insert statement. If you only execute the first part with the insert, without the second part with the selected values, then of course you get an error that you are missing the values clause. You should concatenate them together as you were doing and execute only the final_stmt.

You have an extra "END;" at the end of your procedure that does not belong there.

Did you "SET SERVEROUTPUT ON" before executing the procedure? Dbms_output.enable affects the size of the buffer, but you still have to "SET SERVEROUTPUT ON" or you get nothing.

The way you have it now, presumably for testing, your input parameters are being replaced with hard-coded values and you are only outputing your sql statement, not executing it. You should then be able to copy and paste the outputted insert statement, eliminating the blank lines, to test the validity of the statement and debug it if necessary.

Please see the successful compilation and execution of your procedure below.

SCOTT@10gXE> CREATE OR REPLACE PROCEDURE scls_wk_rebuild (
  2  	   p_pdegree	  NUMBER,
  3  	   p_begin_date   NUMBER,
  4  	   p_end_date	  NUMBER
  5  	)
  6  IS
  7  p_fiscal_week_end_date	NUMBER;
  8  to_table VARCHAR2 (300)   := 'agg.sale_inv_org_ven_sclass_week';
  9  from_table1 VARCHAR(300)	:= 'agg.sale_inv_org_ven_sclass_day';
 10  sql_stmt1	    VARCHAR2 (3000);
 11  sql_stmt2	    VARCHAR2 (5000);
 12  final_stmt     VARCHAR2 (32760);
 13  p_error_code   NUMBER;
 14  p_error_msg    VARCHAR2 (255);
 15  v_begin_date   NUMBER	     := p_begin_date;
 16  v_end_date     NUMBER	     := p_end_date;
 17  v_debug	    VARCHAR2 (1)     := 'Y';
 18  	BEGIN
 19  	   v_begin_date := 20060402;
 20  	   v_end_date := 20060408;
 21  
 22  sql_stmt1 :=
 23  		 'INSERT INTO '
 24  	      || to_table
 25  	      || ' (
 26    organization_hierarchy_id
 27  	 ,product_hierarchy_id
 28  	 ,fiscal_year_week
 29  	 ,vendor_id
 30  	 ,net_sales_rtl
 31  	 ,net_sales_units
 32  	 ,net_sales_cst
 33  	 ,oh_units
 34  	 ,oh_rtl
 35  	 ,oh_cst
 36  	 ,oo_units
 37  	 ,oo_rtl
 38  	 ,oo_cst
 39  	 ,it_units
 40  	 ,it_rtl
 41  	 ,it_cst
 42  	 ,receipt_units
 43  	 ,receipt_rtl
 44  	 ,receipt_cst
 45  	 ,merch_sale_units
 46  	 ,merch_sale_rtl
 47  	 ,merch_sale_cst
 48  	 ,pos_md_rtl
 49  	 ,md_units
 50  	 ,md_rtl
 51  	 ,return_units
 52  	 ,return_rtl
 53  	 ,oh_clr_units
 54  	 ,oh_clr_rtl
 55  	 ,oh_clr_cst
 56  	 ,rtv_units
 57  	 ,rtv_rtl
 58  	 ,rtv_cst
 59  	 ,shrink_units
 60  	 ,shrink_rtl
 61  	 ,shrink_cst
 62  	 ,inv_adj_units
 63  	 ,inv_adj_rtl
 64  	 ,inv_adj_cst
 65  	 ,ship_trf_units
 66  	 ,ship_trf_rtl
 67  	 ,ship_trf_cst
 68  	 ,emp_net_sales_rtl
 69  	 ,emp_net_sales_units
 70  	 ,emp_md_rtl
 71  	 ,emp_return_rtl
 72  	 ,emp_return_units
 73  	 ,reg_net_sales_rtl
 74  	 ,reg_net_sales_units
 75  	 ,reg_md_rtl
 76  	 ,reg_return_rtl
 77  	 ,reg_return_units
 78  	 ,clr_net_sales_rtl
 79  	 ,clr_net_sales_units
 80  	 ,clr_md_rtl
 81  	 ,clr_return_units
 82  	 ,clr_return_rtl
 83  	 ,promotion_rtl
 84  	 ,other_discount_net_sales
 85  	 ,other_discount_units
 86  	 ,vendor_freight_rtl
 87  	 ,emp_net_sales_cst
 88  	 ,reg_net_sales_cst
 89  	 ,clr_net_sales_cst
 90  	 ,date_added
 91  	 ,added_by
 92  	 ,date_last_modified
 93  	 ,last_modified_by
 94  	 ) ';
 95  
 96  	      sql_stmt2 :=
 97  		 ' SELECT /*+ index(A), parallel(a,'
 98  	      || p_pdegree
 99  	      || ' )*/
100  organization_hierarchy_id
101  	    ,product_hierarchy_id
102  	    ,p_fiscal_week_end_date
103  	    ,vendor_id
104  	    ,SUM(net_sales_rtl)net_sales_rtl
105  	    ,SUM(net_sales_units)net_sales_units
106  	    ,SUM(net_sales_cst)net_sales_cst
107  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_units, 0))oh_units
108  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_rtl  , 0))oh_rtl
109  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_cst  , 0))oh_cst
110  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_units, 0))oo_units
111  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_rtl  , 0))oo_rtl
112  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_cst  , 0))oo_cst
113  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_units, 0))it_units
114  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_rtl  , 0))it_rtl
115  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_cst  , 0))it_cst
116  	    ,SUM(receipt_units)receipt_units
117  	    ,SUM(receipt_rtl)receipt_rtl
118  	    ,SUM(receipt_cst)receipt_cst
119  	    ,SUM(merch_sale_units)merch_sale_units
120  	    ,SUM(merch_sale_rtl)merch_sale_rtl
121  	    ,SUM(merch_sale_cst)merch_sale_cst
122  	    ,SUM(pos_md_rtl)pos_md_rtl
123  	    ,SUM(md_units)md_units
124  	    ,SUM(md_rtl)md_rtl
125  	    ,SUM(return_units)return_units
126  	    ,SUM(return_rtl)return_rtl
127  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_clr_units,0))oh_clr_units
128  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_clr_rtl	,0))oh_clr_rtl
129  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_clr_cst	,0))oh_clr_cst
130  	    ,SUM(rtv_units)rtv_units
131  	    ,SUM(rtv_rtl)rtv_rtl
132  	    ,SUM(rtv_cst)rtv_cst
133  	    ,SUM(shrink_units)shrink_units
134  	    ,SUM(shrink_rtl)shrink_rtl
135  	    ,SUM(shrink_cst)shrink_cst
136  	    ,SUM(inv_adj_units)inv_adj_units
137  	    ,SUM(inv_adj_rtl)inv_adj_rtl
138  	    ,SUM(inv_adj_cst)inv_adj_cst
139  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, ship_trf_units,0))ship_trf_units
140  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, ship_trf_rtl  ,0))ship_trf_rtl
141  ,SUM(DECODE(a.date_id, p_fiscal_week_end_date, ship_trf_cst  ,0))ship_trf_cst
142  	    ,SUM(emp_net_sales_rtl)emp_net_sales_rtl
143  	    ,SUM(emp_net_sales_units)emp_net_sales_units
144  	    ,SUM(emp_md_rtl)emp_md_rtl
145  	    ,SUM(emp_return_rtl)emp_return_rtl
146  	    ,SUM(emp_return_units)emp_return_units
147  	    ,SUM(reg_net_sales_rtl)reg_net_sales_rtl
148  	    ,SUM(reg_net_sales_units)reg_net_sales_units
149  	    ,SUM(reg_md_rtl)reg_md_rtl
150  	    ,SUM(reg_return_rtl)reg_return_rtl
151  	    ,SUM(reg_return_units)reg_return_units
152  	    ,SUM(clr_net_sales_rtl)clr_net_sales_rtl
153  	    ,SUM(clr_net_sales_units)clr_net_sales_units
154  	    ,SUM(clr_md_rtl)clr_md_rtl
155  	    ,SUM(clr_return_units)clr_return_units
156  	    ,SUM(clr_return_rtl)clr_return_rtl
157  	    ,SUM(promotion_rtl)promotion_rtl
158  	    ,SUM(other_discount_net_sales)other_discount_net_sales
159  	    ,SUM(other_discount_units)other_discount_units
160  	    ,SUM(vendor_freight_rtl)vendor_freight_rtl
161  	    ,SUM(emp_net_sales_cst)emp_net_sales_cst
162  	    ,SUM(reg_net_sales_cst)reg_net_sales_cst
163  	    ,SUM(clr_net_sales_cst)clr_net_sales_cst
164  	    ,SYSDATE
165  	    ,SUBSTR(USER,1,8)
166  	    ,SYSDATE
167  	    ,SUBSTR(USER,1,8)
168  	   FROM '
169  	      || from_table1
170  	      || ' a WHERE a.date_id between '
171  	      || v_begin_date
172  	      || ' and '
173  	      || v_end_date
174  	      || ' GROUP BY organization_hierarchy_id
175  	      ,product_hierarchy_id
176  	      ,vendor_id; ';
177  
178  
179  final_stmt := sql_stmt1 || sql_stmt2;
180  
181  	   IF v_debug = 'Y'
182  	   THEN
183  		  DBMS_OUTPUT.ENABLE (1000000);
184  		  DBMS_OUTPUT.put_line (final_stmt);
185  	   ELSE
186  
187  	      EXECUTE IMMEDIATE final_stmt;
188  	   END IF;
189  	EXCEPTION
190  	   WHEN OTHERS
191  	   THEN
192  	      p_error_code := SQLCODE;
193  	      p_error_msg := SQLERRM;
194  	      DBMS_OUTPUT.put_line ('Error occured while loading' || SQLERRM);
195  	      RAISE;
196  	      COMMIT;
197  END scls_wk_rebuild;
198  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> SET SERVEROUTPUT ON
SCOTT@10gXE> EXECUTE scls_wk_rebuild (1, 1, 1)
INSERT INTO agg.sale_inv_org_ven_sclass_week (
  organization_hierarchy_id

,product_hierarchy_id
    ,fiscal_year_week
    ,vendor_id
    ,net_sales_rtl
    ,net_sales_units

,net_sales_cst
    ,oh_units
    ,oh_rtl
    ,oh_cst
    ,oo_units
    ,oo_rtl
    ,oo_cst

,it_units
    ,it_rtl
    ,it_cst
    ,receipt_units
    ,receipt_rtl
    ,receipt_cst

,merch_sale_units
    ,merch_sale_rtl
    ,merch_sale_cst
    ,pos_md_rtl
    ,md_units
    ,md_rtl

,return_units
    ,return_rtl
    ,oh_clr_units
    ,oh_clr_rtl
    ,oh_clr_cst
    ,rtv_units

,rtv_rtl
    ,rtv_cst
    ,shrink_units
    ,shrink_rtl
    ,shrink_cst
    ,inv_adj_units

,inv_adj_rtl
    ,inv_adj_cst
    ,ship_trf_units
    ,ship_trf_rtl
    ,ship_trf_cst

,emp_net_sales_rtl
    ,emp_net_sales_units
    ,emp_md_rtl
    ,emp_return_rtl

,emp_return_units
    ,reg_net_sales_rtl
    ,reg_net_sales_units
    ,reg_md_rtl

,reg_return_rtl
    ,reg_return_units
    ,clr_net_sales_rtl
    ,clr_net_sales_units

,clr_md_rtl
    ,clr_return_units
    ,clr_return_rtl
    ,promotion_rtl

,other_discount_net_sales
    ,other_discount_units
    ,vendor_freight_rtl
    ,emp_net_sales_cst

,reg_net_sales_cst
    ,clr_net_sales_cst
    ,date_added
    ,added_by
    ,date_last_modified

,last_modified_by
    )  SELECT /*+ index(A), parallel(a,1 )*/
organization_hierarchy_id

,product_hierarchy_id
       ,p_fiscal_week_end_date
       ,vendor_id

,SUM(net_sales_rtl)net_sales_rtl
       ,SUM(net_sales_units)net_sales_units

,SUM(net_sales_cst)net_sales_cst
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_units,
0))oh_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_rtl  ,
0))oh_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_cst  , 0))oh_cst
,SUM(DECODE(a.date_id,
p_fiscal_week_end_date, oo_units, 0))oo_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_rtl
, 0))oo_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oo_cst  ,
0))oo_cst
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_units,
0))it_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_rtl  ,
0))it_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, it_cst  , 0))it_cst

,SUM(receipt_units)receipt_units
       ,SUM(receipt_rtl)receipt_rtl

,SUM(receipt_cst)receipt_cst
       ,SUM(merch_sale_units)merch_sale_units

,SUM(merch_sale_rtl)merch_sale_rtl
       ,SUM(merch_sale_cst)merch_sale_cst

,SUM(pos_md_rtl)pos_md_rtl
       ,SUM(md_units)md_units
       ,SUM(md_rtl)md_rtl

,SUM(return_units)return_units
       ,SUM(return_rtl)return_rtl
,SUM(DECODE(a.date_id,
p_fiscal_week_end_date, oh_clr_units,0))oh_clr_units
,SUM(DECODE(a.date_id, p_fiscal_week_end_date,
oh_clr_rtl  ,0))oh_clr_rtl
,SUM(DECODE(a.date_id, p_fiscal_week_end_date, oh_clr_cst
,0))oh_clr_cst
       ,SUM(rtv_units)rtv_units
       ,SUM(rtv_rtl)rtv_rtl

,SUM(rtv_cst)rtv_cst
       ,SUM(shrink_units)shrink_units
       ,SUM(shrink_rtl)shrink_rtl

,SUM(shrink_cst)shrink_cst
       ,SUM(inv_adj_units)inv_adj_units

,SUM(inv_adj_rtl)inv_adj_rtl
       ,SUM(inv_adj_cst)inv_adj_cst
,SUM(DECODE(a.date_id,
p_fiscal_week_end_date, ship_trf_units,0))ship_trf_units
,SUM(DECODE(a.date_id,
p_fiscal_week_end_date, ship_trf_rtl  ,0))ship_trf_rtl
,SUM(DECODE(a.date_id,
p_fiscal_week_end_date, ship_trf_cst  ,0))ship_trf_cst

,SUM(emp_net_sales_rtl)emp_net_sales_rtl
       ,SUM(emp_net_sales_units)emp_net_sales_units

,SUM(emp_md_rtl)emp_md_rtl
       ,SUM(emp_return_rtl)emp_return_rtl

,SUM(emp_return_units)emp_return_units
       ,SUM(reg_net_sales_rtl)reg_net_sales_rtl

,SUM(reg_net_sales_units)reg_net_sales_units
       ,SUM(reg_md_rtl)reg_md_rtl

,SUM(reg_return_rtl)reg_return_rtl
       ,SUM(reg_return_units)reg_return_units

,SUM(clr_net_sales_rtl)clr_net_sales_rtl
       ,SUM(clr_net_sales_units)clr_net_sales_units

,SUM(clr_md_rtl)clr_md_rtl
       ,SUM(clr_return_units)clr_return_units

,SUM(clr_return_rtl)clr_return_rtl
       ,SUM(promotion_rtl)promotion_rtl

,SUM(other_discount_net_sales)other_discount_net_sales

,SUM(other_discount_units)other_discount_units
       ,SUM(vendor_freight_rtl)vendor_freight_rtl

,SUM(emp_net_sales_cst)emp_net_sales_cst
       ,SUM(reg_net_sales_cst)reg_net_sales_cst

,SUM(clr_net_sales_cst)clr_net_sales_cst
       ,SYSDATE
       ,SUBSTR(USER,1,8)
       ,SYSDATE

,SUBSTR(USER,1,8)
      FROM agg.sale_inv_org_ven_sclass_day a WHERE a.date_id between 20060402 and
20060408 GROUP BY organization_hierarchy_id
         ,product_hierarchy_id
         ,vendor_id;

PL/SQL procedure successfully completed.

SCOTT@10gXE>

Report message to a moderator

Re: Urgent Help required in DYNAMIC SQL coding inside a PL/SQL procedure. [message #201113 is a reply to message #200877]

Thu, 02 November 2006 11:03

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Hello,

Thank You Barbara, this helped me a lot.....
I have a basic question to clarify.
how to implement multiuple row select query for inserting data into a table using Dynamic SQL and EXECUTE IMMEDIATE.
For example

sql_stmt1:='insert into' ||to_table||
'(
col1
,col2
,......
) ';
sql_stmt2:='select /*index(a), parallel(a,'||p_pdegree||')*/
col1
,col2
.......
from' ||from_table1||','||from_table2||
'where ......; ';

Here in the bove example the SELECT statement returns multiple rows which are to be inserted in the "to_table"
Here the variables we have are "to_table", parallel degree (variable name is p_pdegree, "from_table1", "from_table2".
How to implement the above using DYNAMIC SQL and EXECUTE IMMEDIATE.
Please help me out regarding the above.
It is urgent.

Thanks a lot in advance.

Regards,
Suranjita

Report message to a moderator

Usage of EXECUTE IMMEDIATE command in DYNAMIC SQL, please help me out. [message #201137 is a reply to message #200877]

Thu, 02 November 2006 14:43

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Hello,

While executing the following procedure:

CREATE OR REPLACE PROCEDURE data_entry
(
to_table VARCHAR,
from_table VARCHAR,
p_pdegree NUMBER,
p_id NUMBER
)
IS
sql_stmt1 VARCHAR2(100);
sql_stmt2 VARCHAR2(100);
final_stmt VARCHAR2(500);

BEGIN

sql_stmt1:= 'INSERT INTO '|| to_table||' (id ,name) ';
sql_stmt2 :='SELECT /*+ parallel(a,'||p_pdegree||' )*/ id,name FROM '||from_table|| ' where id ='||p_id||';' ;

FINAL_STMT := SQL_STMT1 || SQL_STMT2;

dbms_output.put_line (FINAL_STMT);

execute immediate final_stmt;

COMMIT;
END;
/

I am getting the following error:

ORA-00911 : invalid character
ORA-06512: at "AGG_PROCESS.DATA_ENTRY", line 27
ORA-06512: at line 13

My DBMS_OUTPUT.PUT_LINE works fine displaying the desired output string. My query also runs fine when I run it seperately in an SQL Editor.

But the EXECUTE IMMEDIATE fails giving the above error.

Please help me out in this regard. This is really urgent.

Thanks a lot in advance.

Regards,
Suranjita

Report message to a moderator

Re: Usage of EXECUTE IMMEDIATE command in DYNAMIC SQL, please help me out. [message #201147 is a reply to message #201137]

Thu, 02 November 2006 16:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Dynamically created statement shouldn't have a semi-colon at the end. Here:

where id ='||p_id||';' ;

should be

where id ='||p_id;

Report message to a moderator

Re: Usage of EXECUTE IMMEDIATE command in DYNAMIC SQL, please help me out. [message #201324 is a reply to message #201147]

Fri, 03 November 2006 09:07

suranjita_hajra_7
Messages: 20
Registered: November 2006
Location: Pittsburgh, USA

Junior Member

Thanks a lot....It WORKED....!!!!!!!! It helped me a lot...

Report message to a moderator

Re: Usage of EXECUTE IMMEDIATE command in DYNAMIC SQL, please help me out. [message #201326 is a reply to message #201147]

Fri, 03 November 2006 09:08

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Ideally it would be

where id = :b1

and youd add 'USING p_id' to the execute immediate. This way you get to use bind variables a bit, and minimise the load on your Db.

Report message to a moderator

Previous Topic:	Age related problem (merged)
Next Topic:	Insert into a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 17:06:17 CST 2024