Differences

This shows you the differences between two versions of the page.

--- dynamic_sql [2018/12/08 12:49] – created 0.0.0.0
+++ dynamic_sql [2019/01/30 11:32] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== 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]]
+==== 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\\
+  *  Ref: [[https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536889800346305468|- asktom.oracle.com]]
+<code>
+set serverout on size 999999
+set verify off
+declare
+    p_query varchar2(32767) := q'{&1}';
-  * [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]]
+    l_theCursor     integer default dbms_sql.open_cursor;
-  * [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]]
+    l_columnValue   varchar2(4000);
-=====Nice example from a question on AskTom using dynamic sql passing an SQL statement as a parameter=====
+    l_status        integer;
-Pretty print format a row from a table<br />
+    l_descTbl       dbms_sql.desc_tab;
-  * Ref: [[https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536889800346305468|- asktom.oracle.com]]
+    l_colCnt        number;
-<code>0@@</code>
+    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>
+</code>
+==== Drop database links by creating a procedure as the user then running the procedure ====
+<code>
+    printf "\
+\
+%s\
+" "*** 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
+</code>
-=====Drop database links by creating a procedure as the user then running the procedure=====
-<code>1@@</code>