批量获取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;

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

cat func_oracle.txt
SYS|GETLONG|FUNCTION
SYS|AV_CACHE_COL|FUNCTION

输出文件如下:

  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;

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()

  }
}

  • 6
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值