Home » Developer & Programmer » Forms » How to run SQL Script from forms 6i?
( ) 1 Vote
| How to run SQL Script from forms 6i? [message #160454] |
Sun, 26 February 2006 09:12  |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
Hi,
We want to run some SQL Scripts from forms 6i.
We are using a form’s host command in order to that but not successful.
For Example:
…………………………
output:='c:\run_sql.txt '||un||'/'||pw||'@'||cn ;
Host(output,no_screen);
The run_sql.txt contains the following :
set trimspool on
set heading off
set feedback off
set echo off
spool c:\traceonly.txt
set autotrace traceonly
select * from scott.emp ;
spool off
set autotrace off
Please let us inform how should we run the above script from forms 6i’s Host Command?
insbor
|
|
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #160509 is a reply to message #160455] |
Mon, 27 February 2006 01:12  |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
Hi,
As you said we put the sqlplus with the username and password in the output variable like that:
output:='sqlplus Rami/Ramo@Dento @C:\run_sql.txt' ;
But after openeing the database session for running the scripts from forms6i, forms 6i hangs and closed the database session.
Why this happened.
We have oracle database 10 G.
insbor
|
|
|
|
|
|
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #162186 is a reply to message #161091] |
Thu, 09 March 2006 02:37 |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
Hi,
Now The sql scripts from forms 6i against 10G database are running fine.
But why this Ora-00904 error comes along with the scripts results on forms 6i screen. You can see it below
Below you find the complete code which I am using on WBPT.
If you have suggestion about improving this code please let me know. I am a single person whoc is doing this.
Your suggestion will be appreciated.
Thanks
Insbor
Following result was generate on forms screen with ORA-00904: "POSITION" error
Forms Screen
ERROR:
ORA-00904: "POSITION": invalid identifier
Error enabling EXPLAIN reportStatistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8646 consistent gets
45 physical reads
0 redo size
775383 bytes sent via SQL*Net to client
356955 bytes received via SQL*Net from client
3214 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
48188 rows processed
My code on forms button:----------------------------
Declare
in_file Text_IO.File_Type;
linebuf VARCHAR2(1800);
output varchar2(1000);
/* New Parameter define for form_ddl */
v_create_table_sql varchar2(2000);
v_test number;
un VARCHAR2(80);
pw VARCHAR2(80);
cn VARCHAR2(80);
BEGIN
Get_Connect_Info(un,pw,cn);
v_create_table_sql := 'Create table plan_table (STATEMENT_ID varchar2(30),'||
'PLAN_ID NUMBER ,'|| 'TIMESTAMP DATE ,'|| 'remarks varchar2(80),'||
'operation varchar2(30),'||'options varchar2(30),'||'object_owner varchar2(30),'
||'object_name varchar2(30),'||'optimizer varchar2(255),'||'search_columns number,'
||'id number(38),' ||'parent_id number(38),'
||'cost number(38),'||'cardinality number(38))';
----------------
select count(*) into v_test
from all_objects
where object_name = 'PLAN_TABLE'
and owner in (user,'PUBLIC');
if v_test = 0 then
forms_ddl(v_create_table_sql);
Forms_DDL('grant select on plan_table to public');
Forms_DDL('create public synonym plan_table for plan_table');
Forms_DDL('create role plustrace');
Forms_DDL('grant select on v_$sesstat to plustrace');
Forms_DDL('grant select on v_$statname to plustrace') ;
Forms_DDL('grant select on v_$session to plustrace') ;
Forms_DDL('grant plustrace to dba with admin option') ;
Forms_DDL('grant plustrace to public') ;
end if;
if v_test <> 0 then
forms_ddl('delete from plan_table ');
Forms_DDL ('commit');
end if;
IF NOT Form_Success THEN
Message ('Table Creation Failed');
ELSE
null;
-- Message ('Table Created');
END IF;
-----------------
-----------------
--Old Work
in_file := Text_IO.Fopen('c:\explain_plus\misc\create_ascript3.bat', 'w');
Text_IO.Put_Line(in_file, linebuf);
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' @echo off');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db=%1 ' );
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' if exist connect_DB == goto usage ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,
' echo " sqlplus %connect_db% @c:\explain_plus\misc\auto_trace.txt" ' ||
' > c:\explain_plus\misc\runauto.txt ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' :end ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db= ');
Text_IO.put_line(in_file,' ');
Text_IO.FCLOSE(in_file);
------------
in_file := Text_IO.Fopen('c:\explain_plus\misc\create_ascript4.bat', 'w');
Text_IO.Put_Line(in_file, linebuf);
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' @echo off');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db=%1 ' );
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' if exist connect_DB == goto usage ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,
' echo " sqlplus %connect_db% @c:\explain_plus\misc\raw_Explain.txt" ' ||
' > c:\explain_plus\misc\run_explain.txt ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' :end ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db= ');
Text_IO.put_line(in_file,' ');
Text_IO.FCLOSE(in_file);
-----------
in_file := Text_IO.Fopen('c:\explain_plus\misc\del_all.bat', 'w');
Text_IO.Put_Line(in_file, linebuf);
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' @echo off');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' del /s /q c:\explain_plus\misc\* ');
Text_IO.put_line(in_file,' ');
Text_IO.FCLOSE(in_file);
-----------------
in_file := Text_IO.Fopen('c:\explain_plus\misc\raw_explain.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,' EXPLAIN PLAN FOR ');
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,' exit');
Text_IO.FCLOSE(in_file);
---------
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||'@'||cn ;
Host(output,no_screen);
output:='sqlplus -s system/manager @c:\explain_plus\misc\runauto.txt' ;
Host(output,no_screen);
output:='c:\explain_plus\misc\create_ascript4.bat '||un||'/'||pw||'@'||cn ;
Host(output,no_screen);
output:='sqlplus -s system/manager @c:\explain_plus\misc\run_explain.txt' ;
Host(output,no_screen);
set_alert('Explain Plan','You are going to generate explain plan for that SQL. ');
exception
WHEN OTHERS
THEN
message('Error occured');
If Text_IO.is_open(in_file) then
Text_IO.Fclose(in_file);
output:='c:\explain_plus\misc\del_all.bat';
host(output,no_screen);
End if;
--------------------
end;[Updated on: Mon, 13 March 2006 23:35] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #162398 is a reply to message #162349] |
Fri, 10 March 2006 01:04 |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
Hi,
Actually we are trying to generate autotrace plan of a sql statement on forms screen.
In order to do that I type the sql on forms screen1 and then press the button "Generate Autotrace" in which all the code is written which I posted earlier.
FOr example: I type the following SQL on screen 1:
Select * from scott.emp
I also like to inform you that I am using another procedure "ALERT1" which invokes when I press the OK button of alert message. This alert comes with OK and CANCEL button after pressing the first "Generate Autotrace" button.
The alert message invoke with OK and CANCEL Button.The OK button invoke the procedure "Alert1" which then generate the results on forms screen2. Earlier when I run the forms 6i against 8i database I didn't get this error, which now comes along with the autotrace results on forms screen2 in database 10G.
Below is the autotrace trace result starting with error:
I will very much apprecaite your suggestion that how to get rid of this error as well as please look at my code which I posted earlier as well as the procedure "ALERT1" which you can see below, should I improve it:
Insbor
The result genrates with error:
ERROR:
ORA-00904: "POSITION": invalid identifier
Error enabling EXPLAIN report
0 recursive calls
0 db block gets
46 consistent gets
45 physical reads
0 redo size
83 bytes sent via SQL*Net to client
35 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
Porcedure "ALERT1"PROCEDURE alert1
(alert_title VARCHAR2,
alert_message VARCHAR2)
IS
alert_id alert;
button_pressed NUMBER;
alert_not_found EXCEPTION;
in_file text_io.file_type;
linebuf VARCHAR2(1800);
filename VARCHAR2(30);
output VARCHAR2(1000);
BEGIN
alert_id := Find_alert('messages');
IF Id_null(alert_id) THEN
RAISE alert_not_found;
END IF;
Set_alert_property(alert_id,
title,
alert_title);
Set_alert_property(alert_id,
alert_message_text,
alert_message);
Set_alert_button_property(alert_id,
alert_button1,
label,
'OK');
button_pressed := Show_alert(alert_id);
IF button_pressed = alert_button1 THEN
:execution_plan := '';
output := 'cscript c:\explain_plus\misc\sleep1.vbs';
Host(output,
no_screen);
in_file := text_io.Fopen('c:\explain_plus\misc\traceonly.txt',
'r');
LOOP
text_io.Get_line(in_file,
linebuf);
:sql.execution_plan := :sql.execution_plan || linebuf || Chr(10);
END LOOP;
text_io.Fclose(in_file);
END IF; -------------------
IF button_pressed = alert_button2 THEN
output := 'c:\explain_plus\sleep4.vbs';
Host(output,
no_screen);
output := 'c:\explain_plus\misc\del_all.bat';
Host(output,
no_screen);
:execution_plan := '';
END IF;
-----------
EXCEPTION
WHEN no_data_found THEN
text_io.Put_line('Closing the file...');
text_io.Fclose(in_file);
NULL;
WHEN ALERT_NOT_FOUND THEN
Message('ALERT MESSAGES DOES NOT EXIST.');
WHEN OTHERS THEN
text_io.Put_line('Closing the file...');
text_io.Fclose(in_file);
Message('INTERNAL ERROR OCCURED.');
END;
Upd-mod: Feed code through formatter at http://www.orafaq.com/utilities/sqlformatter.htm
|
|
|
|
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #163282 is a reply to message #162881] |
Wed, 15 March 2006 23:16 |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
Hi David,
I am not a pefect prograamer or user of forms 6i. I am a free lance certified DBA who is working on this small forms applicaton.
Before start working on my application I got suggestion from someone who recomended me to use text_io ( a forms utility) for reading and writing a text file through forms. I read forms help about text_io, from where I got it in my little application.
Is not a good option, please give me a good example of utl_file using my code, which can be helpful for me to replace utl_file with text_io.
As you said that output lines are longer than 80 chararacters can be written out using the default 'text_io.Fopen' statement, could you explain it?
REgarding the error It might be a missing column name thats why the result generate with error.
David please send me a good example of UTl_File using my code which I posted earlier.
I will be thankful to you. May GOD bless on you.
Insbor
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #163292 is a reply to message #163282] |
Thu, 16 March 2006 00:03 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
| Quote: |
have you determined on which line the error is being raised? This one is important.
|
Put an alert between each line to help you determine which line is failing.
| Quote: |
using the default 'text_io.Fopen' statement, could you explain it?
|
I don't have good documentation for the 'text_io.Fopen' so I can't tell you exactly how to handle lines longer than 'xxx' characters. I don't even know what the default 'xxx' value IS!! Search this forum for 'text_io', there are plenty of examples. Also try googling it. Are you getting all the text coming out in your output files? If so, then the default line width is not the problem. Experiment, try putting out an 80 character line, then a 100, then a 150, then a 200. If they all work, then the line width is not the problem.
So put that alert between each line and FIND OUT WHICH IS FAILING.
Anyway, work out EXACTLY which line is causing the error and get back to us with it.
David
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #163546 is a reply to message #163292] |
Fri, 17 March 2006 03:38 |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
David,
Thanks for your reply.
Regarding putting alert b/w each line in oredr to determine which line is falling, could you please give an example of that alert.
Quote:
So put that alert between each line and FIND OUT WHICH IS FAILING.
Put alert b/w each lines, does it means I put the alert in each line of my ALERT1 procedure?
Thanks
insbor
|
|
|
|
|
|
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #164549 is a reply to message #160454] |
Thu, 23 March 2006 23:34 |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
Hi David,
Now my application of getting explain plan of a sql is running fine.
But during running, one script which is written inside the wbp trigger, I manually get rid of the forms plus80.sql screen, which is cumbersome. Could someone suggest me what changes I will do in the code in order to terminate this plus80.sql forms screen automatically.
Thanks
Insbor.
My code as I posted earlier
Declare
in_file Text_IO.File_Type;
linebuf VARCHAR2(1800);
output varchar2(1000);
/* New Parameter define for form_ddl */
v_create_table_sql varchar2(2000);
v_test number;
un VARCHAR2(80);
pw VARCHAR2(80);
cn VARCHAR2(80);
BEGIN
Get_Connect_Info(un,pw,cn);
-------------------------------
select count(*) into v_test
from all_objects
where object_name = 'PLAN_TABLE'
and owner in (user,'PUBLIC');
if v_test = 0 then
forms_ddl(v_create_table_sql);
end if;
if v_test <> 0 then
forms_ddl('delete from plan_table ');
Forms_DDL ('commit');
end if;
IF NOT Form_Success THEN
Message ('Table Creation Failed');
ELSE
null;
-- Message ('Table Created');
END IF;
-----------------
-----------------
--Old Work
in_file := Text_IO.Fopen('c:\explain_plus\misc\create_ascript3.bat', 'w');
Text_IO.Put_Line(in_file, linebuf);
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' @echo off');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db=%1 ' );
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' if exist connect_DB == goto usage ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' echo host " sqlplus -s %connect_db% @c:\explain_plus\misc\auto_trace.txt" > c:\explain_plus\misc\runauto.txt ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' :end ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db= ');
Text_IO.put_line(in_file,' ');
Text_IO.FCLOSE(in_file);
------------
in_file := Text_IO.Fopen('c:\explain_plus\misc\create_ascript4.bat', 'w');
Text_IO.Put_Line(in_file, linebuf);
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' @echo off');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db=%1 ' );
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' if exist connect_DB == goto usage ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' echo host " sqlplus -s %connect_db% @c:\explain_plus\misc\raw_Explain.txt" > c:\explain_plus\misc\run_explain.txt ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' :end ');
Text_IO.put_line(in_file,' ');
Text_IO.put_line(in_file,' set connect_db= ');
Text_IO.put_line(in_file,' ');
Text_IO.FCLOSE(in_file);
-----------------
in_file := Text_IO.Fopen('c:\explain_plus\misc\raw_explain.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,' EXPLAIN PLAN FOR ');
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,' exit');
Text_IO.FCLOSE(in_file);
---------
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||'@'||cn ;
Host(output,no_screen);
output:='sqlplus -s system/manager @c:\explain_plus\misc\runauto.txt' ;
Host(output,no_screen);
output:='c:\explain_plus\misc\create_ascript4.bat '||un||'/'||pw||'@'||cn ;
Host(output,no_screen);
output:='sqlplus -s system/manager @c:\explain_plus\misc\run_explain.txt' ;
Host(output,no_screen);
exception
WHEN OTHERS
THEN
message('Error occured');
If Text_IO.is_open(in_file) then
Text_IO.Fclose(in_file);
output:='c:\explain_plus\misc\del_all.bat';
host(output,no_screen);
End if;
--------------------
end;
|
|
|
|
|
|
| Re: How to run SQL Script from forms 6i? [message #164832 is a reply to message #164824] |
Sun, 26 March 2006 23:47 |
insbor
Messages: 13
Registered: February 2006
Location: PakPattan
|
Junior Member |
|
|
Hi David,
In my code as I posted I didnt use close_form or exit_form command.
Regarding the problem Should I use these commands in my code, if Yes where should I place this command, so the plus80.sql window will be automatically closed after performing the operation.
Thanks
Insbor
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 07:33:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
