Home » SQL & PL/SQL » SQL & PL/SQL » Checking the parameter inside Procedure (merged 2)
| Checking the parameter inside Procedure (merged 2) [message #643416] |
Wed, 07 October 2015 05:54  |
 |
hypesslearner
Messages: 15
Registered: July 2015
|
Junior Member |
|
|
Hi all ,
PLSQL package code is as below , I am passing a parameter process_name as a runtime parameter as Ghep
Now Based on the ghep value I need to filter rows which I mentioned at the last part of the code as
Is this the way I need to call the parameter inside procedure . Please clarify
create or replace PROCEDURE P_PROCESS(process_name varchar2) IS
VAL VARCHAR2(4000);
BEGIN
select csv into VAL from (SELECT SUBSTR (SYS_CONNECT_BY_PATH ("AC_SP" , ','), 2) csv FROM (SELECT "AC_SP" , ROW_NUMBER () OVER (ORDER BY "AC_SP") rn, COUNT (*) OVER () cnt FROM AA_AME_TRANS.MAP_ACC_COPA) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1);
COMMIT;
EXECUTE IMMEDIATE 'INSERT INTO AA_TRANS.T_COPA ("ACC", "PCR", "D_C", "PSO", "Cost_Center", "Sales_Org", "RG", "Customer", "Product", "Data")
SELECT "ACC",
CAST(''AA_SM_LOAD_INPUT'' AS VARCHAR2(80)) AS "PCR",
CAST(''AA_DC_SDIV'' AS VARCHAR2(80)) AS "D_C",
CAST(''AA_SO_P_SDIV'' AS VARCHAR2(80)) AS "PSO",
CAST(DECODE(REC_TYPE, ''D'', ''AA_CC_''||SUBSTR(COSTCENTER,5), ''AA_CC_TOTAL_SDIV'') AS VARCHAR2(80)) AS "Cost_Center",
CAST(''AA_SDIV'' AS VARCHAR2(80)) AS "RG",
CAST(''AA_CH_9999999999'' AS VARCHAR2(80)) AS "Cust",
CAST(FUN_PCR_PRODUCT("PFT_CTR") AS VARCHAR2(80)) AS "Product",
"Data"
FROM STG.AMERICA
UNPIVOT ("Data" FOR "ACC" IN ('||VAL||'))
JOIN SO_COPA MSO
ON AGVH897 = MSO."SP_SO"
WHERE
PROCESS IN (select SAP_PROCESS_NAME from MAP_PROCESS_NAME where ''PROCESS_VAR'' = ''||process_name||'')';
COMMIT;
END; |
|
|
|
| Checking the parameter inside Procedure [message #643417 is a reply to message #643416] |
Wed, 07 October 2015 05:54  |
|
hypesslearner
Messages: 15
Registered: July 2015
|
Junior Member |
|
|
Hi all ,
PLSQL package code is as below , I am passing a parameter process_name as a runtime parameter as Ghep
Now Based on the ghep value I need to filter rows which I mentioned at the last part of the code as
Is this the way I need to call the parameter inside procedure . Please clarify
create or replace PROCEDURE P_PROCESS(process_name varchar2) IS
VAL VARCHAR2(4000);
BEGIN
select csv into VAL from (SELECT SUBSTR (SYS_CONNECT_BY_PATH ("AC_SP" , ','), 2) csv FROM (SELECT "AC_SP" , ROW_NUMBER () OVER (ORDER BY "AC_SP") rn, COUNT (*) OVER () cnt FROM AA_AME_TRANS.MAP_ACC_COPA) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1);
COMMIT;
EXECUTE IMMEDIATE 'INSERT INTO AA_TRANS.T_COPA ("ACC", "PCR", "D_C", "PSO", "Cost_Center", "Sales_Org", "RG", "Customer", "Product", "Data")
SELECT "ACC",
CAST(''AA_SM_LOAD_INPUT'' AS VARCHAR2(80)) AS "PCR",
CAST(''AA_DC_SDIV'' AS VARCHAR2(80)) AS "D_C",
CAST(''AA_SO_P_SDIV'' AS VARCHAR2(80)) AS "PSO",
CAST(DECODE(REC_TYPE, ''D'', ''AA_CC_''||SUBSTR(COSTCENTER,5), ''AA_CC_TOTAL_SDIV'') AS VARCHAR2(80)) AS "Cost_Center",
CAST(''AA_SDIV'' AS VARCHAR2(80)) AS "RG",
CAST(''AA_CH_9999999999'' AS VARCHAR2(80)) AS "Cust",
CAST(FUN_PCR_PRODUCT("PFT_CTR") AS VARCHAR2(80)) AS "Product",
"Data"
FROM STG.AMERICA
UNPIVOT ("Data" FOR "ACC" IN ('||VAL||'))
JOIN SO_COPA MSO
ON AGVH897 = MSO."SP_SO"
WHERE
PROCESS IN (select SAP_PROCESS_NAME from MAP_PROCESS_NAME where ''PROCESS_VAR'' = ''||process_name||'')';
COMMIT;
END; |
|
|
|
| Re: Checking the parameter inside Procedure [message #643418 is a reply to message #643417] |
Wed, 07 October 2015 06:11 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've got your quotes wrong.
Whenever you use dynamic sql you should assign the dynamic sql to a variable and output the variable using dbms_output or similar to see if you've got it right.
That said I see no reason for any of this to be dynamic, why aren't you using static sql?
|
|
|
|
| Re: Checking the parameter inside Procedure (merged 2) [message #643420 is a reply to message #643416] |
Wed, 07 October 2015 06:25 |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Is this the way I need to call the parameter inside procedure
NO! Definitively no.
1/ You don't need any dynamic query
2/ Use your VARIABLE (and not its value) inside the statement.
Just
INSERT INTO AA_TRANS.T_COPA ("ACC", "PCR", "D_C", "PSO", "Cost_Center", "Sales_Org", "RG", "Customer", "Product", "Data")
SELECT "ACC",
CAST('AA_SM_LOAD_INPUT' AS VARCHAR2(80)) AS "PCR",
CAST('AA_DC_SDIV' AS VARCHAR2(80)) AS "D_C",
CAST('AA_SO_P_SDIV' AS VARCHAR2(80)) AS "PSO",
CAST(DECODE(REC_TYPE, 'D', 'AA_CC_'||SUBSTR(COSTCENTER,5), 'AA_CC_TOTAL_SDIV') AS VARCHAR2(80)) AS "Cost_Center",
CAST('AA_SDIV' AS VARCHAR2(80)) AS "RG",
CAST('AA_CH_9999999999' AS VARCHAR2(80)) AS "Cust",
CAST(FUN_PCR_PRODUCT("PFT_CTR") AS VARCHAR2(80)) AS "Product",
"Data"
FROM STG.AMERICA
UNPIVOT ("Data" FOR "ACC" IN (<here search for varying in-list to know how to use VAL>))
JOIN SO_COPA MSO
ON AGVH897 = MSO."SP_SO"
WHERE
PROCESS IN (select SAP_PROCESS_NAME from MAP_PROCESS_NAME where 'PROCESS_VAR' =process_name)'; |
|
|
|
Goto Forum:
Current Time: Sat Jul 11 10:18:56 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
