OraFAQ Forum: SQL & PL/SQL » Oracle SQL Customization

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SQL Customization (Oracle 12C )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle SQL Customization [message #639266]

Sat, 04 July 2015 01:56

deepakdot
Messages: 89
Registered: July 2015

Member

we write our SQLs in SQLJ files under Java. we dont write any database Procedure/functions/packages.

In DB2, we use to do SQL Customization and it creates packages in the database.after Customization is done. we can see these SQLs by query the system tables.

Now we are moving to Oracle. In Oracle also we are doing SQL Customization. Where i can see all the SQLs in Database. which system table i should query to view all the SQLs.

Report message to a moderator

Re: Oracle SQL Customization [message #639267 is a reply to message #639266]

Sat, 04 July 2015 02:01

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

SQL is not stored in database, I think you mean PL/SQL objects.

[Updated on: Sat, 04 July 2015 03:30]

Report message to a moderator

Re: Oracle SQL Customization [message #639269 is a reply to message #639266]

Sat, 04 July 2015 02:14

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Oracle doesn't work that way. It compiles (and optimizes) your SQL dynamically when you invoke it. This is, in some ways, a better architecture than DB2.

Report message to a moderator

Re: Oracle SQL Customization [message #639270 is a reply to message #639266]

Sat, 04 July 2015 02:18

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With "SQL Customization" do you mean "SQL optimization"?
IF so Oracle can store the query and its execution plan.

Report message to a moderator

Re: Oracle SQL Customization [message #639285 is a reply to message #639270]

Sat, 04 July 2015 05:06

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm not familiar with DB2. However, as you mentioned "packages", Oracle uses them as well. So, if you wrote packages, stored procedures and/or functions, triggers or whatever you might have written, try to query USER_SOURCE. SQL code is stored in its TEXT column.

Report message to a moderator

Re: Oracle SQL Customization [message #639333 is a reply to message #639285]

Mon, 06 July 2015 06:32

deepakdot
Messages: 89
Registered: July 2015

Member

Let me explain more clearly. We dont write any Database Procedure/packages. We write our SQLs in .sqlj files which are called from Java. Java Program Invokes/call this SQLs.

In DB2: As the SQLs are present in the SQLJ Java file, during deploy our source code, We used to custmize this SQLs against the Database. This process create Packages in the Database. We called it as static SQLs as the database alrady knows about the SQLs container and knows the path before. Here i can go to the system tables and see all the SQLs associated. Here the Package name might be equal to the Java file name. If we dont do custmization, it behave like Dynamic SQLs and Performance is Low.

Oracle: Oracle also supports static SQLs. I just wonder where to find these information from Database once we run/compile our SQL code against the database.

Please suggest.

Report message to a moderator

Re: Oracle SQL Customization [message #639334 is a reply to message #639333]

Mon, 06 July 2015 06:41

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

So, you have the Packages in the .sqlj files which when called by Java program creates the Packages in the database. Is this what you mean?

Report message to a moderator

Re: Oracle SQL Customization [message #639335 is a reply to message #639333]

Mon, 06 July 2015 06:46

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

THis is your mistake,
Quote:

If we dont do custmization, it behave like Dynamic SQLs and Performance is Low.

Oracle doesn't have this problem. It is a DB2 issue. You should forget about it (assuming, of course, that your Oracle DBA knows how to do his job).

Report message to a moderator

Re: Oracle SQL Customization [message #639336 is a reply to message #639334]

Mon, 06 July 2015 06:47

deepakdot
Messages: 89
Registered: July 2015

Member

Before the actual Java call is Made. While we deploy our code in the WAS , same time we do the custmization. I think 'Binding' will be the appropriate word for this forum. We bind all the SQLs against the database. So it create/Stores all the SQL informations and its access path before. This helps to fasten the executions.

Report message to a moderator

Re: Oracle SQL Customization [message #639338 is a reply to message #639336]

Mon, 06 July 2015 06:56

deepakdot
Messages: 89
Registered: July 2015

Member

One More reason why we do 'Binding' is to ensure the SQL written is proper. There are some syntax errors which is not caught at time of compilation but it might throw an error during runtime.When we bind, this ensure that the SQL writen is proper with the Database.

I am new to Oracle. In DB2 we used to do this. So we are just trying to Map with Oracle how to proceed on Binding .

Report message to a moderator

Re: Oracle SQL Customization [message #639339 is a reply to message #639338]

Mon, 06 July 2015 06:59

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Deepak, you pobably need to attend a few courses on Oracle DB admin. Or hire a consultant who can advise you. If you proceed with your current level of knowledge, your whole project may be less than successful.

My boss would be happy to givde you a quote Smile