-
Task
-
Resolution: Unresolved
-
Major
-
None
-
Linux
HI Team,
I have created 2 String parameter that is SCHEMA_OWNER & SCHEMA_ROLE within Pipeline project and trying to run parameter based sql script(grant_role.sql) with script type as file and passing params as given below.
{stage('SQLRunner_DB02'){
step([
$class: 'SQLPlusRunnerBuilder',
credentialsId:'BASE_OWNER',
instance:'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=127.0.0.1)(Port=1521))(CONNECT_DATA=(SID=ORC:L)))',
scriptType:'file',
script: 'grant_role.sql ${SCHEMA_OWNER} ${SCHEMA_ROLE}',
scriptContent:'',
customOracleHome:'/usr/lib/oracle/19.5/client64'])
Output:::
[DB_Test] $ /usr/lib/oracle/19.5/client64/bin/sqlplus -L BASE_OWNER/BASE_OWNER@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=127.0.0.1)(Port=1521))(CONNECT_DATA=(SID=ORCL))) "@/var/lib/jenkins/workspace/DB_Test/grant_role.sql EST_OWNER TEST_ROLE"
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 2 12:04:33 2020
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Mon Mar 02 2020 12:04:33 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SP2-0310: unable to open file "/var/lib/jenkins/workspace/DB_Test/grant_role.sql TEST_OWNER TEST_ROLE"
Please suggest a way to overcome this problem
[JENKINS-61297] Parameter to SQLScript from pipeline project using class SQLPlusRunnerBuilder
Vinaypal Singh
added a comment - Thanks For response.
You mean to say from script type as File, we will call one normal scripts from inside we need to call our parameter based script, but i think it will not solve the purpose of passing parameters to sql scripts, how inner sql scripts will identify the parameters.
Can you please elaborate more about your suggestion to solve this problem.
Vinaypal Singh
added a comment - Thanks For response.
You mean to say from script type as File, we will call one normal scripts from inside we need to call our parameter based script, but i think it will not solve the purpose of passing parameters to sql scripts, how inner sql scripts will identify the parameters.
Can you please elaborate more about your suggestion to solve this problem. No, I meant use another plugin (Iike Managed Scripts) and manually create temp.sql file with contents "grant_role.sql TEST_OWNER TEST_ROLE".
After that use temp.sql as you script to be called by SQL*Plus.
Sorry, there is no support for SQL*Plus parameters, maybe you can create manually a SQL script before calling it.