Home » SQL & PL/SQL » SQL & PL/SQL » merged: extracting query from a procedure
merged: extracting query from a procedure [message #301114] Tue, 19 February 2008 04:09 Go to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
I want to create a generalised pl/sql block in which if a procedure existing in the database is called then the code should be such that that the output of the block is the query contained in the respective procedure entered.i.e. changes like deletion of if statements, semicolons and replacement of '' with ' should take place n query should be outputed.Could someone please provide a solution or atleast give me an idea on how to go about it? Sad
Urgent:Extracting query from a procedure. [message #301115 is a reply to message #301114] Tue, 19 February 2008 04:13 Go to previous messageGo to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
I want to create a generalised pl/sql block in which if a procedure existing in the database is called then the code should be such that that the output of the block is the query contained in the respective procedure entered.i.e. changes like deletion of if statements, semicolons and replacement of '' with ' should take place n query should be outputed.Could someone please provide a solution or atleast give me an idea on how to go about it? Sad
Re: URGENT: Extracting query from a procedure [message #301118 is a reply to message #301114] Tue, 19 February 2008 04:17 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Not clear what you actually want.

But the content of the stored procedure can be found in the data dictionary view USER_SOURCE.

But a stored procedure doesn't necessarily have to have queries in it.
Also: previous versions of stored procedures are not kept in the database, that is something you'll have to do on your own (versioning systems galore to be found)

HTH
Marc
Re: Urgent:Extracting query from a procedure. [message #301120 is a reply to message #301115] Tue, 19 February 2008 04:26 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
search for SYS_REFCURSOR or REF CURSOR.


regards,
Re: Urgent:Extracting query from a procedure. [message #301121 is a reply to message #301115] Tue, 19 February 2008 04:27 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member

It will be ideal if you could also explain by giving us an example what you are really trying to achieve.

Regards

Raj

P.S : Ignore my post I think I have completely misunderstood the question. So I have removed the link.

[Updated on: Tue, 19 February 2008 04:29]

Report message to a moderator

Re: URGENT: Extracting query from a procedure [message #301124 is a reply to message #301118] Tue, 19 February 2008 04:29 Go to previous messageGo to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
Ok...suppose i create a stored procedure which has a query in it. and now i want to create a pl/sql block in which if that procedure is given as an input, the query contained in it should be the output.for example if the procedure i gave as input is:

PROCEDURE DAC_SP_AvailableCreditRep(oc_CreditCursor OUT creditcursor,iv_CompanyNumber IN VARCHAR2,iv_CostCenter IN VARCHAR2)IS
vl_SQLstr LONG;
BEGIN
vl_SQLstr := 'SELECT
D.DEAL_ID DEAL_ID,
SUM(CREDIT_BY_PAYMENT),
CE.NAME NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST) CLOSED_DATE_ACT,
DB.CHARGE_TO_CODE CHARGE_TO_CODE,
Y.CC GL_COST_CENTER,
Y.CO GL_COMPANY_NUMBER,
Y.HIER CCT_HIER_PT_CD
FROM
(
SELECT ELI.EXPENSE_LINE_NUMBER,
ELI.EXPENSE_STATUS_CODE,
ELI.EXPENSE_AMOUNT,
(PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) APPLIED,
PR.RESERVE_AMOUNT,
PR.RESERVE_AMOUNT - (PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) CREDIT_BY_PAYMENT
FROM DAC.EXPENSE_LINE_ITEM ELI,
DAC.PAYMENT_RESERVE PR
WHERE ELI.EXPENSE_LINE_NUMBER = PR.EXPENSE_LINE_NUMBER
AND ELI.EXPENSE_TYPE_CODE = ''A''
AND ELI.EXPENSE_STATUS_CODE NOT IN (''DEL'',''ARC'')
) X,
DAC.DEAL_EXPENSE DE,
DAC.DEAL_VIEW D,
DAC.DEAL_BOOK DB,
CED.CORE_ENTITY CE,
(
SELECT P.ENUMERATE_ID,
PRT.PRODUCT_REGISTRY_ID,
PRT.GL_COMPANY_NUMBER CO,
PRT.GL_COST_CENTER CC,
CC.CCT_HIER_PT_CD HIER
FROM DAC.PRODUCT_REGION_TERMS PRT,
DAC.MVW_DAC_PRODUCT P,
CED.COST_CENTER CC
WHERE PRT.REGION = ''US''
AND PRT.PRODUCT_REGISTRY_ID = P.COMP_ID
AND PRT.GL_COMPANY_NUMBER = CC.CPY_NO
AND PRT.GL_COST_CENTER = CC.CCT_NO';
---------------START IF-----------------------
IF iv_CompanyNumber <> ' ' THEN
vl_SQLstr := vl_SQLstr ||' '|| ' AND PRT.GL_COMPANY_NUMBER IN (' || iv_CompanyNumber || ')';
END IF;
IF iv_CostCenter <> ' ' THEN
vl_SQLstr := vl_SQLstr ||' '|| 'AND PRT.GL_COST_CENTER IN (' || iv_CostCenter ||' )';
END IF;
-------------END IF----------------------------
vl_SQLstr := vl_SQLstr ||' '|| ') Y
WHERE
X.CREDIT_BY_PAYMENT != 0
AND X.EXPENSE_LINE_NUMBER = DE.EXPENSE_LINE_NUMBER
AND DE.DEAL_REGISTRY_ID = D.DEAL_ID
AND D.DEAL_ID = DB.DEAL_REGISTRY_ID
AND D.CLIENT_ID = CE.ENT_ID
AND D.GPI3_ID = Y.ENUMERATE_ID
GROUP BY
D.DEAL_ID,
CE.NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST),
DB.CHARGE_TO_CODE,Y.CO,Y.CC,Y.HIER
ORDER BY Y.CO, Y.CC';
OPEN oc_CreditCursor FOR vl_SQLstr;
END DAC_SP_AvailableCreditRep;



i require the following query to be outputed:

SELECT
D.DEAL_ID DEAL_ID,
SUM(CREDIT_BY_PAYMENT),
CE.NAME NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST) CLOSED_DATE_ACT,
DB.CHARGE_TO_CODE CHARGE_TO_CODE,
Y.CC GL_COST_CENTER,
Y.CO GL_COMPANY_NUMBER,
Y.HIER CCT_HIER_PT_CD
FROM
(
SELECT ELI.EXPENSE_LINE_NUMBER,
ELI.EXPENSE_STATUS_CODE,
ELI.EXPENSE_AMOUNT,
(PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) APPLIED,
PR.RESERVE_AMOUNT,
PR.RESERVE_AMOUNT - (PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) CREDIT_BY_PAYMENT
FROM DAC.EXPENSE_LINE_ITEM ELI,
DAC.PAYMENT_RESERVE PR
WHERE ELI.EXPENSE_LINE_NUMBER = PR.EXPENSE_LINE_NUMBER
AND ELI.EXPENSE_TYPE_CODE = 'A'
AND ELI.EXPENSE_STATUS_CODE NOT IN ('DEL','ARC')
) X,
DAC.DEAL_EXPENSE DE,
DAC.DEAL_VIEW D,
DAC.DEAL_BOOK DB,
CED.CORE_ENTITY CE,
(
SELECT P.ENUMERATE_ID,
PRT.PRODUCT_REGISTRY_ID,
PRT.GL_COMPANY_NUMBER CO,
PRT.GL_COST_CENTER CC,
CC.CCT_HIER_PT_CD HIER
FROM DAC.PRODUCT_REGION_TERMS PRT,
DAC.MVW_DAC_PRODUCT P,
CED.COST_CENTER CC
WHERE PRT.REGION = 'US'
AND PRT.PRODUCT_REGISTRY_ID = P.COMP_ID
AND PRT.GL_COMPANY_NUMBER = CC.CPY_NO
AND PRT.GL_COST_CENTER = CC.CCT_NO
AND PRT.GL_COMPANY_NUMBER IN ('10')
AND PRT.GL_COST_CENTER IN ('143' )
) Y
WHERE
X.CREDIT_BY_PAYMENT != 0
AND X.EXPENSE_LINE_NUMBER = DE.EXPENSE_LINE_NUMBER
AND DE.DEAL_REGISTRY_ID = D.DEAL_ID
AND D.DEAL_ID = DB.DEAL_REGISTRY_ID
AND D.CLIENT_ID = CE.ENT_ID
AND D.GPI3_ID = Y.ENUMERATE_ID
GROUP BY
D.DEAL_ID,
CE.NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST),
DB.CHARGE_TO_CODE,Y.CO,Y.CC,Y.HIER
ORDER BY Y.CO,
Y.CC



i've just given 10 and 143 as inputs here cause they're one of the valid inputs here...Am i making myself any clearer now?
Re: Urgent:Extracting query from a procedure. [message #301127 is a reply to message #301121] Tue, 19 February 2008 04:33 Go to previous messageGo to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
Ok...suppose i create a stored procedure which has a query in it. and now i want to create a pl/sql block in which if that procedure is given as an input, the query contained in it should be the output.for example if the procedure i want to give as input is:

PROCEDURE DAC_SP_AvailableCreditRep(oc_CreditCursor OUT creditcursor,iv_CompanyNumber IN VARCHAR2,iv_CostCenter IN VARCHAR2)IS
vl_SQLstr LONG;
BEGIN
vl_SQLstr := 'SELECT
D.DEAL_ID DEAL_ID,
SUM(CREDIT_BY_PAYMENT),
CE.NAME NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST) CLOSED_DATE_ACT,
DB.CHARGE_TO_CODE CHARGE_TO_CODE,
Y.CC GL_COST_CENTER,
Y.CO GL_COMPANY_NUMBER,
Y.HIER CCT_HIER_PT_CD
FROM
(
SELECT ELI.EXPENSE_LINE_NUMBER,
ELI.EXPENSE_STATUS_CODE,
ELI.EXPENSE_AMOUNT,
(PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) APPLIED,
PR.RESERVE_AMOUNT,
PR.RESERVE_AMOUNT - (PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) CREDIT_BY_PAYMENT
FROM DAC.EXPENSE_LINE_ITEM ELI,
DAC.PAYMENT_RESERVE PR
WHERE ELI.EXPENSE_LINE_NUMBER = PR.EXPENSE_LINE_NUMBER
AND ELI.EXPENSE_TYPE_CODE = ''A''
AND ELI.EXPENSE_STATUS_CODE NOT IN (''DEL'',''ARC'')
) X,
DAC.DEAL_EXPENSE DE,
DAC.DEAL_VIEW D,
DAC.DEAL_BOOK DB,
CED.CORE_ENTITY CE,
(
SELECT P.ENUMERATE_ID,
PRT.PRODUCT_REGISTRY_ID,
PRT.GL_COMPANY_NUMBER CO,
PRT.GL_COST_CENTER CC,
CC.CCT_HIER_PT_CD HIER
FROM DAC.PRODUCT_REGION_TERMS PRT,
DAC.MVW_DAC_PRODUCT P,
CED.COST_CENTER CC
WHERE PRT.REGION = ''US''
AND PRT.PRODUCT_REGISTRY_ID = P.COMP_ID
AND PRT.GL_COMPANY_NUMBER = CC.CPY_NO
AND PRT.GL_COST_CENTER = CC.CCT_NO';
---------------START IF-----------------------
IF iv_CompanyNumber <> ' ' THEN
vl_SQLstr := vl_SQLstr ||' '|| ' AND PRT.GL_COMPANY_NUMBER IN (' || iv_CompanyNumber || ')';
END IF;
IF iv_CostCenter <> ' ' THEN
vl_SQLstr := vl_SQLstr ||' '|| 'AND PRT.GL_COST_CENTER IN (' || iv_CostCenter ||' )';
END IF;
-------------END IF----------------------------
vl_SQLstr := vl_SQLstr ||' '|| ') Y
WHERE
X.CREDIT_BY_PAYMENT != 0
AND X.EXPENSE_LINE_NUMBER = DE.EXPENSE_LINE_NUMBER
AND DE.DEAL_REGISTRY_ID = D.DEAL_ID
AND D.DEAL_ID = DB.DEAL_REGISTRY_ID
AND D.CLIENT_ID = CE.ENT_ID
AND D.GPI3_ID = Y.ENUMERATE_ID
GROUP BY
D.DEAL_ID,
CE.NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST),
DB.CHARGE_TO_CODE,Y.CO,Y.CC,Y.HIER
ORDER BY Y.CO, Y.CC';
OPEN oc_CreditCursor FOR vl_SQLstr;
END DAC_SP_AvailableCreditRep;



i require the following query to be outputed through the code:

SELECT
D.DEAL_ID DEAL_ID,
SUM(CREDIT_BY_PAYMENT),
CE.NAME NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST) CLOSED_DATE_ACT,
DB.CHARGE_TO_CODE CHARGE_TO_CODE,
Y.CC GL_COST_CENTER,
Y.CO GL_COMPANY_NUMBER,
Y.HIER CCT_HIER_PT_CD
FROM
(
SELECT ELI.EXPENSE_LINE_NUMBER,
ELI.EXPENSE_STATUS_CODE,
ELI.EXPENSE_AMOUNT,
(PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) APPLIED,
PR.RESERVE_AMOUNT,
PR.RESERVE_AMOUNT - (PR.ALLOCATED_AMOUNT + PR.PICKEDUP_AMOUNT) CREDIT_BY_PAYMENT
FROM DAC.EXPENSE_LINE_ITEM ELI,
DAC.PAYMENT_RESERVE PR
WHERE ELI.EXPENSE_LINE_NUMBER = PR.EXPENSE_LINE_NUMBER
AND ELI.EXPENSE_TYPE_CODE = 'A'
AND ELI.EXPENSE_STATUS_CODE NOT IN ('DEL','ARC')
) X,
DAC.DEAL_EXPENSE DE,
DAC.DEAL_VIEW D,
DAC.DEAL_BOOK DB,
CED.CORE_ENTITY CE,
(
SELECT P.ENUMERATE_ID,
PRT.PRODUCT_REGISTRY_ID,
PRT.GL_COMPANY_NUMBER CO,
PRT.GL_COST_CENTER CC,
CC.CCT_HIER_PT_CD HIER
FROM DAC.PRODUCT_REGION_TERMS PRT,
DAC.MVW_DAC_PRODUCT P,
CED.COST_CENTER CC
WHERE PRT.REGION = 'US'
AND PRT.PRODUCT_REGISTRY_ID = P.COMP_ID
AND PRT.GL_COMPANY_NUMBER = CC.CPY_NO
AND PRT.GL_COST_CENTER = CC.CCT_NO
AND PRT.GL_COMPANY_NUMBER IN ('10')
AND PRT.GL_COST_CENTER IN ('143' )
) Y
WHERE
X.CREDIT_BY_PAYMENT != 0
AND X.EXPENSE_LINE_NUMBER = DE.EXPENSE_LINE_NUMBER
AND DE.DEAL_REGISTRY_ID = D.DEAL_ID
AND D.DEAL_ID = DB.DEAL_REGISTRY_ID
AND D.CLIENT_ID = CE.ENT_ID
AND D.GPI3_ID = Y.ENUMERATE_ID
GROUP BY
D.DEAL_ID,
CE.NAME,
NVL(D.CLOSED_DATE_ACT, D.CLOSED_DATE_EST),
DB.CHARGE_TO_CODE,Y.CO,Y.CC,Y.HIER
ORDER BY Y.CO,
Y.CC



i've just given 10 and 143 as inputs here cause they're one of the valid inputs here...Hope i'm making myself clearer now..
Re: URGENT: Extracting query from a procedure [message #301129 is a reply to message #301124] Tue, 19 February 2008 04:37 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Just a bit clearer Wink

What if the procedure has more than one query in it?
What if the query in that procedure is built dynamicalle?
What if ...

A lots of what-if could follow.

Basically what you want is to retrieve a piece of the code of a stored procedure.

As far as I know there is no standard-package to extract queries from the code of a stored procedure.

That's something you'll have to write, because you know the requirements better than us.

For the given procedure get the code (USER_SOURCE is your base data dictionary view as I already told) and parse it via a self-written procedure or program.
Re: URGENT: Extracting query from a procedure [message #301130 is a reply to message #301129] Tue, 19 February 2008 04:40 Go to previous messageGo to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
Well i need to create a generalised block but i thought maybe first i'd pick one procedure DAC_SPAVAILABLECREDITREP up and try writing the code for it and then try generalising it.I'm having difficulty with proceeding with it.
Re: URGENT: Extracting query from a procedure [message #301136 is a reply to message #301130] Tue, 19 February 2008 05:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why why why why ??????
http://www.orafaq.com/forum/t/97512/94420/

Regards

Raj
Re: URGENT: Extracting query from a procedure [message #301140 is a reply to message #301136] Tue, 19 February 2008 05:36 Go to previous messageGo to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
??? I didn't get you..I just posted it in both forums so that more ppl cud access it!
Re: URGENT: Extracting query from a procedure [message #301141 is a reply to message #301140] Tue, 19 February 2008 05:39 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
beatnik wrote on Tue, 19 February 2008 12:36
??? I didn't get you..I just posted it in both forums so that more ppl cud access it!


So you think that people don't look at both fora?
As far as I know it's one of the basic rules of forum policy to not cross posts at all, ever.

But don't worry, in a moment a moderator will come along and merge these two topics into one Wink

I suggest we stop posting in this thread and keep the other one alive.

Or beatnik must have enough information/answers to his question at this point Wink
Re: URGENT: Extracting query from a procedure [message #301147 is a reply to message #301141] Tue, 19 February 2008 05:51 Go to previous messageGo to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
okay...will keep that in mind the next time...n haven't gathered enuf information yet 2 come up with something solid... Sad Searching searching searching...
Re: URGENT: Extracting query from a procedure [message #301148 is a reply to message #301147] Tue, 19 February 2008 05:58 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
beatnik wrote on Tue, 19 February 2008 12:51
Searching searching searching...


That's the spirit: keep Searching Wink
Re: URGENT: Extracting query from a procedure [message #301159 is a reply to message #301148] Tue, 19 February 2008 06:25 Go to previous messageGo to next message
beatnik
Messages: 8
Registered: February 2008
Junior Member
Laughing
Re: Urgent:Extracting query from a procedure. [message #301177 is a reply to message #301127] Tue, 19 February 2008 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clear as mud as well in the form than in the content.

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: URGENT: Extracting query from a procedure [message #301186 is a reply to message #301147] Tue, 19 February 2008 07:56 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you are keeping something in mind, read OraFAQ Forum Guide abuot formatiting your post, IM speak and all other rules about posting.

Regards
Michel
Previous Topic: SQL conditional show group by number
Next Topic: A query related to Collapsing nulls /pivoing of data
Goto Forum:
  


Current Time: Sat Dec 03 13:42:25 CST 2016

Total time taken to generate the page: 0.07760 seconds