场景:需要获取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()
}
}