Home » SQL & PL/SQL » SQL & PL/SQL » How to allow multi-users in Procedure (Oracle 12c)
How to allow multi-users in Procedure [message #672807] Fri, 26 October 2018 14:16 Go to next message
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 #672808 is a reply to message #672807] Fri, 26 October 2018 14:21 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>Thank you all for the answer!
What is the question?
Re: How to allow multi-users in Procedure [message #672809 is a reply to message #672808] Fri, 26 October 2018 14:28 Go to previous messageGo to next message
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, how to make it?

[Updated on: Fri, 26 October 2018 14:30]

Report message to a moderator

Re: How to allow multi-users in Procedure [message #672810 is a reply to message #672809] Fri, 26 October 2018 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Always post your Oracle version (with 4 decimals) as answer depends on it
2/ Do not commit inside a procedure: this is the caller that knows if it wants to commit or not, not the procedure (above all when you sometimes commit and sometimes not)
3/ "SET ROLE ALL EXPECT;" is not a valid PL/SQL statement
4/ A procedure can be called by several sessions at the same time, there is nothing to do (of course not 2 sessions can update the same row at the same time)

Re: How to allow multi-users in Procedure [message #672811 is a reply to message #672810] Fri, 26 October 2018 14:50 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
GRANT EXECUTE ON account_api TO USER1;
GRANT EXECUTE ON account_api TO USER2;
GRANT EXECUTE ON account_api TO USER3;
Re: How to allow multi-users in Procedure [message #672812 is a reply to message #672807] Fri, 26 October 2018 14:53 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Your code sometimes refers to a table ACCOUNT and sometimes to a table ACCOUNTS. So I don't think it compiled. What are you really asking?
Re: How to allow multi-users in Procedure [message #672828 is a reply to message #672812] Sun, 28 October 2018 14:45 Go to previous messageGo to next message
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 #672829 is a reply to message #672828] Sun, 28 October 2018 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member

http://www.orafaq.com/wiki/WHEN_OTHERS
Re: How to allow multi-users in Procedure [message #672831 is a reply to message #672829] Sun, 28 October 2018 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
As learning exercise, please post results from SQL below

SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;
Re: How to allow multi-users in Procedure [message #672832 is a reply to message #672831] Sun, 28 October 2018 14:53 Go to previous messageGo to next message
petar97
Messages: 120
Registered: October 2018
Senior Member

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
SYS_C0011761
INDEX

ACC_SEQ
SEQUENCE

ACCOUNT_API
PACKAGE


OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
ACCOUNTS
TABLE

ACCOUNT_API
PACKAGE BODY

ACCOUNTS_HISTORY
TABLE
Re: How to allow multi-users in Procedure [message #672834 is a reply to message #672828] Sun, 28 October 2018 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
2/ Do not commit inside a procedure: this is the caller that knows if it wants to commit or not, not the procedure (above all when you sometimes commit and sometimes not)
Re: How to allow multi-users in Procedure [message #672835 is a reply to message #672832] Sun, 28 October 2018 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
petar97 wrote on Sun, 28 October 2018 12:53

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
SYS_C0011761
INDEX

ACC_SEQ
SEQUENCE

ACCOUNT_API
PACKAGE


OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
ACCOUNTS
TABLE

ACCOUNT_API
PACKAGE BODY

ACCOUNTS_HISTORY
TABLE
Why are none of your FUNCTIONs listed?

IMO, if you are brand new to Oracle & SQL.
You should not be messing with PL/SQL until after you have basic SQL knowledge; since it is whole DIFFERENT language!
Re: How to allow multi-users in Procedure [message #672871 is a reply to message #672835] Tue, 30 October 2018 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Sun, 28 October 2018 19:56

Why are none of your FUNCTIONs listed?

Why wold they be listed in user_objects?
They're in the package.
Re: How to allow multi-users in Procedure [message #672909 is a reply to message #672871] Wed, 31 October 2018 16:19 Go to previous messageGo to next message
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 #672912 is a reply to message #672909] Wed, 31 October 2018 17:39 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
petar97 wrote on Wed, 31 October 2018 14:19
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.

Why do you conclude that the function did not return the data?
Nothing was done with RES1 variable after it was populated by the function.


DBMS_OUTPUT.PUT_LINE(TO_CHAR(RES1,'YYYY-MM-DD HH24:MI:SS'));
Re: How to allow multi-users in Procedure [message #672913 is a reply to message #672909] Wed, 31 October 2018 17:40 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
petar97 wrote on Wed, 31 October 2018 16:19
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.
HOw do you know it didn't return the date? In the above, you called the function as an assignment to variable 'res1', but then you didn't do anything with it ... like

dbms_output.put_line('function returned: '||to_char(res1)


Re: How to allow multi-users in Procedure [message #672915 is a reply to message #672913] Wed, 31 October 2018 17:51 Go to previous messageGo to next message
petar97
Messages: 120
Registered: October 2018
Senior Member

I'm a beginner, how to do it: dbms_output.put_line ('function returned:' || to_char (res1)
Re: How to allow multi-users in Procedure [message #672920 is a reply to message #672915] Thu, 01 November 2018 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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 #672926 is a reply to message #672920] Thu, 01 November 2018 04:09 Go to previous messageGo to next message
petar97
Messages: 120
Registered: October 2018
Senior Member
I 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
Re: How to allow multi-users in Procedure [message #672927 is a reply to message #672926] Thu, 01 November 2018 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have here an implicit conversion you must be aware and take care of.

[Updated on: Thu, 01 November 2018 04:28]

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 Go to previous messageGo to next message
EdStevens
Messages: 1248
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 Go to previous message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
petar97 wrote on Thu, 01 November 2018 04:09
I 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/
Previous Topic: date column displays strange output
Next Topic: How to print Boolean or True/False value in Sql
Goto Forum:
  


Current Time: Mon Sep 21 01:44:14 CDT 2020