?
Solved

NEED HELP FAST: How to execute a dynamic sql statement on post_query trigger

Posted on 2003-02-26
11
Medium Priority
?
371 Views
Last Modified: 2013-12-12
How can I execute a dynamic sql statement in forms on a trigger. ie.

my_sql := 'select ' || col_name || ' into my_count, my_count1, my_count2,my_count3     from ets_item_dashboard where item_type = ' || '''' || :ets_item_dashboard.item_type || '''' ||
               ' and org_id = ' || '''' || :ets_item_dashboard.org_id || '''' ;    

:control.v1 := my_count
:control.v2 := my_count1
:control.v3 := my_count2
:control.v4 := my_count3

I need to be  able to populate several field afterwards. There is considerable more to the routine. However I need to be able to create the statement this way as the column name can change.

0
Comment
Question by:Maritimer
  • 7
  • 3
11 Comments
 

Author Comment

by:Maritimer
ID: 8030022
I have tried this but no success so far


     my_sql :=      'select ' || col_name || ' into :ets_item_dashboard.v1      from ets_item_dashboard where item_type = ' || '''' || :ets_item_dashboard.item_type || '''' ||
               ' and org_id = ' || '''' || :ets_item_dashboard.org_id || ''';' ;
         
     forms_ddl(my_sql);
0
 

Author Comment

by:Maritimer
ID: 8030106
I have tried this but no success so far


     my_sql :=      'select ' || col_name || ' into :ets_item_dashboard.v1      from ets_item_dashboard where item_type = ' || '''' || :ets_item_dashboard.item_type || '''' ||
               ' and org_id = ' || '''' || :ets_item_dashboard.org_id || ''';' ;
         
     forms_ddl(my_sql);
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 8031697
I think that problems are caused by using bind variables. What happens if you replace them with values ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Expert Comment

by:andrewst
ID: 8032451
FORMS_DDL can't return values to the form, it can just execute the SQL statement.  You will have to use either EXECUTE IMMEDIATE, if your version of forms supports it, or DBMS_SQL, which will definitely work.

With EXECUTE IMMEDIATE the code is:

DECLARE
  my_sql VARCHAR2(2000);
BEGIN
  my_sql := 'select ' || col_name || ' from ets_item_dashboard where item_type = :item_type and org_id = :org_id';

  EXECUTE IMMEDIATE my_sql
  INTO :control.v1,:control.v2,:control.v3,:control.v4
  USING :ets_item_dashboard.item_type, :ets_item_dashboard.org_id;    
END;

With DBMS_SQL the code is:

DECLARE
  my_sql VARCHAR2(2000);
  c number;
  r number;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  my_sql := 'select ' || col_name || ' from ets_item_dashboard where item_type = :item_type and org_id = :org_id';
  DBMS_SQL.PARSE( c, my_sql, 1 );
  DBMS_SQL.DEFINE_COLUMN( c, 1, :control.v1 );
  DBMS_SQL.DEFINE_COLUMN( c, 2, :control.v2 );
  DBMS_SQL.DEFINE_COLUMN( c, 3, :control.v3 );
  DBMS_SQL.DEFINE_COLUMN( c, 4, :control.v4 );
  DBMS_SQL.BIND_VARIABLE( c, 'item_type', :ets_item_dashboard.item_type );
  DBMS_SQL.BIND_VARIABLE( c, 'org_id', :ets_item_dashboard.org_id );
  r := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE( c, 1, :control.v1 );
  DBMS_SQL.COLUMN_VALUE( c, 2, :control.v2 );
  DBMS_SQL.COLUMN_VALUE( c, 3, :control.v3 );
  DBMS_SQL.COLUMN_VALUE( c, 4, :control.v4 );
  DBMS_SQL.CLOSE_CURSOR(c);
END;

In both cases I have changed your query to make use of bind variables, which you should always do.
0
 

Author Comment

by:Maritimer
ID: 8033701
Hi andrewst

I have tried yours I am getting an ora-01006 bind variable does not exist.
0
 

Author Comment

by:Maritimer
ID: 8033854
This is what I have now under my trigger.


declare
cursor a1 is select distinct status_code from status_code order by status_code;
     
     A2_tmp A2%rowtype;
     A1_tmp A1%rowtype;
     B1_tmp B1%rowtype;
     
     col_name varchar2(2000);
     my_sql varchar2(2000);
     temp varchar2(100);
     
     err_code number;
      err_text varchar(100);
      c number;
      r number;

BEGIN
     
     counter := 0;

         
     col_name := 'ASSIGNED,ACTIVE,INACTIVE,LOST';
     

for A1_tmp in A1 loop
         
         
         
     c := DBMS_SQL.OPEN_CURSOR;
     
          my_sql :=      'select ' || col_name || ' from ets_item_dashboard where item_type = ' || '''' ||                :ets_item_dashboard.item_type || '''' ||
               ' and org_id = ' || '''' || :ets_item_dashboard.org_id || '''' ;
         
     
 DBMS_SQL.PARSE( c, my_sql, 1 );
     
 DBMS_SQL.DEFINE_COLUMN( c, 1, :ets_item_dashboard.v1 );
 
 DBMS_SQL.DEFINE_COLUMN( c, 2, :ets_item_dashboard.v2 );
 DBMS_SQL.DEFINE_COLUMN( c, 3, :ets_item_dashboard.v3 );
 DBMS_SQL.DEFINE_COLUMN( c, 4, :ets_item_dashboard.v4 );

 DBMS_SQL.BIND_VARIABLE( c, 'item_type', :ets_item_dashboard.item_type );
 DBMS_SQL.BIND_VARIABLE( c, 'org_id', :ets_item_dashboard.org_id );
 r := DBMS_SQL.EXECUTE_AND_FETCH(c);
 DBMS_SQL.COLUMN_VALUE( c, 1, :ets_item_dashboard.v1 );
 DBMS_SQL.COLUMN_VALUE( c, 2, :ets_item_dashboard.v2 );
 DBMS_SQL.COLUMN_VALUE( c, 3, :ets_item_dashboard.v3 );
 DBMS_SQL.COLUMN_VALUE( c, 4, :ets_item_dashboard.v4 );
 DBMS_SQL.CLOSE_CURSOR(c);

     
     
end loop;
     
     
     
     exception    
  when others then
 
  err_code := SQLCODE;
     err_text := SQLERRM;
     MESSAGE ('Error '|| TO_CHAR(err_code)|| ' - ' || err_text);
     MESSAGE ('Error '|| TO_CHAR(err_code)|| ' - ' || err_text);
END;
0
 

Author Comment

by:Maritimer
ID: 8033900
This is what I have now under my trigger.


declare
cursor a1 is select distinct status_code from status_code order by status_code;
     
     A2_tmp A2%rowtype;
     A1_tmp A1%rowtype;
     B1_tmp B1%rowtype;
     
     col_name varchar2(2000);
     my_sql varchar2(2000);
     temp varchar2(100);
     
     err_code number;
      err_text varchar(100);
      c number;
      r number;

BEGIN
     
     counter := 0;

         
     col_name := 'ASSIGNED,ACTIVE,INACTIVE,LOST';
     

for A1_tmp in A1 loop
         
         
         
     c := DBMS_SQL.OPEN_CURSOR;
     
          my_sql :=      'select ' || col_name || ' from ets_item_dashboard where item_type = ' || '''' ||                :ets_item_dashboard.item_type || '''' ||
               ' and org_id = ' || '''' || :ets_item_dashboard.org_id || '''' ;
         
     
 DBMS_SQL.PARSE( c, my_sql, 1 );
     
 DBMS_SQL.DEFINE_COLUMN( c, 1, :ets_item_dashboard.v1 );
 
 DBMS_SQL.DEFINE_COLUMN( c, 2, :ets_item_dashboard.v2 );
 DBMS_SQL.DEFINE_COLUMN( c, 3, :ets_item_dashboard.v3 );
 DBMS_SQL.DEFINE_COLUMN( c, 4, :ets_item_dashboard.v4 );

 DBMS_SQL.BIND_VARIABLE( c, 'item_type', :ets_item_dashboard.item_type );
 DBMS_SQL.BIND_VARIABLE( c, 'org_id', :ets_item_dashboard.org_id );
 r := DBMS_SQL.EXECUTE_AND_FETCH(c);
 DBMS_SQL.COLUMN_VALUE( c, 1, :ets_item_dashboard.v1 );
 DBMS_SQL.COLUMN_VALUE( c, 2, :ets_item_dashboard.v2 );
 DBMS_SQL.COLUMN_VALUE( c, 3, :ets_item_dashboard.v3 );
 DBMS_SQL.COLUMN_VALUE( c, 4, :ets_item_dashboard.v4 );
 DBMS_SQL.CLOSE_CURSOR(c);

     
     
end loop;
     
     
     
     exception    
  when others then
 
  err_code := SQLCODE;
     err_text := SQLERRM;
     MESSAGE ('Error '|| TO_CHAR(err_code)|| ' - ' || err_text);
     MESSAGE ('Error '|| TO_CHAR(err_code)|| ' - ' || err_text);
END;
0
 
LVL 15

Expert Comment

by:andrewst
ID: 8033909
Which did you try - EXECUTE IMMEDIATE or DBMS_SQL?

The DBMS_SQL version works for me.  Maybe you mistyped something?  The bind variables are :item_type and :org_id in my_sql, and must be refered to as 'item_type' and 'org_id' in the BIND_VARIABLE calls.
0
 
LVL 15

Accepted Solution

by:
andrewst earned 200 total points
ID: 8033926
You forgot to change the my_sql statement to:

my_sql := 'select ' || col_name || ' from ets_item_dashboard where item_type = :item_type and org_id = :org_id';
 
0
 

Author Comment

by:Maritimer
ID: 8034131
Hi andrewst

I figured out the error and have it working now. Again thanks for your help.

Maritimer.

0
 

Author Comment

by:Maritimer
ID: 8034565
Again Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month9 days, 9 hours left to enroll

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question