Home » SQL & PL/SQL » SQL & PL/SQL » how to work around the substitution variables in plsql (Oracle 12c )
how to work around the substitution variables in plsql [message #670399] Mon, 02 July 2018 22:43 Go to next message
mgag
Messages: 1
Registered: July 2018
Junior Member
Help needed to turn this into a stored procedure.
When using dbms_output and copy and paste the output query, the output query runs fine with expected results.
However I am not able to call the v_sql1 as execute immediate due to "COLUMN"
What can I do to make this work?


set define off
set serveroutput on size 1000000
declare
v_sql1 clob;
--v_sql2 clob;
v_date1 varchar2(10) := '1/1/2018';
v_date2 varchar2(10) := '2018-01-01';

begin

v_sql1 := 'COLUMN m1_col NEW_VALUE m1
COLUMN m2_col NEW_VALUE m2
COLUMN m3_col NEW_VALUE m3
COLUMN m4_col NEW_VALUE m4
COLUMN m5_col NEW_VALUE m5
COLUMN m6_col NEW_VALUE m6
COLUMN m7_col NEW_VALUE m7
COLUMN m8_col NEW_VALUE m8
COLUMN m9_col NEW_VALUE m9
COLUMN m10_col NEW_VALUE m10
COLUMN m11_col NEW_VALUE m11
COLUMN m12_col NEW_VALUE m12
set define on
WITH months_to_pivot AS
(
SELECT LEVEL AS num, TO_CHAR ( ADD_MONTHS (to_date('''||v_date1||''',''mm/dd/yyyy''), LEVEL - 1) ,''MON_YY'') AS str
FROM dual
CONNECT BY LEVEL <= 12
)
SELECT *
FROM months_to_pivot
PIVOT ( MIN (str)
FOR num IN ( 1 AS m1_col, 2 AS m2_col, 3 AS m3_col, 4 AS m4_col, 5 AS m5_col, 6 AS m6_col , 7 AS m7_col, 8 AS m8_col , 9 AS m9_col , 10 AS m10_col, 11 AS m11_col , 12 AS m12_col )
);
WITH data_to_pivot AS
(
SELECT CP_CONTRACT.ENT_OWNER,
PIPELINE.SHORT_NAME AS PIPELINE,
CP_CONTRACT.SHIPPER_COMPANY AS Shipper,
CP_CONTRACT_DETAIL.Entitlement_Owner,
CP_CONTRACT.CONTRACT_ID AS Contract,
CP_CONTRACT.MASTER_CONTRACT_ID,
CP_RATE_SCHEDULE_XREF.RATE_SCHEDULE,
CASE WHEN EXTRACT (YEAR FROM cp_contract.end_date) > EXTRACT (YEAR FROM DATE '''||v_date2||''') THEN mth_mdq
WHEN TO_NUMBER ( ( ( EXTRACT ( MONTH FROM cp_contract.end_date)|| SUBSTR (EXTRACT ( YEAR FROM cp_contract.end_date), -2)))) <
MOD (cp_contract_detail.inv_month + 12- EXTRACT (MONTH FROM DATE '''||v_date2||'''),12)+ 1|| 19 THEN 0
ELSE CP_CONTRACT_DETAIL.mth_mdq
END mth_mdq,
MOD (cp_contract_detail.inv_month + 12 - EXTRACT (MONTH FROM DATE '''||v_date2||'''), 12) + 1 AS col_num
FROM PIPELINE,
CP_CONTRACT,
CP_CONTRACT_DETAIL,
CP_RATE_SCHEDULE_XREF
WHERE CP_CONTRACT.CP_CONTRACT_SEQ = CP_CONTRACT_DETAIL.CP_CONTRACT_SEQ
AND CP_CONTRACT.PL_NUMBER = PIPELINE.PL_NUMBER
AND CP_CONTRACT.RATE_SCHEDULE_SEQ = CP_RATE_SCHEDULE_XREF.RATE_SCHEDULE_SEQ
AND CP_CONTRACT.end_date >= TO_DATE ('''||v_date1||''', ''mm/dd/yyyy'')
)
SELECT * FROM data_to_pivot
PIVOT ( SUM (mth_mdq)
FOR col_num IN ( 1 AS &m1, 2 AS &m2, 3 AS &m3 , 4 AS &m4, 5 AS &m5 , 6 AS &m6, 7 AS &m7 , 8 AS &m8 , 9 AS &m9 , 10 AS &m10, 11 AS &m11, 12 AS &m12 ) )
ORDER BY 1';

--dbms_output.put_line (v_sql1);
execute immediate v_sql1;

end ;
/
Re: how to work around the substitution variables in plsql [message #670401 is a reply to message #670399] Tue, 03 July 2018 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66682
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

COLUMN is a SQL*Plus command NOT a SQL or PL/SQL one.
If you want specific format in DBMS_OUTPUT then you have to format it yourself but format is a client program job (like COLUMN statements tells how SQL*Plus has to format your columns, note this is SQL*Plus not PL/SQL that formats) and PL/SQL is NOT a client program; DBMS_OUTPUT should be used ONLY for debugging.

Re: how to work around the substitution variables in plsql [message #670404 is a reply to message #670401] Tue, 03 July 2018 00:34 Go to previous messageGo to next message
_jum
Messages: 557
Registered: February 2008
Senior Member
Question answered in Oracle Community.
Re: how to work around the substitution variables in plsql [message #670634 is a reply to message #670404] Tue, 17 July 2018 06:26 Go to previous message
ajaychinna
Messages: 2
Registered: July 2018
Junior Member
Thanks for providing the information

Previous Topic: Generate New Excel format (XLSX) files using Oracle PLSQL
Next Topic: query performance
Goto Forum:
  


Current Time: Wed Nov 13 07:00:19 CST 2019