Query to show the columns in rows on oracle

The Below Query helps to find to show the columns in rows. Here inside the Single Quotes replace your Table or View.

 

SELECT
column_name
FROM user_tab_columns
WHERE table_name=UPPER(‘xxcust_report_param_v’) order by 1;

Element Link report for balances and costing

Query to check get the Element name, Costing, Balancing

select petf.ELEMENT_NAME,pcak1.concatenated_segments COSTING,pcak2.concatenated_segments BALANCING from pay_element_links_f pelf,      pay_element_types_f petf,      PAY_COST_ALLOCATION_KEYFLEX PCAK1,       PAY_COST_ALLOCATION_KEYFLEX PCAK2 where pelf.element_type_id = petf.element_type_id and pelf.COST_ALLOCATION_KEYFLEX_ID = pcak1.COST_ALLOCATION_KEYFLEX_ID and pelf.BALANCING_KEYFLEX_ID = pcak2.COST_ALLOCATION_KEYFLEX_ID and sysdate between petf.EFFECTIVE_START_DATE and petf.EFFECTIVE_END_DATE and sysdate between pelf.EFFECTIVE_START_DATE and pelf.EFFECTIVE_END_DATE

Relation between Location and Organization in oracle apps

select * from hr_all_organization_units hou, hz_locations hl where hou.location_id=hl.location_id

A simple VO Iteration of Rows using While loop in OAF

 

A very simple method to  Iterate VO using While Loop.

 

OAViewObject eVO=am.findViewObject(“xxSuppSumVO”);

eVO.first();
while(eVO.hasNext())
{
System.out.println(“Attributes ————————————> “+eVO.getCurrentRow().getAttribute(0)+eVO.getCurrentRow().getAttribute(1)+eVO.getCurrentRow().getAttribute(2));
eVO.next();
}

Bring your firefox 29 to classic look by installing classic Theme

1

A few days ago, the fine folks at Mozilla released Firefox version 29. The ridiculously high number itself is a result of their attempt to appear as on the ball as Google’s Chrome, which seems to release new versions just for sport. Anyway, this latest Firefox release was the first in a long long while to display any actual change in the user interface, again in an apparent attempt to keep up with Chrome. I’m not a fan of Chrome myself, and have always stayed with Firefox, largely due the Mozilla’s devotion to most of the principles of free and/or open source software. Nothing in particular against Google, but they do seem a bit nosy at times. The new interface for Firefox takes some getting used to. It’s not awful, but some buttons have changed and the tabs look different. Lots of folks seem to hate it. What we’re gonna do here is guide you through the easy process of returning Firefox to its previous user interface in appearance.

It’s pretty simple really. One add-on and you’re in business. Even if you like the new changes, this add-on is handy as it gives you additional things to tweak. The add-on is called Classic Theme Restorer (get it here), and will get your Firefox back to looking like what you’re used to and not like the Chrome wannabe its new appearance implies. Just download it, maybe play with a few settings, and Bob’s your uncle, as they say. I’m not gonna go to the trouble of doing before and after screen-shots, a step by step sequence of what settings to fiddle with, or any of that malarkey, mainly because it’s too much work and you’ll be able to see for yourself what’s changed and what changes back when you install the add-on. It’s really really simple to figure out what to do, and most of it gets done just by installing it. I’ve settled on a blend of features from both new and old, keeping mainly the new curved tabs from the new “Australis” theme, while changing a few things back to the old way. Lots of stuff to play with in the settings here. Have fun.

I’ll take an additional moment here to once more beat the drum for Linux and free software in general, of which Firefox is a great and probably the most popular example. I did a piece a while back on Linux, but to sum up, it’s easy to use, more secure, infinitely configurable, does everything the average user needs to do, is available in an astonishing number of flavors, and best of all, is completely free in (in many cases) every possible sense of the word. Freedom is good. Don’t let the Microsofts and the Apples fool you. Don’t be an insecure sheep. Give it a try. Or at least look into using some of the many great free/open source software products available for all platforms. Some of my favorites include VLC, which is a great player of almost any kind of media you can imagine, the GIMP, which does a lot of what Photoshop does and more, Clementine, an easy to use and lightweight music player/library program (among other things, it handles all 103,000 of my music files), FBReader, which will read almost any kind of e-book format, and of course LibreOffice, a full featured MS Office replacement that is quite compatible for most everyday uses. These are all completely free in every sense of the word, the most important being the freedom thing. The no money thing is nice too.

 

Source

Prepared Statement and Callable Statement in OAF

We use Prepared Statement to Execute a Query or a SQL Statement and we use callable statement to call a package, function, procedure

In general, to invoke a stored procedure from within an entity object or an application module, you need to:
1. Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
2. Bind any variables.
3. Execute the statement.
4. Optionally retrieve the values of any OUT parameters.
5. Close the statement.

For Prepared Statement we use directly for any DML operations. To Execute the SELECT Query we have to use the class Result set to get the Data..

if(pageContext.getParameter(EVENT_PARAM).equals(“TEST”))
{
String   valuetobePassed1=repId+””;
String valuetobePassed2 = null;
System.out.println(“Condition called for test”);
CallableStatement callableStatement = txn.createCallableStatement(“declare begin :outVariable := RET_QUERY_REP.RET_QUERY(:2, :3); end;”,OADBTransaction.DEFAULT); //Refer to the Syntax where the String is a procedure
//Create a JDBC CallableStatement with the PL/SQL block containing the stored procedure invocation
//Bind any variables.
try
{
callableStatement.registerOutParameter(1, Types.VARCHAR); //First Bind Varible should give us out Parameter
callableStatement.setString(2, valuetobePassed1); //Passing a variable to Second Variable
callableStatement.setString(3, valuetobePassed2); //Passing a variable to Second Variable
callableStatement.execute(); // Execute the statement.
outParamValue = (callableStatement.getString(1)).toString(); //Retrieve the values of any OUT parameters.
System.out.println(“The Out Parameter Obtained from the Procedure is “+outParamValue);
}
catch (SQLException sqle)  //Be cautious that execute() throws an SQL Exception
{
System.out.println(“Exception occured”);
}
//Sql Validation whether Query is Right or wrong
pageContext.writeDiagnostics(this, repId+outParamValue, 4);
System.out.println(“The output value is “+repId+”This “+outParamValue);
PreparedStatement transaction = (PreparedStatement)am.getOADBTransaction().createPreparedStatement(outParamValue,0);
OracleResultSet rs = null;  //Result Set to get the Data from the Query
try
{
rs=(OracleResultSet)transaction.executeQuery();
if(rs.next())
{
pageContext.writeDiagnostics(this, “Column one value”+rs.getString(1)+”Column two value”+rs.getString(2), 4 ); //We can see the Output
System.out.println(“Column one value”+rs.getString(1)+”Column two value”+rs.getString(2)); //We can see the Output
}
else
{
rs.close(); //Close the statement.
}
}
catch(Exception e)
{
pageContext.writeDiagnostics(this, “Exception Has occured”, 4 );
System.out.println(“Exception came Hence SQL query is wrong”);
}
}

 

 

 

The package that returns a Query from the table which is used in callable statement

 

CREATE OR REPLACE PACKAGE RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY RET_QUERY_REP
IS
FUNCTION RET_QUERY (P_REP_ID NUMBER, P_VAL VARCHAR2)
RETURN VARCHAR2
IS
QUERYSTR   VARCHAR2 (10000);
TYPE R_CURSOR IS REF CURSOR;
C_REF      R_CURSOR;
LV_TEMP    VARCHAR2 (1000);
LV_TEST    VARCHAR2 (1000);
LV_COLV    VARCHAR2 (1000);
BEGIN
IF P_VAL IS NULL THEN
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL (WHERE_CLAUSE, ’1=1′)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ’1′))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;          –Query without column names
ELSE
SELECT   (‘ FROM ‘|| FROM_CLAUSE|| ‘ WHERE ‘|| NVL2 (WHERE_CLAUSE, WHERE_CLAUSE||’ AND ‘||P_VAL, ’1=1′)|| NVL2 (GROUPBY_CLAUSE, ‘ GROUP BY ‘ || GROUPBY_CLAUSE, ”)|| ‘ ORDER BY ‘|| NVL (ORDERBY_CLAUSE, ’1′))
INTO   QUERYSTR
FROM   XXCUST_REPORT_V
WHERE   REP_ID = P_REP_ID;
END IF;

–DBMS_OUTPUT.PUT_LINE (‘THE QUERY OUTLINE ——-> ‘ || QUERYSTR);
OPEN C_REF FOR
SELECT   XRCV.REP_COLUMN
FROM   XXCUST_REPORT_V XRV, XXCUST_REPORT_COLUMN_V XRCV
WHERE   XRV.REP_ID = XRCV.REP_ID AND XRCV.REP_ID = P_REP_ID;
LOOP
FETCH C_REF INTO   LV_COLV;
EXIT WHEN C_REF%NOTFOUND;
LV_TEST := LV_TEST || LV_COLV || ‘, ‘;
–DBMS_OUTPUT.PUT_LINE(‘THE COLUMN NAMES ARE ——-> ‘||LV_TEST);
END LOOP;
CLOSE C_REF;
LV_TEMP := ‘SELECT ‘ || RPAD (LV_TEST, LENGTH (LV_TEST) – 2) || QUERYSTR;
DBMS_OUTPUT.PUT_LINE (‘THE FINAL QUERY IS  ARE ——-> ‘ || LV_TEMP);
RETURN LV_TEMP;
END RET_QUERY;
END RET_QUERY_REP;

 

 

Firefox OS: Devices and Dark Matter

Originally posted on dietrich.blog:

UPDATE: Scroll down for update on May 26, 2013.

Since beginning work on the Firefox OS project, the number one question I’m asked is “Does it run on my phone?”. Sadly, the answer for almost everyone is “no”. The question itself is interesting though, and shows how people – even geeky technical people – don’t have a good understanding of how mobile devices work, nor the whole business and technical ecosystem that brings these things into the hands of consumers (hm, maybe that’ll be my next blog post). Porting an operating system to a device is tricky work in the best of circumstances and when done without the direct assistance of the various business entities involved in the stack for any single device (OEM, chipset manufacturer, original OS vendor), involves a lot of, well, fiddling around. The kind of fiddling around that voids warranties and turns $600 hardware into a…

View original 536 more words

Oracle: Description for GL_CODE_COMBINATIONS Segments

Originally posted on oraclecafe:

In Oracle E-Business 11.10.5, the GL_CODE_COMBINATIONS segments details are stored in FND_FLEX_VALUES_VL. For each segment we have to map the segment value with the FLEX_VALUE column of the above table. Suppose if there is a code combination id of 8 segments then our query would look like
1
SELECT gcc.segment1
|| ‘-’
|| gcc.segment2
|| ‘-’
|| gcc.segment3
|| ‘-’
|| gcc.segment4
|| ‘-’
|| gcc.segment5
|| ‘-’
|| gcc.segment6
|| ‘-’
|| gcc.segment7
|| ‘-’
|| gcc.segment8 account,
a1.description
|| ‘-’
|| a2.description
|| ‘-’
|| a3.description
|| ‘-’
|| a4.description
|| ‘-’
|| a5.description
|| ‘-’
|| a6.description
|| ‘-’
|| a7.description
|| ‘-’
|| a8.description
description
FROM fnd_flex_values_vl a1,
fnd_flex_values_vl a2,
fnd_flex_values_vl a3,
fnd_flex_values_vl a4,
fnd_flex_values_vl a5,
fnd_flex_values_vl a6,
fnd_flex_values_vl a7,
fnd_flex_values_vl a8,
gl_code_combinations gcc
WHERE a1.flex_value = gcc.segment1
AND a2.flex_value = gcc.segment2
AND a3.flex_value = gcc.segment3
AND a4.flex_value = gcc.segment4
AND a5.flex_value = gcc.segment5
AND a6.flex_value =…

View original 145 more words

Error on report Builder (Unable to Connect to the specifed Database, ORA-12541: TNS: no listener)

Unable to Connect to the specifed Database
ORA-12541: TNS: no listener

1

You may get this problem due to the TNS listener not entered or a wrong user name, password, database or Incorrect Database name

For TNS names Entry
Get the TNS entry in the tnsnames file.

Go to the location C:\DevSuiteHome_1\NETWORK\ADMIN\tnsnames.ora (check your location where DevSuiteHome is installed)

If there is not a file named tnsnames.ora create a file and that should be inside DevSuiteHome.

This DevSuiteHome that where your Oracle k2k gets installed .

You can refer to the sample file(tnsnames.ora ) in sample folder for reference.

Below is the format where

TEST → Database

Hostname → Hostname has to be given(IP address or Host address)

1523 → Port number

TEST → SID

 

TEST=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=Hostname)
(PORT=1523)
)
(CONNECT_DATA=
(SID=TEST)
)
)

 

Most Annoying things !

Most Annoying things !.

Follow

Get every new post delivered to your Inbox.

Join 365 other followers