set serveroutput on
declare
s_sql varchar2(2000);
s_rez varchar2(32767);
begin
s_sql := 'select LONG_COLUMN from SOME_TABLE where ID_COLUMN = 123' ;
execute immediate s_sql into s_rez;
-- print it on the screen or convert/transform/search...
dbms_output.put_line ( s_rez ) ;
end;
SELECT
'create '
||DECODE(U.NAME,'PUBLIC','public ')
||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')
|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM
sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;
Technically saying, this is granting permissions to all tables in the schema
set verify off
set feedback off
set pagesize 10000
set linesize 120ACCEPT main_owner CHAR PROMPT 'MAIN OWNER: '
ACCEPT grant_to CHAR PROMPT 'GRANT TO: '
ACCEPT permissions CHAR PROMPT 'PERMISSIONS (i.e. SELECT,UPDATE): '
spool grant_from_&&main_owner._to_&&grant_to..sql
select 'grant &&permissions on "&&main_owner"."'
||table_name
||'" to "&&grant_to";' "---"
from dba_tables
where owner=upper('&&main_owner');
spool off
undefine permissions
undefine main_owner
undefine grant_to
Proxy autoconfig file could be used in browser (Opera,FireFox,IE) to change the proxy dynamically.
var ALLOWED = "DIRECT";
var DISALLOWED = "PROXY 0.0.0.0:1234";
function FindProxyForURL(url, host)
{
if (shExpMatch(url,"*.badsite.com/*")) {return DISALLOWED;}
var currentTime = new Date();// convert the current time to minutes
var now = 60*currentTime.getHours() + currentTime.getMinutes();
// Do not allow the access before 18:00
if (now < 18 * 60 ) {
return DISALLOWED ;
}
else
{
return ALLOWED ;
}
} // end of function
<script type="text/javascript">
function buildTable(n_rows,n_columns) {
var the_body = document.getElementsByTagName("body")[0];
var new_table = document.createElement("table");
var new_tbody = document.createElement("tbody");
var new_row, new_col, new_text ;
for(row=1;row<=n_rows;row++) {
new_row = document.createElement("tr");
new_row.className = "tr"+row ;
for(col=1;col<=n_columns;col++) {
new_col = document.createElement("td");
new_col.className = "td"+col ;
new_text = document.createTextNode("text:" + row + ":" + col );
new_col.appendChild(new_text);
new_row.appendChild(new_col);
}
new_tbody.appendChild(new_row);
}
new_table.appendChild(new_tbody);
new_table.setAttribute("border", "2");
the_body.appendChild(new_table);
return new_table ;
}
</script>
<body>
Example:
<script type="text/javascript">
buildTable(4,3)
</script>
</body>
There is also interesting article on oreillynet.com, discussing performance improvement for such operations.
The following code starts the SQL from the shell script directly, without additional SQL file
#!/bin/sh
--/. &> /dev/null; exec mysql "$@"
select 'Hello, world!' as test ;
The detailed explanation of the trick is here
ls -1 | cpio -o | cpio -ivt | awk '{print $NF, $(NF-1), $(NF-4), $(NF-3) }'
Warning: I/O expensive for the large files!
Perl:
@a = localtime((stat($my_file))[9]); $a[4]++;
printf "%02d%02d%02d",@a[5,4,3];
Start the dxdiag tool.
Windows XP:
Start -> Run -> dxdiag
Windows Vista:
Start -> Search -> dxdiag
…and if You could not find dxdiag, probably You do not have DirectX on Your machine.
Are You still using old kind DOS box?
SELECT s.sid, s.serial#, st.sql_text sql_text
FROM v$session s, v$sql st, v$process p
WHERE s.sql_hash_value = st.hash_value
AND s.sql_address = st.address
AND s.paddr = p.addr
and ( s.sid=&which_sid. )
Full text
select x.sql_text
from v$session s,v$sqltext x
where s.sql_address=x.address and s.sid = &which_sid
order by sid, piece asc
create table MAIN_TBL (
magazine varchar2(10),
region varchar2(5),
quantity int );
insert into MAIN_TBL values ( 'Playboy', 'Nord', 1 );
insert into MAIN_TBL values ( 'Playboy', 'East', 2 );
insert into MAIN_TBL values ( 'AutoWeek', 'Nord', 3 );
insert into MAIN_TBL values ( 'AutoWeek', 'West', 4 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 5 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 6 );
insert into MAIN_TBL values ( 'Wired', 'West', 7 );
insert into MAIN_TBL values ( 'Wired', 'East', 8 );
insert into MAIN_TBL values ( 'Wired', 'South', 9 );
declare
report_exists number;
report_owner varchar(30) := 'FRODO' ;
--
report_name varchar(30) := 'REPORT_TBL' ;
query_main varchar(16000) :=
'create table ' || report_owner || '.'
|| report_name || ' as select MAGAZINE ' ;
--
query_part varchar(1024) ;
my_var varchar2(5);
cursor cur_region is
select distinct REGION
from MAIN_TBL
order by region;
begin
select count(*) into report_exists
from dba_tables
where table_name = report_name and owner = report_owner;
if ( report_exists = 1 ) then
execute immediate 'drop table ' || report_owner || '.' || report_name ;
end if;
open cur_region ;
loop
fetch cur_region into my_var ;
exit when cur_region%NOTFOUND;
query_part := 'select nvl(sum(quantity),0) from MAIN_TBL x where x.magazine = main.magazine and x.region='''||my_var||'''' ;
query_main := query_main || chr(10) || ',(' || query_part || ')"' || my_var || '"';
end loop;
close cur_region ;
query_main := query_main || ' from (select distinct MAGAZINE from MAIN_TBL ) main' ;
execute immediate query_main ;
end;
/
select * from FRODO.REPORT_TBL
/
Output:
MAGAZINE East Nord South West
———- —– —– —— ——-
AutoWeek 0 3 0 4
Playboy 2 1 0 0
Wired 8 11 9 7
There is also discussion of the similar subject on AskTom site.
[amazon-product]0137142838[/amazon-product]