Re: g

From: zafri1936 <zafri1936_at_hotmail.com>
Date: 13 Feb 2006 22:59:50 -0800
Message-ID: <1139900390.257079.106520_at_g44g2000cwa.googlegroups.com>

[Quoted] Thanks that you are assisting me in this regard. Below you find my code, which was written inside the when button press trigger. In my application after typing some sql on the screen and then press the button, the application generate the execution plan for that SQL. That's why my scripts accessing 10G Sqlplus via forms 6i. Should I also put all these work in forms ddl instead of this? The users system is login successfully on forms 6i but later when the sql scripts try to connect to databases after pressing the button, the error comes.

Thanks

Zafri

Declare

 in_file3 Text_IO.File_Type;
 linebuf3 VARCHAR2(1800);
 output11 varchar2(1000);

begin
  in_file3 := Text_IO.Fopen('c:\explain_plus\misc\create_table.txt',
'w');

  Text_IO.Put_Line(in_file3, linebuf3);

  Text_IO.put_line(in_file3,' ');

  Text_IO.put_line(in_file3,' run { sql "create table PLAN_TABLE (statement_id varchar2(30),timestamp date,remarks varchar2(80), numeric,cost numeric,cardinality numeric,bytes numeric,other_tag

varchar2(255),partition_start varchar2(255),partition_stop
varchar2(255),partition_id numeric,other long,distribution
varchar2(30)) "; } ');

  Text_IO.put_line(in_file3,' ');
  Text_IO.put_line(in_file3,' run { sql "grant select on plan_table to public";} ');
  Text_IO.put_line(in_file3,' ');

  Text_IO.FCLOSE(in_file3);

---

  in_file3 := Text_IO.Fopen('c:\explain_plus\misc\create_role.txt',
'w');

  Text_IO.Put_Line(in_file3, linebuf3);
  Text_IO.put_line(in_file3,'  ');
  Text_IO.put_line(in_file3,' run { sql "create role plustrace";}  ');
  Text_IO.put_line(in_file3,'  ');
  Text_IO.put_line(in_file3,' run { sql "grant select on v_$sesstat to

plustrace";} ');

Text_IO.FCLOSE(in_file3);

  ........................................

  Declare

 un  VARCHAR2(80);
 pw  VARCHAR2(80);
 cn  VARCHAR2(80);

 output VARCHAR2(1000);
 output2 VARCHAR2(1000);
 dummy varchar2(40);
 in_file Text_IO.File_Type;
 linebuf VARCHAR2(1800);

 BEGIN
         declare

                 dummy2 varchar2(40);

         begin

          select table_name into dummy2 from all_tables where table_name='PLAN_TABLE';

 	  if dummy2 = 'PLAN_TABLE'  then
 	  output2:='rman target/ nocatalog _at_C:\EXPLAIN_PLUS\misc\TRUNC2.txt '

;

    Host(output2,no_screen);

          end if;

 	 exception
  	when no_data_found	then

 output2:='rman target/ nocatalog
_at_C:\EXPLAIN_PLUS\misc\create_table.txt ';  Host(output2,no_screen);

          end;

  in_file := Text_IO.Fopen('c:\explain_plus\misc\auto_trace.txt', 'w');

  Text_IO.Put_Line(in_file, linebuf);
  Text_IO.put_line(in_file,' set trimspool on  ');
  Text_IO.put_line(in_file,' set heading off ');
  Text_IO.put_line(in_file,' set feedback off ');
  Text_IO.put_line(in_file,' set echo off');
  Text_IO.put_line(in_file,' spool

c:\explain_plus\misc\traceonly.txt');

  Text_IO.put_line(in_file,' set autotrace traceonly  ');
  Text_IO.put_line(in_file,'  ');
  Text_IO.put(in_file, :sql.sql);
  Text_IO.put_line(in_file,' ;  ' );
  Text_IO.put_line(in_file,'  ');
  Text_IO.put_line(in_file,' spool off');
  Text_IO.put_line(in_file,' set autotrace off  ');
  Text_IO.put_line(in_file,'   exit');
  Text_IO.FCLOSE(in_file);

  output:='c:\explain_plus\misc\create_ascript3.bat
'||un||'/'||pw||'_at_'||cn ;
 Host(output,no_screen);

  output:=' rman target/ nocatalog _at_c:\explain_plus\misc\runauto.txt' ;
  Host(output,no_screen);

 exception

          when no_data_found then
 output:='rman target/ nocatalog _at_C:\EXPLAIN_PLUS\misc\create_role.txt
' ;

 Host(output,no_screen);

.................................

end if;

end; Received on Tue Feb 14 2006 - 07:59:50 CET