Subscribe:
RSS feed
-
Subscribe
Subscribed
Already have a WordPress.com account? Log in now.
— Note: only works if the ALL_TABLES object is up to date – read up on the ALL_TABLES first SELECT sum(num_rows) FROM all_tables WHERE owner LIKE ‘MyObjectOwnerName’
January 18, 2012
You can display your own custom comments, debug information or output from a function or package by specifying the output using DBMS_OUTPUT() If you’re running the code and not seeing the output when executing a command start by running “set serveroutput on feedback off;” end by running “set serveroutput off feedback on;”
January 18, 2012
You must first create a “Database Link”, which is a schema object that allows you to access objects in one database, from another. The example below assumes you have already created a database link that allows adequate permissions. Let’s assume you have two Oracle database instances, one named PROD which contains your live production data, […]
January 18, 2012
exp user/pswd@mySchema exp USER/PSWD@mySchema file=c:\temp\DBNAME_20080806.dmp exp scott/tiger file=mySchema.dmp log=mySchema.log tables=mySchema rows=yes indexes=no
January 18, 2012
Dim strDate as Date = “1/5/03” Dim strOracleDate as String = strDate.ToString(“dd-MMM-yy”).ToUpper()
January 18, 2012
UPDATE FROM ** SQL version *** UPDATE erp.rse SET expardesc = r.expardesc || ‘ – ‘ || p.parname FROM erp.rse r INNER JOIN erp.omqc_paramdesc p ON p.rsid = r.rsid AND r.rtype = ‘PSVP’ *** Oracle vesrion *** UPDATE erp.rse SET rse.expardesc = rse.expardesc || ‘ – ‘ || (SELECT p.parname FROM erp.omqc_paramdesc p WHERE p.exparid […]
January 18, 2012
STRING CONCAT (this is an alt/preferred version of wm_concat), can also use LISTAGG as of Oracle 11.2 SELECT i.imacs_id, SUBSTR(MAX(SYS_CONNECT_BY_PATH(ia.long_val1,’,’)),2) ImpactAgents FROM imacs.unique_rec i LEFT OUTER JOIN ( SELECT impa.imacs_id, dia.long_val1, row_number() OVER (PARTITION BY impa.imacs_id ORDER BY dia.long_val1) rn FROM imacs.part_a_impact_agents impa LEFT OUTER JOIN imacs.decoder dia ON dia.decoder_id = impa.impact_agents_cd ) ia ON […]
January 18, 2012
Oracle’s DECODE() function is pretty slick for returning a specific value based on a particular field, such as in the following: SELECT supplier_code, DECODE(supplier_id, 10000, ‘IBM’, 10001, ‘Microsoft’, 10002, ‘Hewlett Packard’, ‘Gateway’) supplier_name FROM suppliers; However, what happens when you need to specify a result based on the difference between two numbers. This can be […]
maverick web 
January 18, 2012
0