批量获取oracle库存储过程

场景:需要获取oracle库下的存储过程做备份

示例:获取单个存储过程,sql实现如下

SELECT owner,object_name,object_type FROM dba_objects;
SELECT DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('FUNCTION','CONCATSTR','TEST')) FROM DUAL;
CREATE OR REPLACE EDITIONABLE FUNCTION "TEST"."CONCATSTR" (input ConcatObj) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING ConcatStrImpl;

1
2
3
4

代码示例：
输入文件如下:

cat func_oracle.txt
SYS|GETLONG|FUNCTION
SYS|AV_CACHE_COL|FUNCTION
1
2
3

输出文件如下:

  CREATE OR REPLACE NONEDITIONABLE FUNCTION "SYS"."GETLONG" ( opcode  in number,
                                    p_rowid in rowid ) return varchar2
as
begin
    return CDBView.getlong(opcode, p_rowid);
end getlong;

  CREATE OR REPLACE NONEDITIONABLE FUNCTION "SYS"."AV_CACHE_COL" (
  incol in varchar2, incolpos in number)
return varchar2
is
begin
  return incol;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

scala代码如下:

package com.tpiods.spark.jdbc

import java.io.{File, FileInputStream, PrintWriter}
import java.sql.DriverManager

object TestGetOracleFunction {
  def main(args: Array[String]): Unit = {
    val input = "ods_etl/src/main/resources/work0511_func/func_oracle.txt"
    val output = "ods_etl/src/main/resources/work0511_func/func_oracle_out.txt"
    val lines1 = scala.io.Source.fromInputStream(new FileInputStream(input)).getLines()
    //定义连接条件,拿到连接
    val conn_str = "jdbc:oracle:thin:@10.22.33.44:1521/TPI|PAY|4ApNtjQvmq"
    val conns = conn_str.split("\\|")
    val url: String = conns(0)
    val username: String = conns(1)
    val password: String = conns(2)
    Class.forName("oracle.jdbc.driver.OracleDriver")
    val conn = DriverManager.getConnection(url, username, password)

    val sb = new StringBuilder
    //SELECT DBMS_METADATA.GET_DDL('PROCEDURE','ADD_COMM','TPPROD') FROM DUAL
    //SYS|GETLONG|FUNCTION
    for (line <- lines1 if line != "") {
      val split = line.split("\\|")
      val owner = split(0)
      val objecName = split(1)
      val procedure = split(2)
      val sql =
        s"""
           |SELECT DBMS_METADATA.GET_DDL('$procedure','$objecName','$owner') as proc_ddl FROM DUAL
           |""".stripMargin
      val pst = conn.prepareStatement(sql)
      val res = pst.executeQuery()
      while (res.next()) {
        val procDdl = res.getString("proc_ddl")
        println(procDdl)
        sb.append(procDdl).append("\n")
      }
      pst.close()

    }

    conn.close()

    val writer = new PrintWriter(new File(output))
    writer.write(sb.toString().replaceAll("\\+", ""))
    writer.close()

  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51