Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Java stored procedure in oracle

669410Nov 30 2008 — edited Dec 1 2008
Hi,
This is regarding the java stored procedure.
I have written a java stored procedure for mailing from DB.But it required some grants or extra privileges to be run from the data base.
CAn anyone please specify what are the grants needed for this.

I have to give to following permissions to the java methods....

dbms_java.grant_permission(user,'java.util.PropertyPermission','*','read');
dbms_java.grant_permission(user,'java.net.SocketPermission','*','connect, resolve');
dbms_java.grant_permission(user,'java.io.FilePermission',FilePathName,'read');


Can anyone please specify what are the privileges required to run the above.Because I have tired it with normal priviledges..it's not working.....

I am getting the following error while executing this from the normal user..rather the user that have been created for me....

ORA-29532: Java call terminated by uncaught Java exception: java.lang.SecurityException: policy table update SYS:java.util.PropertyPermission, *
ORA-06512: at "SYS.DBMS_JAVA", line 313
ORA-06512: at line 1



Thanks,
Anirban Datta

Edited by: user5815929 on Nov 30, 2008 8:42 PM
This post has been answered by zhxiangxie on Dec 1 2008
Jump to Answer

Comments

mathguy

To get the sum of numbers in a ROW, you just use the + operator with the column names as operands; don't use SUM(...).
Then: weird column names must be enclosed in DOUBLE-QUOTES, like so:

"2021-01-08"

(those are not two consecutive single-quotes, but a single double-quote character on each side of the column name).
Beware of null, too; unlike the SUM(...) aggregate (which works only on columns -adding values from all rows - the opposite of what you need here), the simple addition operator does not ignore null. Instead, it returns null as the answer. If you want null to be treated as "zero", then you will need to wrap each column name (double-quoted as shown) within NVL(...., 0) before applying the addition operator +
In the end. the formula for the extra column might look something like

nvl("2021-01-01", 0) + nvl("2021-01-02", 0) + ...   as total

or without the NVL if you don't expect null, or if you don't want null to be treated as zero

"2021-01-01" + "2021-01-02" + ....   as total

EDIT:

Looking at it again, you are simply letting PIVOT create the column names. So they aren't simply 2020-01-01 for example; they are already enclosed in single-quotes. If that works for you, fine; then the column names are referenced like so:

"'2020-01-01'"

Happily the code formatting makes it easy to read this. There are double-quote characters at the extremities; then single-quote characters which are part of the column names (as generated by PIVOT).

The alternative is to generate column names without single-quotes. You can do that in PIVOT:

pivot ... for dates in ('2021-01-01' as "2021-01-01", .....)

then PIVOT will use those column names instead of the automatically generated ones. Note that here, too, you must use double-quotes around the weird column names.

1 person found this helpful
User_OMEF8

undefined (0 Bytes)Thanks Mathguy.
I tried the double quotes, but it still produces the same error message (missing right parenthesis). I am not too worried about the null because my count will interpret it to 0 if there are none for that day.

pivot (  
             count(*)
             for(DATES) in ( '2021-01-01', '2021-01-02', '2021-01-03' )
             "2021-01-01" + "2021-01-02" + "2021-01-03"
            )

I think it has to do with my pivot and the "dynamic? (not sure if this is the right word for it)" date columns ('2021-01-01', '2021-01-02', '2021-01-03').

mathguy

Did you read my EDIT (to my first answer)?

1 person found this helpful
Frank Kulash
Answer

Hi,
As Mathguy said, the column names are not 10 characters long, starting with 2 (e.g., "2021-01-01"); they are 12 characters long, starting with single-quote (e.g., "'2021-01-01'"). Another problem is that those column names cannot be used inside the PIVOT clause; you can use them in the SELECT clause.
I suggest using names that don't require double_quotes, such as d_2021_01_01. If you must use non-standard names, then do something like this:

WITH  data_to_pivot  AS
(
      select upper (a.doc_type)                 as DOC_TYPE
      ,      to_char(a.date_time, 'yyyy-mm-dd') as DATES
      from   tableA a
      where  exists ( 
                      select 1 from tableB b 
                      where b.filename = 'ABCXYZ' and to_char(a.id) = b.id
                    ) 
)
SELECT    p.*
,         "'2021-01-01'" + "'2021-01-02'" + "'2021-01-03'" AS total
FROM     data_to_pivot
PIVOT    (   COUNT (*)
         FOR (dates) IN ( '2021-01-01', '2021-01-02', '2021-01-03' )
         ) p
ORDER BY doc_type;

If you'd care to post CREATE TABLE and INSERT statements for some sample data, then I could test this.

Marked as Answer by User_OMEF8 · Jan 8 2021
User_OMEF8

I didn't see it the first time, so I may have already been in the thread before the edit. Thanks again for the help though. I did use the double quotes and got rid of the single quote in the column name like you mentioned. I still have the wrong syntax for the "sum" column.

User_OMEF8

Thanks Frank! Your query did the trick. It looks like I was putting the SUM part in the wrong spot in the query to begin with.

mathguy

Lol, I was looking so closely at the trees, I didn't see the problem with the forest.
I saw the SUM(...) thing, and the way you were trying to reference the column names, and I didn't see where you were doing all these things. As Mr. Kulash pointed out, you can only do that in the SELECT clause, not in PIVOT.

1 person found this helpful
User_OMEF8

Lol, no worries! I was wondering why you were focused on that part, but I figured there must be some type of lesson you were trying to teach me. Thanks again for the help.

Frank Kulash

HI, @user-omef8
This illustrates one of the many reasons why you need to include a little sample data whenever you have a question, so the people who want to help you can test their ideas. You may have several separate problems with a piece of code; you want them all fixed, not just the most obvious one.

1 person found this helpful
User_OMEF8

That is a very valid point. I will try to provide those the next time I post a question. Thanks again for your help. I greatly appreciate it.

1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 29 2008
Added on Nov 30 2008
#java, #pl-sql, #procedure
4 comments
6,374 views