Difference between revisions of "Dynamic SQL"

Revision as of 11:30, 14 August 2018

• [Using Dynamic SQL]
• [asktom discussion]

Nice example from a question on AskTom

Ref: - asktom.oracle.com

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>

Drop database links by creating a procedure as the user then running the procedure

    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