Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE ; ROWTYPE as USING
EXECUTE IMMEDIATE ; ROWTYPE as USING [message #356653] Fri, 31 October 2008 14:07 Go to next message
sabarimohan
Messages: 1
Registered: October 2008
Location: India
Junior Member

Dear ALL,

Please find the program I wrote for testing.
I need to populate the field name and data in the runtime.
Can any one help me out with answer.
I really appreciated your help in advance.

Thanks,
Sabari.

/*

Table name : IF_TEST

F1 NUMBER(3) Y
F2 NUMBER(3) Y
F3 NUMBER(3) Y

*/

create or replace package body test_simplain_sabari AS


function ins(tablename varchar2,
f1name varchar2, p_f1 number,
f2name varchar2, p_f2 number,
f3name varchar2, p_f3 number) return number is

p_if_test IF_TEST%ROWTYPE;
loc_dyna_sql varchar2(1024);

begin

loc_dyna_sql := 'p_if_test' || '.'|| f1name || ':=' || p_f1 || ';';
dbms_output.put_line(loc_dyna_sql);
EXECUTE IMMEDIATE loc_dyna_sql;

loc_dyna_sql := 'p_if_test' || '.'|| f2name || ':=' || p_f2 || ';';
dbms_output.put_line(loc_dyna_sql);
EXECUTE IMMEDIATE loc_dyna_sql;

loc_dyna_sql := 'p_if_test' || '.'|| f3name || ':=' || p_f3 || ';';
dbms_output.put_line(loc_dyna_sql);
EXECUTE IMMEDIATE loc_dyna_sql;

insert into if_test values p_if_test;

return 1;
end;


procedure Test1 is
i number;
begin
dbms_output.put_line('start of Test1');

i := ins('if_test','f1',1,'f2',2,'f3',3);
i := ins('if_test','f3',3,'f2',2,'f1',1);

dbms_output.put_line('end of Test1');

end;
end test_simplain_sabari;
Re: EXECUTE IMMEDIATE ; ROWTYPE as USING [message #356654 is a reply to message #356653] Fri, 31 October 2008 14:15 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the business need you are trying to achieve?

Also Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: How to select numeric values from a column
Next Topic: Date Range Data Extraction using SQL
Goto Forum:
  


Current Time: Fri Dec 02 17:00:09 CST 2016

Total time taken to generate the page: 0.21359 seconds