How to allow multi-users in Procedure [message #672807] |
Fri, 26 October 2018 14:16 |
|
petar97
Messages: 120 Registered: October 2018
|
Senior Member |
|
|
I create Package, in this package I have update procedure u which should be allow multi-users update in table.
Thank you all for the answer!
I'll post the full package
My procedure look like this:
CREATE OR REPLACE PACKAGE account_api AS
PROCEDURE add_new_account
(
p_acc_id accounts.acc_id%type
, p_acc_name accounts.acc_name%type
, p_acc_amount accounts.acc_amount%type
, p_acc_date accounts.acc_date%type
);
PROCEDURE upd_account
(
p_acc_name accounts.acc_name%type
, p_acc_amount accounts.acc_amount%type
, p_acc_date accounts.acc_date%type
);
PROCEDURE del_accounts
(
p_acc_id accounts.acc_id%type
);
FUNCTION get_amount
(p_acc_id accounts.acc_id%type)
RETURN number;
FUNCTION get_date
(p_acc_id accounts.acc_id%type)
RETURN date;
end account_api;
/
CREATE OR REPLACE PACKAGE BODY account_api AS
PROCEDURE add_new_account
(
p_acc_id accounts.acc_id%type
, p_acc_name accounts.acc_name%type
, p_acc_amount accounts.acc_amount%type
, p_acc_date accounts.acc_date%type
)
IS
BEGIN
INSERT INTO account (acc_id, acc_name, acc_amount, acc_date)
VALUES (acc_seq.nextval, p_acc_id, p_acc_name, p_acc_amount, p_acc_date)
END;
PROCEDURE upd_account
(
p_acc_name accounts.acc_name%type
, p_acc_amount accounts.acc_name%type
, p_acc_date accounts.acc_name%type
)
IS
BEGIN
UPDATE accounts
SET acc_naziv = p_acc_naziv
, acc_amount = p_acc_amount
, acc_date = p_acc_date
WHERE acc_id = p_acc_id
SET ROLE ALL EXPECT;
COMMIT;
END;
PROCEDURE del_accounts
(
p_acc_id accounts.acc_id%type
)
DELETE FROM accounts WHERE acc_id = p_acc_id;
COMMIT;
END;
FUNCTION get_amount
(p_acc_id accounts.acc_name%type)
return Number is res number;
begin
select acc_amount into res
from account where acc_id =p_acc_id;
return res;
end;
FUNCTION get_date
(p_acc_id accounts.acc_id%type)
RETURN NUMBER IS res1 NUMBER;
BEGIN
SELECT acc_date INTO res1
FROM accounts WHERE acc_id = p_acc_id;
RETURN res1;
end;
end account_api;
[Edit MC: add code tags]
[Updated on: Fri, 26 October 2018 14:30] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
Re: How to allow multi-users in Procedure [message #672828 is a reply to message #672812] |
Sun, 28 October 2018 14:45 |
|
petar97
Messages: 120 Registered: October 2018
|
Senior Member |
|
|
I solved this problem.
SQL> CREATE OR REPLACE PACKAGE account_api AS
2 PROCEDURE add_new_account
3 ( p_acc_id accounts.acc_id%type
4 , p_acc_name accounts.acc_name%type
5 , p_acc_amount accounts.acc_amount%type
6 , p_acc_date accounts.acc_date%type);
7
8 PROCEDURE upd_account
9 (
10 p_acc_id accounts.acc_id%type
11 , p_acc_name accounts.acc_name%type
12 , p_acc_amount accounts.acc_amount%type
13 , p_acc_date accounts.acc_date%type
14 );
15
16 PROCEDURE del_accounts
17 (p_acc_id accounts.acc_id%type);
18
19 FUNCTION get_amount
20 (p_acc_id accounts.acc_id%type)
21 return Number;
22
23 FUNCTION get_date
24 (p_acc_id accounts.acc_id%type)
25 RETURN date;
26 end account_api;
27 /
CREATE OR REPLACE PACKAGE BODY account_api AS
2 PROCEDURE add_new_account
3 ( p_acc_id accounts.acc_id%type
4 , p_acc_name accounts.acc_name%type
5 , p_acc_amount accounts.acc_amount%type
6 , p_acc_date accounts.acc_date%type)
7 IS
8 BEGIN
9 INSERT INTO accounts(acc_id, acc_name, acc_amount, acc_date)
10 VALUES (p_acc_id, p_acc_name, p_acc_amount, p_acc_date);
11 COMMIT;
12 EXCEPTION
13 WHEN OTHERS THEN
14 ROLLBACK;
15 RAISE;
16 END;
17 PROCEDURE upd_account
18 (p_acc_id accounts.acc_id%type
19 , p_acc_name accounts.acc_name%type
20 , p_acc_amount accounts.acc_amount%type
21 , p_acc_date accounts.acc_date%type
22 )
23 IS
24 BEGIN
25 UPDATE accounts
26 set acc_name = p_acc_name
27 , acc_amount = p_acc_amount
28 , acc_date = p_acc_date
29 WHERE acc_id = p_acc_id;
30 COMMIT;
31 END;
32 PROCEDURE del_accounts
33 (p_acc_id accounts.acc_id%type)
34 IS
35 BEGIN
36 DELETE FROM accounts WHERE acc_id = p_acc_id;
37 COMMIT;
38 EXCEPTION
39 WHEN OTHERS THEN
40 ROLLBACK;
41 RAISE;
42 END;
43 FUNCTION get_amount
44 (p_acc_id accounts.acc_id%type)
45 return Number is res number;
46 begin
47 select acc_amount into res
48 from accounts where acc_id =p_acc_id;
49 return res;
50 end;
51 FUNCTION get_date
52 (p_acc_id accounts.acc_id%type)
53 RETURN date IS res1 date;
54 BEGIN
55 SELECT acc_date INTO res1
56 FROM accounts WHERE acc_id = p_acc_id;
57 RETURN res1;
58 end;
59 end account_api;
60 /
[Updated on: Sun, 28 October 2018 14:48] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: How to allow multi-users in Procedure [message #672909 is a reply to message #672871] |
Wed, 31 October 2018 16:19 |
|
petar97
Messages: 120 Registered: October 2018
|
Senior Member |
|
|
The function is executed but does not return the data.
You can tell me where I was mistaken in the package
or there is an error in calling the function
SQL> set serveroutput on
SQL> declare
2 res1 date;
3 begin
4 res1 := account_api.get_date(1);
5 end;
6 /
PL/SQL procedure successfully completed.
[Updated on: Wed, 31 October 2018 16:22] Report message to a moderator
|
|
|
|
|
|
Re: How to allow multi-users in Procedure [message #672920 is a reply to message #672915] |
Thu, 01 November 2018 01:52 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
As you use SQL*Plus you can use:
var res varchar2(30)
begin
:res := account_api.get_date(1);
end;
/
print res
Unfortunately SQL*Plus does not know DATE datatype, so you have an implicit conversion between the value returned by the function and the variable.
You can get the date in the format you want using TO_CHAR specifying the format.
[Updated on: Thu, 01 November 2018 01:52] Report message to a moderator
|
|
|
|
|
Re: How to allow multi-users in Procedure [message #672934 is a reply to message #672915] |
Thu, 01 November 2018 06:47 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
petar97 wrote on Wed, 31 October 2018 17:51
I'm a beginner, how to do it: dbms_output.put_line ('function returned:' || to_char (res1)
Time for you to start getting acquainted with the documentation. It's not enough to "know" that "I can look up anything on the internet". To even think about looking it up, you have to know that it is there to be looked up. To that end you need to be familiar what what is available to be looked up.
=================================================
Learning how to look things up in the documentation is time well spent investing in your career. To that end, you should drop everything else you are doing and do the following:
Go to https://docs.oracle.com/en/database/database.html
Or more directly, for 11g, go to https://docs.oracle.com/cd/E11882_01/nav/portal_booklist.htm
Or more directly, for 12c, go to https://docs.oracle.com/database/121/nav/portal_booklist.htm
You are now at the entire documentation set for your selected Oracle product and version.
BOOKMARK THAT LOCATION
Spend a few minutes just getting familiar with what is available here. Take special note of the "books" and "search" tabs. Under the "books" tab (for 10.x) or the "Master Book List" link (for 11.x) you will find the complete documentation library.
Spend a few minutes just getting familiar with what kind of documentation is available there by simply browsing the titles.
Open the Reference Manual and spend a few minutes looking through the table of contents to get familiar with what kind of information is available there.
Do the same with the SQL Reference Manual.
Do the same with the Utilities manual.
You don't have to read the above in depth. They are reference manuals. Just get familiar with what is there to be referenced. Ninety percent of the questions asked on this forum can be answered in less than 5 minutes by simply searching one of the above manuals.
Then set yourself a plan to dig deeper.
- *Read a chapter a day from the Concepts Manual*.
- Take a look in your alert log. One of the first things listed at startup is the initialization parms with non-default values. Read up on each one of them (listed in your alert log) in the Reference Manual.
- Take a look at your listener.ora, tnsnames.ora, and sqlnet.ora files. Go to the Network Administrators manual and read up on everything you see in those files.
- *When you have finished reading the Concepts Manual, do it again*.
Give a man a fish and he eats for a day. Teach a man to fish and he feeds himself for a lifetime.
=================================
|
|
|
Re: How to allow multi-users in Procedure [message #672935 is a reply to message #672926] |
Thu, 01 November 2018 06:52 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
petar97 wrote on Thu, 01 November 2018 04:09I solved the problem, like this:
SQL> set serveroutput on
SQL> declare
2 res1 date;
3 begin
4 res1 := account_api.get_date(1);
5 dbms_output.put_line(res1);
6 end;
7 /
31-OCT-18
Ah, I see you found it. So, did it reveal that you are getting results from the function after all?
You do have a fundamental problem with your use of dbms_output, using a DATE data type for input. The expected input of dbms_output.put_line is a string, not a DATE. Your useage forces an internal use of to_char() to convert the DATE to a STRING representation of a DATE. In this trivial example it probably doesn't matter, but you need to start getting familiar with these very fundamental concepts. To better understand the difference between a DATE and a STRING representation of a DATE, please take a look at https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/
|
|
|