Dynamic SQL - Revision history

Stuart: /* Nice example from a question on AskTom */

2018-09-04T13:12:25Z

‎Nice example from a question on AskTom

Stuart: /* Nice example from a question on AskTom */

2018-09-04T13:11:32Z

‎Nice example from a question on AskTom

Stuart at 11:30, 14 August 2018

2018-08-14T11:30:23Z

Stuart at 13:19, 9 August 2018

2018-08-09T13:19:44Z

Stuart at 11:06, 1 February 2013

2013-02-01T11:06:19Z

Stuart: Created page with "* http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL"

2013-02-01T11:05:39Z

Created page with "* http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL"

New page

* [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]]

@@ Line 1: / Line 1: @@
 * [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]]
 * [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]]
-===Nice example from a question on AskTom===
+===Nice example from a question on AskTom using dynamic sql passing an SQL statement as a parameter===
 Pretty print format a row from a table<br />
 * Ref: [https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536889800346305468 - asktom.oracle.com]

@@ Line 2: / Line 2: @@
 * [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]]
 ===Nice example from a question on AskTom===
-Ref: [https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536889800346305468 - asktom.oracle.com]
+Pretty print format a row from a table<br />
 <pre>
 set serverout on size 999999
@@ Line 77: / Line 78: @@
 SQL>
 </pre>
 ===Drop database links by creating a procedure as the user then running the procedure===
 <pre>

← Older revision		Revision as of 11:30, 14 August 2018
Line 76:		Line 76:

	SQL>		SQL>
		+	</pre>
		+	===Drop database links by creating a procedure as the user then running the procedure===
		+	<pre>
		+	printf "\n\n%s\n" "* Removing the database links from db_sizing *"
		+	cat<<EOSQL >"/tmp/results.deletedb.appli.${TO_SID}.sql"
		+	grant create database link to db_sizing;
		+	grant select on sys.dba_db_links to db_sizing;
		+	set pages 0 newpa none lines 1000 feed off serveroutput on
		+	create or replace procedure db_sizing.drop_db_link as
		+	l_db_link varchar2(4000);
		+	l_stmt varchar2(4000);
		+	begin
		+	dbms_output.enable(null);
		+	select db_link
		+	into l_db_link
		+	from sys.dba_db_links
		+	where owner = 'DB_SIZING'
		+	and db_link like '%${TO_SID_UPPER}%';
		+	select 'drop database link '\|\|l_db_link into l_stmt from dual;
		+	dbms_output.put_line(l_stmt);
		+	execute immediate l_stmt;
		+	exception
		+	when no_data_found then
		+	dbms_output.put_line('No link matching %${TO_SID_UPPER}% found.');
		+	end drop_db_link;
		+	/
		+	exec db_sizing.drop_db_link;
		+	revoke create database link from db_sizing;
		+	drop procedure db_sizing.drop_db_link;
		+	EOSQL
	</pre>		</pre>

← Older revision		Revision as of 13:19, 9 August 2018
Line 1:		Line 1:
	* [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]]		* [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]]
	* [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]]		* [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]]
		+	===Nice example from a question on AskTom===
		+	Ref: [https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536889800346305468 - asktom.oracle.com]
		+	<pre>
		+	set serverout on size 999999
		+	set verify off
		+	declare
		+	p_query varchar2(32767) := q'{&1}';
		+
		+	l_theCursor integer default dbms_sql.open_cursor;
		+	l_columnValue varchar2(4000);
		+	l_status integer;
		+	l_descTbl dbms_sql.desc_tab;
		+	l_colCnt number;
		+	n number := 0;
		+	procedure p(msg varchar2) is
		+	l varchar2(4000) := msg;
		+	begin
		+	while length(l) > 0 loop
		+	dbms_output.put_line(substr(l,1,80));
		+	l := substr(l,81);
		+	end loop;
		+	end;
		+	begin
		+	execute immediate
		+	'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
		+
		+	dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
		+	dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
		+
		+	for i in 1 .. l_colCnt loop
		+	dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
		+	end loop;
		+
		+	l_status := dbms_sql.execute(l_theCursor);
		+
		+	while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
		+	for i in 1 .. l_colCnt loop
		+	dbms_sql.column_value( l_theCursor, i, l_columnValue );
		+	p( rpad( l_descTbl(i).col_name, 30 )
		+	\|\| ': ' \|\|
		+	l_columnValue );
		+	end loop;
		+	dbms_output.put_line( '-----------------' );
		+	n := n + 1;
		+	end loop;
		+	if n = 0 then
		+	dbms_output.put_line( chr(10)\|\|'No data found '\|\|chr(10) );
		+	end if;
		+	end;
		+	/
		+
		+	set verify on
		+
		+	SQL> @pt "select * from scott.emp where rownum = 1"
		+	EMPNO : 7369
		+	ENAME : SMITH
		+	JOB : CLERK
		+	MGR : 7902
		+	HIREDATE : 17-dec-1980 00:00:00
		+	SAL : 800
		+	COMM :
		+	DEPTNO : 20
		+	-----------------
		+
		+	PL/SQL procedure successfully completed.
		+
		+	SQL> @pt "select * from scott.dept where rownum = 1"
		+	DEPTNO : 10
		+	DNAME : ACCOUNTING
		+	LOC : NEW YORK
		+	-----------------
		+
		+	PL/SQL procedure successfully completed.
		+
		+	SQL>
		+	</pre>

← Older revision		Revision as of 11:06, 1 February 2013
Line 1:		Line 1:
	* [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]]		* [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]]
		+	* [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]]