SQL & PL/SQL :: Updating Accounts And Displaying Changes?

Aug 13, 2012

I'm trying to write procedures to make updating account owners and the like easier for a group of DBA's.

What I want to do, is create a procedure that displays the changes live.

e.g. If I changed the owner of 5 users from owner 100 to owner 200 it will display:

User test1 owner changed from 100 to 200
User test2 owner changed from 100 to 200
User test3 owner changed from 100 to 200
User test4 owner changed from 100 to 200
User test5 owner changed from 100 to 200

I can not get a loop to work to save my life. Here's what I have to update the account so far...

PROCEDURE UPDATE_OWNER (OWNER NUMBER, NEW_OWNER NUMBER) IS
BEGIN

UPDATE ACCOUNT_TRACKING
SET ACCOUNT_OWNER=NEW_OWNER WHERE ACCOUNT_OWNER = OWNER
AND ACCOUNT_TYPE !='P';
DBMS_OUTPUT.PUT_LINE ('Account Owner '||OWNER||' Changed to '||NEW_OWNER);
END UPDATE_OWNER;

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Trigger For Updating Two Accounts After Money Transfer

Feb 21, 2012

I get a "ORA-00922 missing or invalid option" after compiling the following trigger:

create or replace
trigger aifer_transfer
after insert on transfer
for each row
[code]......

I want the trigger to affect the transferring account (from_account) with minus the "amount" being transferred from it, and the account transferred to (to_account) with plus the "amount" being transferred to it. My tables looks as following:

TRANSFER
----------------------------------------- -------- ------------
ROWNR NOT NULL NUMBER(9)
PNO NOT NULL VARCHAR2(11)
FROM_ACCOUNTNO NOT NULL NUMBER(8)
TO_ACCOUNTNO NOT NULL NUMBER(8)
AMOUNT NUMBER(10,2)
DATE NOT NULL DATE

ACCOUNT Null? Type
----------------------------------------- -------- ------------
ACCOUNTNO NOT NULL NUMBER(8)
KTNR NOT NULL NUMBER(6)
REGDATE NOT NULL DATE
BALANCE NUMBER(10,2)

View 5 Replies View Related

PL/SQL :: See All Accounts With Product_code

Oct 22, 2012

I have table with Account, Transaction_date and Product_code columns.

a.)I want to see all the accounts with Product_code = 3120173, i know this is straight forward.

b.) I want to see all the accounts with Product_code = 3120346 but their most recent Product_code should be Product_ code= 3120173. So basically i need to perform a check before i pull Accounts that are on Product_code = 3120346 to see if their most recent(Transaction_date) Product_code= 3120173.

View 2 Replies View Related

Expiring All User Accounts In Profile

May 16, 2012

I am trying to expire all user accounts belonging to a particular profile my_profile.

The first option was to utilise the utlpwdmg.sql script to update the PASSWORD_LIFE_TIME for my_profile, this worked in Oracle (11g) but caused issues with the change password feature of several applications linking to the database - this option then had to be abandoned.

The next option is to therefore to select all users in my_profile and expire the accounts, what I require is a statement to combine:

- Select USERNAME from DBA_USERS where PROFILE='my_profile';
&
- Alter USER my_user PASSWORD EXPIRE;

So that all users in my_profile have their passwords expired, not just one user my_user.

View 2 Replies View Related

SQL & PL/SQL :: Automate Locking / Unlocking Of User Accounts

Nov 1, 2011

I have been plagued by people logging into my database and making changes when a clone is in process.. Having said that ,I am looking to lock accounts and unlock them when I am done.

I envision my code looking something like this:

sqlplus -s / <<END
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF;
set heading off;
spool /tmp/lockusers.sql
select 'alter user ' || username || ' account lock;' from dba_users where username not in (....) and not locked?;
spool off;
END;

sqlplus -s / <<END
@/tmp/lockusers.sql
END;

When it comes time to unlock the accounts I want to be able to unlock those accounts I previously locked and not all of them. Is there a query, I can use that can tell me when the accounts were locked or some other way about going about this so I dont unlock accounts that were locked prior to my lock script running?

View 9 Replies View Related

Security :: Lock User Accounts In Oracle Database

Jul 12, 2011

I have a doubt in locking user account in oracle database.I was told to drop some unusable users in database which my clients dont require them.I verified the dependent objects of those users but not sure if these users can be dropped.

Instead can i make those user accounts locked so that they cannot connect to database? Would there be any impact on database performance if i lock those user accounts?

Is locking an account and dropping users is similar in anyway?

View 12 Replies View Related

Express Edition (XE) :: Display A List Of All User And Admin Accounts

Jan 28, 2013

I've installed XE and I want to know what accounts are active. If I log in to SQL*Plus as sys as SYSDBA how can I display a list of all user and admin accounts? I want to know who can log in.

View 3 Replies View Related

PL/SQL :: Displaying Records In SQL

Oct 10, 2013

My data is like this. 

PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTAmountFor_qtrFor_year237898270KirtikarWESTEnterprise100000Q1  2012237898720KirtikarWESTEnterprise310000Q12013237898720KirtikarWESTEnterprise400000Q22013237898720KirtikarWESTEnterprise2500000Q32013237898720KirtikarWESTEnterprise520000Q42014 

Required Output:

PERSON_IDEMPNOFULL_NAMEREGIONSEGMENTQ1_AMTQ2_AMTQ3_AMTQ4_AMTYear237898720KirtikarWESTEnterprise31000040000025000005200002013-2014237898720Kirti
[code]......

View 11 Replies View Related

Displaying BLOBs In Cognos

Nov 29, 2012

I have a table in an Oracle db that has texts fields, int fields and a BLOB field. No issues with the table, data is correct and this works fine with crystal reports and sql developer as it does display the BLOB image properly.

We are migrating away from Crystal Reports to Cognos Report Studio 10.1. Cognos does not natively render this data type. So what can I do as a work around to make this work?

1. I thought that if I can get the DBA to put in the file location (Example: URL....) in an new field in the table; I can use HTML tag (<img src="insert URL here">) to render the image. I have tested this theory in another sql table in cognos and it works, but how do I find the location without going to the DBA's or do I really need to?

2. I thought maybe using java script or some other web code I can trick cognos to render the info...so far this has not worked.

View 1 Replies View Related

Displaying Only Max Value Of The Count In Query

Jun 18, 2007

I' m doing a query on multiple tables willing to get only top scorers from a certain round. Here's the relevant part of relation:

SOCCER_TEAM(TEAMID, NAME, CITY)
PLAYER (PLAYERID, NAME_SURNAME, DOB, TEAMID)
GAME_STATS(ROUNDID, GAMEID, TIME, PLAYERID, STATTYPE)

TIME is No between 1-90 representing the minute of the game
STATTYPE is IN('GOAL', 'OWN GOAL', 'RED', 'YELLOW')

Here's my sql code for the query:

SELECT ROUNDID, NAME_SURNAME, NAME, COUNT(STATTYPE)
FROM GAME_STATS, PLAYER, SOCCER_TEAM
WHERE PLAYER.PLAYERID IN (SELECT GAME_STATS.PLAYERID FROM GAME_STATS WHERE STATTYPE='GOAL' AND PLAYER.TEAMID = SOCCER_TEAM.TEAMID)
AND STATTYPE='GOAL'
AND GAME_STATS.PLAYERID = PLAYER.PLAYERID
GROUP BY ROUNDID, NAME_SURNAME, NAME
ORDER BY ROUNDID, COUNT(STATTYPE) DESC

This results in correctly displaying all scorers from all the rounds, yet I haven't been able to construct the HAVING clause to display ONLY the top scorers from each round (there can be multiple of them scoring equal top amount of goals and I need to show them all)

p.s. I have underlined primary keys, while foreign keys are in cursive, if it is of any relevance

View 5 Replies View Related

SQL & PL/SQL :: Displaying 24 Hours Data

Dec 27, 2010

I have a table which will the following type of data

"COL1""COL2"
1001"27-DEC-2010 02:00:00"
1002"27-DEC-2010 07:00:00"
1003"27-DEC-2010 09:00:00"
1004"27-DEC-2010 02:00:00"
1005"27-DEC-2010 12:00:00"

Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,

COL1 COL2
0 2010122701
1001 2010122702
1004 2010122702
0 2010122703
0 2010122704
0 2010122705
0 2010122706
1002 2010122707

View 2 Replies View Related

Forms :: Displaying Arabic In 10g

Sep 15, 2009

1-i have exported a database 9i with character set WE8MSWIN1252 which is her original character set;
2- i have created a new database 10G R2 with character set AL32UTF8;
3- i have imported the .dmp file to this new database with imp.exe 9i version configured with WE8MSWIN1252 character set;

-The problem is:

* when i use sqlplus, arabic data displays correctly but, in forms 10G, arabic data displays lick this ?E?? EC???IEC...... HOW CAN I CONFIGURE FORMS TO DISPLAY CORRECTLY ARABIC DATA?

View 3 Replies View Related

SQL & PL/SQL :: Displaying Column As Rows

Mar 18, 2013

writing the query, Output is shown below. facing problem in when converting columns into rows.

CREATE TABLE TESTTAB (IDNUM VARCHAR2(3),PLAN1 VARCHAR2(5),AMT1 NUMBER,PLAN2 VARCHAR2(5),AMT2 NUMBER,PLAN3 VARCHAR2(5),AMT3 NUMBER,PLAN4 VARCHAR2(5),AMT4 NUMBER,PLAN5 VARCHAR2(5),AMT5 NUMBER)
INSERT INTO TESTTAB VALUES('100','A01',9,'','','','','D04',300,'E05',900)
INSERT INTO TESTTAB VALUES('101','','','B02',56,'C03',24,'','','','')
INSERT INTO TESTTAB VALUES('102','A01',33,'','','C03',545,'','','E05',23)

[Code]...

My OUTPUT SHOULD BE LLIKE

IDNUM PLAN AMT
100 A01 9
100 D04 300
100 E05 900
101 B02 56
101 C03 24
102 A01 33
102 C03 545
102 E05 23
.
.
104 C03 44

View 5 Replies View Related

Forms :: Image Is Not Displaying

Jan 20, 2013

In my explorer/menu form there is a image item, I want to display company logo in this image item therefore I am using read_ image_ file ('c:application LOGO.jpg','JPG','IMAGE53') in WHEN_NEW_FORM_INSTANCE trigger but image isn't showing.

View 3 Replies View Related

Forms :: Displaying Icons In Web

Oct 17, 2004

I have problem in displaying icons in the push button during run time. Actually from few days i m trying to display icons at run time but with out any success.

i had made a simple push button and in its properties i had made the following changes

iconic=yes
icon filename=c:tempsave
the file save.GIF is saved under c:temp directory.

i had tried all the changes in registry.dat,orion file through the doc 203846.1 from metalink. Give me sample code step to step for showing a
push button with icon during run time. Also I tried the steps mentioned in the pdf document of

[URL].....

But it did not solve my problem. My icon file name is save.GIF , I wanted to know whether save.GIF file name convention is valid or not. Give the path from where I can get the icons used for web.

View 4 Replies View Related

SQL & PL/SQL :: Displaying A Single Row Into Column?

Mar 26, 2013

I just want to see single rows data as column.

select * from emp where rownum=1;
EMPNOENAMEJOBMGRHIREDATE SAL COMMDEPTNO
7369SMITHCLERK790217-DEC-80 00-00-00 800 20

How can it show as

column_name column_value
EMPNO 7369
ENAME SMITH
JOB CLERK and so on...

View 13 Replies View Related

Forms :: Message Is Not Displaying?

Feb 14, 2012

i got an error running my form. And when i re-run it again, the message at the bottom was nowhere to be found. How can i bring it back?

View 3 Replies View Related

Forms :: Displaying Time In 10g?

Aug 2, 2010

I want to display time in reverse count down using oracle forms 10g.

View 3 Replies View Related

PL/SQL :: Displaying Value Of Object Type

Aug 30, 2012

I have created one type as Object and I am trying to display the values available in object type for debugging purpose. To display the contents of object type.

View 4 Replies View Related

SQL & PL/SQL :: Displaying Date And Time

Aug 17, 2011

SQL> desc guide;
Name Null? Type
----------------------------------------- -------- ----------
GUIDE_ID NOT NULL NUMBER(20)
CHANNEL_ID NOT NULL NUMBER(5)
SHOW_DATETIME DATE
[code]....

when I run this query it only displays the date but no time.how to display it with the date and time?

View 2 Replies View Related

Displaying Result In Single Line

Sep 18, 2010

I have a table test_test

Name Mark
------- ------
ABC 10
DEF 10
GHI 10
JKL 20
MNO 20
PQR 30

The result should be

mark count names
10 3 ABC,DEF,GHI
20 2 JKL,MNO
30 1 PQR

View 3 Replies View Related

Displaying Specific Data From Database

Apr 5, 2008

i have columns called data_received(data received from server) , URL (sites visited)

now i want to display top 50 sites based on highest data_received.URL column will have some sites repeated.

View 1 Replies View Related

Displaying Rows Into Columns In A Table

Oct 20, 2012

I have table having below records.

empno ename deptno
101 a 10
102 b 20
103 c 10
104 d 20
105 e 30

Normal Output

deptno count(*)
-----------------
10 2
20 2
30 1

I want to display like this(rows into columns)
--------------------------------------------------------
Required Output
-------------
10 20 30
2 2 1

View 1 Replies View Related

SQL & PL/SQL :: Displaying All Other Columns If Column Is A Dupe

Nov 22, 2010

I am able to get a list of all the duplicate location_code values.

select count(*), location_code
from ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1

But how can I select all the other columns from ZZPN_PARKING_PORTFOLIO_801_V where the location_codes are duplicated.

select * from apps.ZZPN_PARKING_PORTFOLIO_801_V
where location_code in
(
select count(*), location_code
from apps.ZZPN_PARKING_PORTFOLIO_801_V
group by location_code
having count(*) > 1
)
order by location_code

View 4 Replies View Related

Forms :: Displaying 2 Canvases Simultaneously?

Oct 8, 2010

I have created a form in which I have 2 canvases - content canvas and a tabbed canvas.

I have only 4 fields on the content canvas and the remaining on the tabbed canvas. My requirement is such that the tabbed canvas should appear below the content canvas when I run the form, i.e. the fields on the content canvas will be visible, and below them will be the tabbed canvas.

For the content canvas, I have set the viewportX and viewport Y position as (0,0), and for the tabbed canvas, I have set the viewportX and viewportY position as (0,100). However when I run the form, I only see the tabbed canvas and the area at the top where I should see the contents from the content canvas appear blank.

View 2 Replies View Related

SQL & PL/SQL :: Variable Value Not Displaying Inside Loop

Jul 8, 2011

I have below peice of SQL,Im trying to form the P_attribute1 value by using the Loop index(i) but the P_attribute1 value which i assigned in declaration not displaying in the output.

DECLARE P_attribute1 VARCHAR2(100) := '110000027';
P_attribute VARCHAR2(100);
l_cnt NUMBER := 10;
v_buffer VARCHAR2(500);
v_buffer1 VARCHAR2(500);
BEGIN
FOR i IN 1 .. l_cnt
LOOP
v_buffer := v_buffer || '' || 'P_attribute' || i || '' || '!';
END LOOP;
dbms_output.put_line('v_buffer :' || v_buffer);
END;
v_buffer :P_attribute1!P_attribute2!P_attribute3!P_attribute4!P_attribute5!P_attribute6!P_attribute7!P_attribute8!P_attribute9!P_attribute10!

View 6 Replies View Related

Forms :: Displaying Image In Oracle 6i?

Apr 24, 2009

i am working with image. I have successfully stored image in oracle table. Now i want to display the stored image in oracle form 6i. There i unable to display the image although i have created the data block using the same table name as the data source.

View 9 Replies View Related

Application Express :: Pie Charts Not Displaying

Jun 25, 2013

I just upgraded from APEX 4.1.1 to APEX 4.2.2All is good, but I can't create pie charts.  Pie Charts never worked for me, but I waited until 4.2.2 to try again.  Still will not work. I imported the workspace from my previous APEX version,so something must not be set correct in my workspace.  I can create the pie chart on apex.oracle.com

Here's my info:- Application Express Version:  4.2.2- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production- OS  Red Hat Enterprise Linux Server release 5.9- WebLogic 10.3.5- Apex Listener 1.1.3.243.11.40

View 6 Replies View Related

PL/SQL :: O/P Not Displaying While Procedure Is Successfully Executed?

Apr 4, 2013

I m executing below PLSQL procedure . Its getting executed successfully but not displaying the O/P. I have set serveroutput on also.

set serveroutput on

DECLARE
   lc_file_handle        UTL_FILE.file_type;
   lc_file_dir           VARCHAR2 (100);
   lc_file_name          VARCHAR2 (50);
  
[code]...

View 14 Replies View Related

Forms :: Show_view Is Not Displaying X And Y Canvas

Sep 27, 2007

I have 3 canvases ---> X ( Content), Y ( Stack) and Z ( Stack) which has 3 blocks BX, BY, BZ resp.

WHEN-NEW-BLOCK-INSTANCE trigger of BX :
execute_query;
show_view('Y') ;
Now X and Y both canvases are displayed.
[code]....

Now only X is displayed. Canvas Y is vanished after a single blink. But I require both canvases as in initial stage.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved