Home » SQL & PL/SQL » SQL & PL/SQL » Getting SQL filename from withing sqlplus
 Getting SQL filename from withing sqlplus [message #171097] |
Mon, 08 May 2006 06:41  |
justaname23
Messages: 14
Registered: April 2001
|
Junior Member |
|
|
Hello!
I am executing a sql file from command line in unix. Is there a way, in sqlplus, to grab the file name from memory?
What am I am trying to do and why? I am writing templates for all of us to use. The individual files themselves may have different names, but all following a certain pattern. I will have standard output as part of header in the template. The output of the sql is always spooled to a file, but following a pattern.
We are trying to dynamically generate the spool output file name based on the sql file that the person copied the template to.
e.g. If a person copies the file name as abc.sql, the output will be abc.out or abc.lst.
If we can capture the sql file that is loaded in memory (i.e. the name of the sql file that is currently executing) then we can manipulate to get the spool file name.
Appreciate your ideas,
Smitha
|
|
|
|
|
|
| Re: Getting SQL filename from withing sqlplus [message #171600 is a reply to message #171184] |
Wed, 10 May 2006 09:13  |
justaname23
Messages: 14
Registered: April 2001
|
Junior Member |
|
|
On the contrary DBMS_APPLICATION_INFO.READ_MODULE gives the information. The following code will work:
I have not tested it completely to make anyone and everyone run it, but with a little debugging you shd be able to get run it
********************************
save the code below to a file and just to @filename.sql from sqlplus
********************************
set serveroutput on
set appinfo on
VARIABLE ch_module_name varchar2(48);
VARIABLE ch_action_name varchar2(32);
column sql_file_name1 new_val sql_file_name1;
column sql_file_name2 new_val sql_file_name2;
column spool_file new_val spool_file;
column instr_pos1 new_val instr_pos1;
column instr_pos2 new_val instr_pos2;
EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:ch_module_name, :ch_action_name);
select instr(:ch_module_name,'\', -1) instr_pos1 from dual;
select &instr_pos1 from dual;
--depending on your OS the above search string will vary. Search string also be different if you are executing something from current directory. In which case you will have to replace '\' with ' ' (a space)
select substr(:ch_module_name, &instr_pos1+1) sql_file_name1 from dual;
select '&sql_file_name1' from dual;
select instr('&sql_file_name1', '.', -1) instr_pos2 from dual;
select &instr_pos2 from dual;
select 'c:\temp\'|| substr('&sql_file_name1', 1, &instr_pos2) || 'out' sql_file_name2 from dual;
select '&sql_file_name2' from dual;
spool &sql_file_name2
select * from tab;
spool off
***************************
***************************
|
|
|
|
|
|
| Re: Getting SQL filename from withing sqlplus [message #171791 is a reply to message #171633] |
Thu, 11 May 2006 08:29 |
justaname23
Messages: 14
Registered: April 2001
|
Junior Member |
|
|
Here is the final code.
First, I converted part of my code into a function. This way we can keep the main file shorter. I will call this function by passing the module name. The function will cleanup and return just the spool file name.
The code for function
*******************************************************
create or replace function ret_spool_file (m_name varchar2)
return varchar2
is
spool_file varchar2(35);
unix_instr number;
wind_instr number;
curr_instr number;
ch_module_name varchar2(48);
ch_action_name varchar2(32);
sql_file_name1 varchar2(35);
sql_file_name2 varchar2(35);
instr_pos1 number;
instr_pos2 number;
begin
dbms_output.put_line(m_name);
curr_instr := instr(m_name,' ', -1);
dbms_output.put_line(curr_instr);
if (curr_instr = 0) then
wind_instr := instr(m_name, '\', -1);
unix_instr := instr(m_name, '/', -1);
if (wind_instr = 0) then
instr_pos1 := wind_instr;
else
instr_pos1 := unix_instr;
end if;
else
instr_pos1 := curr_instr;
end if;
sql_file_name1 := substr(m_name, instr_pos1+1);
instr_pos2 := instr(sql_file_name1, '.', -1) ;
sql_file_name2 := substr(sql_file_name1, 1, instr_pos2) || 'out' ;
dbms_output.put_line(sql_file_name2);
return sql_file_name2;
end;
/
********************************************
Now in your sql file ... (call the file anything you want). My file was fun.sql. The output, fun.out, was generated in the current directory
**************************************
set appinfo on
VARIABLE module_name varchar2(48);
VARIABLE action_name varchar2(48);
VARIABLE spool_file_name varchar2(48);
column my_file new_value spool_file_name noprint
EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:module_name, :action_name);
execute :spool_file_name := ret_spool_file(:module_name);
select :spool_file_name my_file from dual;
spool &spool_file_name
select sysdate from dual;
spool off
**************************************************
|
|
|
|
|
|
| Re: Getting SQL filename from withing sqlplus [message #171801 is a reply to message #171097] |
Thu, 11 May 2006 09:05 |
justaname23
Messages: 14
Registered: April 2001
|
Junior Member |
|
|
Here is the final code.
First, I converted part of my code into a function. This way we can keep the main file shorter. I will call this function by passing the module name. The function will cleanup and return just the spool file name.
The code for function
*******************************************************
create or replace function ret_spool_file (m_name varchar2)
return varchar2
is
spool_file varchar2(35);
unix_instr number;
wind_instr number;
curr_instr number;
ch_module_name varchar2(48);
ch_action_name varchar2(32);
sql_file_name1 varchar2(35);
sql_file_name2 varchar2(35);
instr_pos1 number;
instr_pos2 number;
begin
dbms_output.put_line(m_name);
curr_instr := instr(m_name,' ', -1);
dbms_output.put_line(curr_instr);
if (curr_instr = 0) then
wind_instr := instr(m_name, '\', -1);
unix_instr := instr(m_name, '/', -1);
if (wind_instr = 0) then
instr_pos1 := wind_instr;
else
instr_pos1 := unix_instr;
end if;
else
instr_pos1 := curr_instr;
end if;
sql_file_name1 := substr(m_name, instr_pos1+1);
instr_pos2 := instr(sql_file_name1, '.', -1) ;
sql_file_name2 := substr(sql_file_name1, 1, instr_pos2) || 'out' ;
dbms_output.put_line(sql_file_name2);
return sql_file_name2;
end;
/
********************************************
Now in your sql file ... (call the file anything you want). My file was fun.sql. The output, fun.out, was generated in the current directory
**************************************
set appinfo on
VARIABLE module_name varchar2(48);
VARIABLE action_name varchar2(48);
VARIABLE spool_file_name varchar2(48);
column my_file new_value spool_file_name noprint
EXECUTE DBMS_APPLICATION_INFO.READ_MODULE(:module_name, :action_name);
execute :spool_file_name := ret_spool_file(:module_name);
select :spool_file_name my_file from dual;
spool &spool_file_name
select sysdate from dual;
spool off
**************************************************
|
|
|
|
Goto Forum:
Current Time: Tue May 20 05:57:25 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
