The relevant query is as follows:
SELECT text
FROM dba_source
WHERE name = ‘<PACKAGE_NAME >’
AND text LIKE ‘%Header:%’;
For example, look for the 2 files that make up the package “PO_INQ_SV’:
TEXT
----------------------------------------------------------------------------------------------------------
/* $Header: POXPOVPS.pls 80.1.7016.2 98/02/24 20:00:59 porting ship $ */
/* $Header: POXPOVPB.pls 80.3.7016.4 98/07/01 16:47:41 porting ship $ */
Notice the results give us the file names that make up the Header/Spec and Body of the package. The B and the S at the end of the 7 letters indicate the Body and Spec file.
Hello There,
ReplyDeleteQuery to Checking Version of Database Package being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.
I am trying to use this in a materialized view and got below error:
SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query
12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and nor primary key constraints are supported for
complex queries.
*Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE
option or create a simple materialized view.
select IR.rowid MV_INST_LOBR_ROWID, J.rowid Job_ROWID, J.* FROM JOB J
LEFT JOIN MV_INST_LOB_R IR ON
(IR.I1503_CUST_AC_NO_PT1 = J.I3200_CUST_AC_NO_PT1 AND IR.I1503_CUST_AC_NO_PT2 = J.I3200_CUST_AC_NO_PT2 AND IR.I1503_INST_SEQ_NO = J.I3200_INST_SEQ_NO)
WHERE IR.I1503_CUST_AC_NO_PT1 IS NOT NULL AND IR.I1503_CUST_AC_NO_PT2 IS NOT NULL
UNION SELECT null, J.rowid Job_ROWID, J.* FROM JOB J WHERE J.I3200_CUST_AC_NO_PT1 IS NULL AND J.I3200_CUST_AC_NO_PT1 IS NULL;
Basically, the intention is to get all the records joining MV_INST_LOB_R and JOB table
where MV_INST_LOB_R do not have record for the joining condition J.I3200_CUST_AC_NO_PT1 / J.I3200_CUST_AC_NO_PT2
Can you help me in converting to simple query, so that I can use simple materialized view.
Follow my new blog if you interested in just tag along me in any social media platforms!
Merci
Preethi
Hello,
ReplyDeleteQuery to Checking Version of Database Package being contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.
I am trying to use this in a materialized view and got below error:
SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query
12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and nor primary key constraints are supported for
complex queries.
*Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE
option or create a simple materialized view.
select IR.rowid MV_INST_LOBR_ROWID, J.rowid Job_ROWID, J.* FROM JOB J
LEFT JOIN MV_INST_LOB_R IR ON
(IR.I1503_CUST_AC_NO_PT1 = J.I3200_CUST_AC_NO_PT1 AND IR.I1503_CUST_AC_NO_PT2 = J.I3200_CUST_AC_NO_PT2 AND IR.I1503_INST_SEQ_NO = J.I3200_INST_SEQ_NO)
WHERE IR.I1503_CUST_AC_NO_PT1 IS NOT NULL AND IR.I1503_CUST_AC_NO_PT2 IS NOT NULL
UNION SELECT null, J.rowid Job_ROWID, J.* FROM JOB J WHERE J.I3200_CUST_AC_NO_PT1 IS NULL AND J.I3200_CUST_AC_NO_PT1 IS NULL;
Basically, the intention is to get all the records joining MV_INST_LOB_R and JOB table
where MV_INST_LOB_R do not have record for the joining condition J.I3200_CUST_AC_NO_PT1 / J.I3200_CUST_AC_NO_PT2
Can you help me in converting to simple query, so that I can use simple materialized view.
Follow my new blog if you interested in just tag along me in any social media platforms!
,Merci
Radhey
Hello There,
ReplyDeleteZoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on Query to Checking Version of Database Package !
I have an excel which is in csv format. I am trying to import this data to the table using sql developer.
But I get the same number throughout the full column as 100005000000000.
It is the field with datatype NUMBER(25,0). Actual number is different.
But nice Article Mate! Great Information! Keep up the good work!
Thank you,
Preethi
Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteGet awesome seo service in Chennai at affordable prices.
ReplyDeleteSmall Business SEO services in Florida : Apparatus is a small business SEO company in Florida. They specialize in helping small businesses to improve their rankings on Google by using organic and paid search strategies.
ReplyDelete