deb platt

Ranch Hand
+ Follow
since Jan 23, 2008
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by deb platt

Tomcat-managed authentication with the oracle backend started working. I did not know what action on my part caused this to happen. However after it started working, I started looking at my table definitions and decided to re-do the definition of the tables. The now-working oracle columns were of type VARCHAR. I dropped the tables and re-did the columns as VARCHAR2. I went to log in to the web application, and authentication now failed. I then stopped the web app via Tomcat manager and restarted it. Authentication began working again. I don't know what kind of handshake goes on between the Tomcat server and the Oracle database server, but apparently more information is exchanged a textual representation of the table names and columns.

As to whether something like this caused my original problem, it does not seem likely because I was actually undeploying the entire application, then redeploying it. However I was fiddling with the data types used in the table definitions so perhaps my original problem was that I began by using CHAR data types in both the postgresql backend and the oracle backend, and I'm beginning to wonder if usernames matched in postgresql but not oracle because trailing white space were treated differently during the authentication process with the different databases. Now I am using TEXT data types in postgresql and VARCHAR2 data types in orcacle so there are no trailing white space issues, and the Tomcat-managed authentication works for both the oracle and postgresql backends.

Deb
11 years ago
I have created a small web application that uses Tomcat-managed authentication via JDBCRealm. When I use the following realm descriptor, and go to the web application's site, I am prompted to log in and everything works great.





Now I am trying to use my employer's oracle database with this realm descriptor:



When I try to access the web application with the oracle back end, I am prompted to log in, but denied access. Using wireshark, I have determined that the oracle database is throwing the following exception: ORA-01403 (no data found - typically results when no rows are returned). However querying the actual tables shows that they have the same information in them as is the case for the web application with the postgresql backend. Furthermore if I remove authentication from the web.xml, I can access the oracle database just fine from the web application. In fact, the web application allows me to query and update the oracle tables (user_logins and the user_roles) without difficulty.

As it turns out, our company's intranet uses tomcat-managed authentication (different user/schema, but same domain and data instance), and this works fine.

I am baffled as to why no rows are returned when Tomcat tries to authenticate. I have located the appropriate JDBC drivers in $CATALINA_HOME/lib. Any suggestions as to how to troubleshoot this would be appreciated.

Deb Platt
11 years ago
num and denom were the result of a number of mathematical operations. However the initial operands were real numbers selected from a database query. So when I said that the numbers were real numbers when they left the server, I meant that the queried numbers were real numbers in the database server.

Here's the code that set num and denom:



To set any of the variables ending with the suffix "sum" I began with a statment such as this:

Perhaps I should have initialized it to the value "0.0".

Then there is a loop where I am accumulating the sum from a query, like this:


In the above snippet, the numbers in row[3] and row[4] should be real numbers.

In most cases the ratio I am seeking works out, but in those cases where the numerator and denominator coincidentally do not have a fractional part, I get integer division, and the ratio is misleadingly reported to be zero.
[ December 17, 2008: Message edited by: deb platt ]
11 years ago
JSP
This code:


Displays this in the browser:

7325 / 25600 0.000

I want it to print out this:

7325 / 25600 0.286

I can coerce a double result by doing this:



But I figure there must be a better way.

As an aside, the numerator and denominator that are being treated as integers were actually doubles when last seen leaving the server.
11 years ago
JSP
I just tried Paul's 11/29 suggestion. While I have concealed company-specific data, here's the outcome for one such query, which I've reformatted:

LVL: 1
OBJECT_ID: a_specific_number
OBJECT_TYPE: TRIGGER
OBJ: a_specific_trigger_name

Unfortunately, this doesn't provide me with specific information regarding the trigger's body.

As an aside, in every case where I connect with the database, whether I'm using JDBC, sqlplus, or an database GUI client, I always connect with the application's username and password since the application is the owner of the corresponding schema.
11 years ago
I tried pursuing Shailesh's suggestion without luck. Further google-ing around led me to this information at oracle-base.com:

Prior to Oracle9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited...To solve these issues Oracle9i has introduced the DBMS_METADATA package which can be used to retrieve object definitions as XML or SQL DDL



The above statement leads me to believe that unfortunately Oracle8i does not have the DBMS_METADATA utility package.

Nonetheless, thanks for your input.
11 years ago
Thanks to both of you for your additional input. Via sqlplus, when I execute
I get the anticipated output. However when I execute:

zero rows are returned;

Here's my motivation for learning more about triggers. I am a Java programmer who was hired to maintain a many-years old web-based application with an Oracle8i backend. A typical work scenario might be a sales person reporting that under certain conditions, when they delete an ordered item, the total item count for the order does not get updated. Since the web app uses JDBC, I first try searching the Java source code to find the relevant embeded sql. Except many times there isn't any. So speaking more generally, I can conclude that TABLE.COLUMN is being changed by a trigger, but I don't know which trigger and/or event is causing the update. Prior to finding the Oracle8i tip of the week, I had to look at various triggers, one at a time, via my GUI client. I picked which trigger to look at by guessing from the trigger name that this or that one might be relevant. And after I found one that did update TABLE.COLUMN, I didn't know if I could stop looking or whether there might be another trigger that also impacts TABLE.COLUMN. However since I am connecting to the database with the same user name whether I am using the GUI client or sqlplus, I would think the same data should be available if I only knew how to formulate the sqlplus query. But maybe the GUI client is using the USER_TRIGGERS table, and it's just doing all the work required to convert the LONG data into text.

As an aside, I inherited a very large piece of poster paper with an incomplete entity relationship diagram sketched in pencil. So the documentation on the database isn't as complete as I would have liked.
11 years ago
I just caught Paul's post on using the DESCRIPTION column of USER_TRIGGERS. I just tried that, and unfortunately it doesn't have the information I need. for instance, here is a typical description:

It basically tells me what event causes the trigger to fire, but it doesn't say what happens when it does. I need to know whether it updates another table when it fires, and if so, what column. That information is in the TRIGGER_BODY, but not in the DESCRIPTION.

I also tried this:



I do have access to the all_objects table, but this query doesn't provide me any information as to what tables this trigger modifies.

Nonetheless, I appreciated all responses.
11 years ago
The trigger_body is a LONG, which I believe is a type of binary data. This prevents me from formulating a where clause like this:

And I have been unable to list the CREATE statements that created the triggers via sqlplus, though I'd still really love to do so. However I did find an Oracle Tip of the Week which listed two PL/SQL procedures that facilitated searhing USER_TRIGGERS.TRIGGER_BODY for a text string. I've tried both of them, and they seem to work. So this at least helps me to know if a table or column that I'm accessing via JDBC is being modifed by a trigger and that's a big help.
[ November 25, 2008: Message edited by: deb platt ]
11 years ago
Regarding the disappearing fields: my error. The data returned from my query wasn't being formatted the way that I was expecting it. To help me understand the results of my query, I labeled the query columns like this:


I am now seeing the name, event, when_clause and body in the response to my query. However I just noticed that the trigger_body (data type: LONG) is being truncated. For instance if I use my GUI client to inspect a specific body, I get this:


However if I use sqlplus to query just for this same body, I get this:


After looking at a number of these trigger_bodies, it would appear that only a set number of characters are displayed. How do I get sqlplus to display all the data in this LONG column?
[ November 25, 2008: Message edited by: deb platt ]
11 years ago
Thanks for your input. Here's what's happening now. I tried executing this sql query while using sqlplus:


No luck. I was told the table/view didn't exist. So I tried
Again I was informed that the table/view didn't exist. Perhaps this is a table that came into being for some version of Oracle following Oracle8.

I actually have a hardbound copy of Oracle8: The Complete Reference, and I have read Chapter 23 on Triggers, but this chapter doesn't tell you how to query for existing triggers. Chapter 32 is The Hitchhiker's Guide to the Oracle8 Data Dictionary. It tells me there is a table called USER_TRIGGERS. Using sqlplus, I can successfully do this query:


I can also successfully do this query:


However when I try to see the body associated with a trigger name by querying:

the trigger_name mysteriously disappears. sqlplus prints the heading, "TRIGGER_NAME" followed by no data, then the the heading "TRIGGER_BODY" followed by data for the trigger body, then the next "TRIGGER_NAME" without data, then the next "TRIGGER_BODY" followed by data, etc.

If I do this query:

All data is absent except for the triggering_event. If I do this query:

All data is absent except for the trigger_body. I don't understand why sqlplus is not displaying data for all the columns for which I am querying.
[ November 25, 2008: Message edited by: deb platt ]
11 years ago
I'm working on a legacy web application which uses an Oracle8i database. I usually interact with the database using JDBC or by using a GUI client. At this point, I'm a n00b when it comes to sqlplus.

The database has 80+ triggers which I can view one at a time selecting a specific trigger with the GUI client, then indicating that I want the GUI to display the trigger's properties. However if I need to be able to search all the triggers to see if any impact a specific column of a specific table, how do I do this with (presumably with sqlplus?)

I'd also like to be able to dump as a text file all the create statements that defined the the triggers to begin with.

Any suggestions would be appreciated.

Deb
11 years ago
This is a problem that I couldn't find addressed on the web, but which I eventually figured out. Here's the scenario. I was trying to do this:




But I would get this error message:

Property 'COST' not found on type java.lang.String



Of course I could find the sum in my jsp as follows:



But I'd rather have the DBMS do the summing for me. So I tried this:



This actually worked. However since I knew I was only going to get a single value back from the query, I also tried replacing the loop with this:



That worked like a charm. ${clientShipping} now held the sum returned by the sql query.
[ September 24, 2008: Message edited by: deb platt ]
12 years ago
JSP
I'd like to thank Tim for mentioning that he's using enscript. I just installed it, and it seems to work great.

A co-worker and I are both using eclipse 3.4.0. When he prints out code, it looks fine - just like I'd like to get. When I print out code, it's in a humogous font. The apparent difference is that he's using eclipse on a Windows machine, and I'm using Ubuntu 7.10. After further discussion yesterday, he googled around and said eclipse is supposed to be using my default system fonts. I double checked my font settings under Appearance Preferences > Fonts, and everything was set a 10pt. I'm guessing that's for what's displayed on my screen. Not sure if there's some other setting for what's printed.

Anyway enscript is meeting my current needs. Thanks!

Deb
If I understand things correctly, my system administrator has copied the orcale dbms and the oracle connection manager to different zones in our network. However, when is executed in zone A, it seems to be stopping the connection manager in both zones A and B. I don't understand what's going on. Any help would be appreciated.

Regards,
Deb
12 years ago