Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to extract Stored Procedure codes so that it can be deployed in a 'command' tab of SQL Developer

3145368Mar 2 2016 — edited Mar 2 2016

Not sure I'm phrasing my question correctly so that it is understandable, but here goes...

I have a Stored Procedure 'FETCHFEES' (see below) where I need to modify so that I can run it in a 'normal' SQL window.

What is the best way to modify it with the RECURRING input variables (see the blue below)? Can I use multiple WITH clauses? I was thinking something in the vein of:

WITH TIER1 AS

(

	SELECT
	RANGE_TO,
	FEE_RATE
	INTO
	Tier1UpperLimit,
	Tier1FeeRate
	FROM
	XXX.FEE_RATES
	WHERE
	-- Select Load or Fare Payment Fee
	FEE_CODE_DESCRIPTION = 'Load or Fare Payment Fee'
	-- Get fee rates
	AND FEE_CODE = FEE_CODE
	AND TIER = 1

), TIER2 AS

(

	SELECT
	RANGE_TO,
	FEE_RATE
	INTO
	Tier2UpperLimit,
	Tier2FeeRate
	FROM
	XXX.FEE_RATES
	WHERE
	-- Select Load or Fare Payment Fee
	FEE_CODE_DESCRIPTION = 'Load or Fare Payment Fee'
	-- Get fee rates
	AND FEE_CODE = FEE_CODE
	AND TIER = 2

), TIER3 AS

(

	SELECT
	FEE_RATE
	INTO
	Tier3FeeRate
	FROM
	XXX.FEE_RATES
	WHERE
	-- Select Load or Fare Payment Fee
	FEE_CODE_DESCRIPTION = 'Load or Fare Payment Fee'
	-- Get fee rates
	AND FEE_CODE = FEE_CODE
	AND TIER = 3

)

=============

PROCEDURE FETCHFEES ( vCursor OUT T_Cur, vMonYear IN VARCHAR2)
AS
ReportDate VARCHAR2(10) := UPPER( NVL( vMonYear, TO_CHAR( ADD_MONTHS( SYSDATE, -1 ), 'MON-YYYY' ) ) );
Tier1UpperLimit	NUMBER(15,2)	:= 0;
Tier2UpperLimit	NUMBER(15,2)	:= 0;
Tier1FeeRate	NUMBER(15,6)	:= 0;
Tier2FeeRate	NUMBER(15,6)	:= 0;
Tier3FeeRate	NUMBER(15,6)	:= 0;
TotTxn	NUMBER	:= 0;
Tier1Fees	NUMBER(15,2)	:= 0;
Tier2Fees	NUMBER(15,2)	:= 0;
Tier3Fees	NUMBER(15,2)	:= 0;
BEGIN
SELECT
RANGE_TO,
FEE_RATE
INTO
Tier1UpperLimit,
Tier1FeeRate
FROM
XXX.FEE_RATES
WHERE
-- Select Load or Fare Payment Fee
FEE_CODE_DESCRIPTION = 'Load or Fare Payment Fee'
-- Get fee rates
AND FEE_CODE = FEE_CODE
AND TIER = 1;

	SELECT
	RANGE_TO,
	FEE_RATE
	INTO
	Tier2UpperLimit,
	Tier2FeeRate
	FROM
	XXX.FEE_RATES
	WHERE
	-- Select Load or Fare Payment Fee
	FEE_CODE_DESCRIPTION = 'Load or Fare Payment Fee'
	-- Get fee rates
	AND FEE_CODE = FEE_CODE
	AND TIER = 2;

	SELECT
	FEE_RATE
	INTO
	Tier3FeeRate
	FROM
	XXX.FEE_RATES
	WHERE
	-- Select Load or Fare Payment Fee
	FEE_CODE_DESCRIPTION = 'Load or Fare Payment Fee'
	-- Get fee rates
	AND FEE_CODE = FEE_CODE
	AND TIER = 3;

	-- Get total number of transactions
	-- included in fee calculations
	SELECT
	SUM( TOTAL_TXNS ) INTO TotTxn
	FROM (
	SELECT
	COUNT( * ) AS TOTAL_TXNS
	FROM
	HXXXXX.V_XX_XX_FARE_TXNS_YY_11
	WHERE
	TO_CHAR( SETTLEMENT_DATE, 'MON-YYYY' ) = ReportDate
	UNION
	SELECT
	COUNT( * ) AS TOTAL_TXNS
	FROM
	HXXX.V_13_31_LOAD_TXNS_CR_22
	WHERE
	TO_CHAR( SETTLEMENT_DATE, 'MON-YYYY' ) = ReportDate
	UNION
	SELECT
	COUNT( * ) AS TOTAL_TXNS
	FROM
	HXXX.V_13_31_ADJUST_TXNS_CR_22
	WHERE
	TO_CHAR( SETTLEMENT_DATE, 'MON-YYYY' ) = ReportDate
	);

	-- Calculate tier1 fees
	IF ( TotTxn <= Tier1UpperLimit ) THEN
	Tier1Fees := TotTxn * Tier1FeeRate;
	ELSE
	Tier1Fees := Tier1UpperLimit * Tier1FeeRate;
	END IF;

	-- Calculate tier2 fees
	IF ( TotTxn > Tier1UpperLimit ) THEN
	IF ( TotTxn <= Tier2UpperLimit ) THEN
	Tier2Fees := ( TotTxn - Tier1UpperLimit ) * Tier2FeeRate;
	ELSE
	Tier2Fees := ( Tier2UpperLimit - Tier1UpperLimit ) * Tier2FeeRate;
	END IF;
	END IF;

	-- Calculate tier3 fees
	IF ( TotTxn > Tier2UpperLimit ) THEN
	Tier3Fees := ( TotTxn - Tier2UpperLimit ) * Tier3FeeRate;
	END IF;

OPEN vCursor FOR

( MAIN SELECT QUERY GOES HERE ........)

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 30 2016

Added on Mar 2 2016

#general-database-discussions

4 comments

928 views