?
Solved

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

Posted on 2003-02-26
11
Medium Priority
?
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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 …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

762 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