Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql problem (oracle 10g)
dynamic sql problem [message #360302] Thu, 20 November 2008 06:10 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Hi
I m creating a dynamic string to execute in which values are coming from procedure's input parameter. Its not working for obvious reason . can anybody suggest a workaround.
following is the code
begin
 
       for i in 1..40 loop
    v_sql :=
     '  
       insert into CASH_DEPOSITE_INDEX_ACCTNG
       select
          i_sub_system_flg'||i||' ,
          i_sub_system'||i||' ,
          i_dept_to_book_flg'||i||' ,
          i_dept_to_book'||i||' ,
          i_db_cr_division_flg'||i||' ,
          i_db_cr_division'||i||' ,
          i_acct_title_class_flg'||i||' ,
          i_acct_title_clsfctn'||i||' ,
          i_db_cr_code_flg'||i||' ,
          i_db_cr_code'||i||' ,
          i_code_flag'||i||' ,
          i_code'||i||' ,
          i_money_amt_flg'||i||' ,
          i_money_amt'||i||' ,
          i_atm_machine_flg'||i||' ,
          i_atm_machine'||i||' ,
          i_bank_no_flg'||i||' ,
          i_bank_no'||i||' ,
          i_finance_inst_code_flg'||i||' ,
          i_finance_inst_code'||i||' ,
          i_bank_acct_no_flg'||i||' ,
          i_bank_acct_no'||i|| '             
       from dual  where i_sub_system_flg'||i||' <> ''0''
             AND i_dept_to_book_flg'||i||' <> ''0''
             AND i_db_cr_division_flg'||i||' <> ''0''
             AND i_acct_title_class_flg'||i||' <> ''0''
             AND i_db_cr_code_flg'||i||' <> ''0''
             AND i_money_amt_flg'||i||' <> ''0''
      ';
  execute immediate v_sql;
     
    end loop;
    end;


Regards,
Navneet
Re: dynamic sql problem [message #360306 is a reply to message #360302] Thu, 20 November 2008 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not obvious, because this is the first time we've seen what you're trying to do.
I'll assume that it's not a syntax or typo error.

I ssupect that you've got 40 different versions of each of these variables, and you want to insert them as 40 distinct rows.
The correct solution would be to have a different design where these were 40 rows in a collection.
However, you' didn't go down that route.

I can't think of a way to avoid the pain on this one - Dynamic SQL can't see the variables in the environment it's called from, so you're going to have to specify which variables you want it to use at some point, either by using them as a bind variable, or by copying them into the code explicitly.

Re: dynamic sql problem [message #360307 is a reply to message #360306] Thu, 20 November 2008 06:26 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Thanks JRowbottom,
Yes indeed i m getting 40 diffrent set of variables and want to insert 40 rows.it was looking very tedius and dirty to write 40 diffrent insert scripts for the similar thing in procedure.

Is there any way so that dynamic sql can see the variables in its environment? i m still hopefull somebody will comeup with some idea.Smile

Regards,
Navneet

Re: dynamic sql problem [message #360514 is a reply to message #360302] Fri, 21 November 2008 04:28 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
Its not working for obvious reason


What is the outcome of its execution Or what is the error you are getting ?
Any way display the variable v_sql and see what happens if you try to execute separately.

Smile
Rajuvan.
Re: dynamic sql problem [message #360545 is a reply to message #360514] Fri, 21 November 2008 05:43 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rajavu1 wrote on Fri, 21 November 2008 11:28
What is the outcome of its execution Or what is the error you are getting ?

It fails as DUAL table contains only DUMMY columns and all variables are out of the dynamic SQL scope.
It would only be possible if parameters would be stored in collections, as mentioned by JRowbottom.

But, I wonder, that when declaring/passing 40*22 parameters, the original poster minds 40 INSERT statements.

[Edit: just chosen better word for failure]

[Updated on: Fri, 21 November 2008 05:44]

Report message to a moderator

Re: dynamic sql problem [message #360552 is a reply to message #360545] Fri, 21 November 2008 05:54 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
I know its around 900 input parameters in my procedure which is quite a wrong way of coding.but I can't help it as i m getting these variables as single MQ message and application which is sending this linear message is out of my priveleges.

Flyboy , definitely i was concerned abt those many input variables but that is not in my hand. I tried to improve the process on which i have some control ,the same reason for posting this question to you esteemed guests.

Regards,
Navneet
Re: dynamic sql problem [message #360555 is a reply to message #360302] Fri, 21 November 2008 06:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You can do almost the same way you are doing by using Bind variable as suggested by JRowBottom .

SQL> create Or replace Procedure exceute_immediate_emp ( empn NUMBER,deptn NUMBER,mgr NUMBER)
  2  AS
  3  v_sql VARCHAR2(200);
  4  excld_deptno NUMBER(3):=10;
  5  BEGIN
  6
  7  v_sql := 'INSERT INTO EMP_MGR (empno, deptno,mgr)
  8           SELECT :1,:2,:3 From Dual WHERE :2<>:4';
  9
 10  Execute immediate v_sql Using empn||'0',deptn,mgr||'0',deptn,excld_deptno;
 11
 12  END;
 13  /

Procedure created.

SQL> SELECT EMPNO,DEPTNO,MGR FROM EMP_MGR;

     EMPNO     DEPTNO        MGR
---------- ---------- ----------
      7369         20       7902
      7499         30       7698
      7521         30       7698
      7566         20       7839
      7654         30       7698
      7698         30       7839
      7788         20       7566
      7844         30       7698
      7876         20       7788
      7900         30       7698
      7902         20       7566

11 rows selected.

SQL> DECLARE
  2    EMPN NUMBER;
  3    DEPTN NUMBER;
  4    MGR NUMBER;
  5
  6  BEGIN
  7    EMPN := 200;
  8    DEPTN := 20;
  9    MGR := 790;
 10
 11    SCOTT.EXCEUTE_IMMEDIATE_EMP ( EMPN, DEPTN, MGR );
 12    COMMIT;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL> DECLARE
  2    EMPN NUMBER;
  3    DEPTN NUMBER;
  4    MGR NUMBER;
  5
  6  BEGIN
  7    EMPN := 100;
  8    DEPTN := 10;
  9    MGR := 790;
 10
 11    SCOTT.EXCEUTE_IMMEDIATE_EMP ( EMPN, DEPTN, MGR );
 12    COMMIT;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SQL> SELECT EMPNO,DEPTNO,MGR FROM EMP_MGR;

     EMPNO     DEPTNO        MGR
---------- ---------- ----------
      2000         20       7900
      7369         20       7902
      7499         30       7698
      7521         30       7698
      7566         20       7839
      7654         30       7698
      7698         30       7839
      7788         20       7566
      7844         30       7698
      7876         20       7788
      7900         30       7698
      7902         20       7566

12 rows selected.

SQL>



Smile
Rajuvan.

[Updated on: Fri, 21 November 2008 06:29]

Report message to a moderator

Re: dynamic sql problem [message #360579 is a reply to message #360555] Fri, 21 November 2008 08:04 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But surely that doesn't help, as the OP will still have to call the procedure 40 times, each time explicitly specifying each one of 22 different parameters.

The nearest thing I can think of to a solution is to explicitly write a block of code to do one insert, and then use a competent text editor to copy/paste that 39 times, and increment the variable number by one each time.

The only other thing I can think of is if you can unpack the MQ message yourself, and pack the fields into a collection at that point.
Previous Topic: update procedure using cursor / insert rank based on year (merged 4 different posts)
Next Topic: replacing single quote while loading data
Goto Forum:
  


Current Time: Tue Dec 06 06:14:58 CST 2016

Total time taken to generate the page: 0.05633 seconds