Home » SQL & PL/SQL » SQL & PL/SQL » SQL connectivity test (oracle, 11g, AIX)
SQL connectivity test [message #654569] Sat, 06 August 2016 04:37 Go to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
Hi Leaders,

Greetings of the day. Need help in this regards.

We have a table dynamic_sql_connect having two fields username, passwd. Below is the data in the table.
INSERT INTO dynamic_sql_connect ( username, passwd ) VALUES ( 'C11111', 'c11111' );
My another SQL script "test1.sql" will read the data from dynamic_sql_connect table and connect to the DB user "C11111"
and executes "test2.sql". If the database is having this user c11111 then only it will connect and execute the "test2.sql",
otherwise we get the error invalid username and password. I want to avoid this error. For that, I need sql/plsql code or
functions if any, to perform the authentication first. If authentication is succeeded then only "test2.sql" script should
execute otherwise it should report "C11111 user does not exists" message.

Thanks.
Re: SQL connectivity test [message #654570 is a reply to message #654569] Sat, 06 August 2016 04:53 Go to previous messageGo to next message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
If I understand you correctly, this is impossible. You have a session, logged on. You then want to logon as someone else. That will disconenct your current session.

You had better explain what it is that you want to do, in business terms not technical, and then perhaps someone can suggest a solution.
Re: SQL connectivity test [message #654571 is a reply to message #654569] Sat, 06 August 2016 04:55 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
In my sql script "test1.sql", I have the code as below.

When I execute the "test1.sql" script, it will first read the username and password from dynamic_sql_connect table. then try to connect to new user to perform some other operations.

1. <read data from dynamic_sql_connect>
2. connect &username/&passwd; -- connect to the new user based on step1 data to perform some other operations.
3. perform some other operations.


If step 2 fails, I should get an exception. This is what I am expecting.

[Updated on: Sat, 06 August 2016 05:00]

Report message to a moderator

Re: SQL connectivity test [message #654576 is a reply to message #654571] Sat, 06 August 2016 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If step 2 fails you actually have an exception.

Re: SQL connectivity test [message #654577 is a reply to message #654576] Sat, 06 August 2016 05:53 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
If step2 fails, exception will come. True. we have other operations also, after the step2. Those operations should not get executed if step2 fails.
Execution of other operations may return "NOT CONNECTED" error on to the scree. This has to be avoided with the appropriate user exception message.
Re: SQL connectivity test [message #654578 is a reply to message #654577] Sat, 06 August 2016 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
we have other operations also, after the step2. Those operations should not get executed if step2 fails.
Use WHENEVER SQLERROR command.

Quote:
Execution of other operations may return "NOT CONNECTED" error on to the scree.
See above.

Quote:
This has to be avoided with the appropriate user exception message.
This has to be done in the shell script or application calling SQL*Plus or the connect command.

Re: SQL connectivity test [message #654579 is a reply to message #654578] Sat, 06 August 2016 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also why didn't you feedback and tell us the origin and solution of the problem in your previous topic?

Re: SQL connectivity test [message #654580 is a reply to message #654576] Sat, 06 August 2016 06:02 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
My code: It fails because we cannot use connect in PLSQL code. I am checking How to fix here ?

column acctcorp new_value corpA noprint
column objuser new_value objuserA noprint
column synuser new_value synuserA noprint
column synpass new_value synpassA noprint
column entuser new_value entuserA noprint
--

set term on

-- get synonym user and password from dynamic_sql_connect
select upper(username) "synuser"
      ,passwd "synpass"
from dynamic_sql_connect
/

set echo on 
set serveroutput on 
DECLARE
i number:=0;
BEGIN
-- connect as synonym user
connect &synuserA/&synpassA
exception
WHEN OTHERS 
THEN
i=1;
DBMS_OUTPUT.PUT_LINE(' I value is : ' || i );
DBMS_OUTPUT.PUT_LINE ('CONNECTIVITY FAILDED');
END;
/
Re: SQL connectivity test [message #654582 is a reply to message #654580] Sat, 06 August 2016 07:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CONNECT is only sqlplus command; not PL/SQL command (& not SQL command)
Re: SQL connectivity test [message #654583 is a reply to message #654580] Sat, 06 August 2016 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sat, 06 August 2016 12:59

Also why didn't you feedback and tell us the origin and solution of the problem in your previous topic?
Do it now!


Re: SQL connectivity test [message #654585 is a reply to message #654583] Sat, 06 August 2016 07:35 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
Done Smile Thank you,
Re: SQL connectivity test [message #654586 is a reply to message #654582] Sat, 06 August 2016 07:42 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
I already conveyed this in my post. "It fails because we cannot use connect in PL/SQL code".

Now I am checking how can we achieve using "WHENEVER SQLERROR EXIT" command. Here, it will simply EXIT from the SQL.
But, I need some appropriate message has to be printed to the user screen.
Re: SQL connectivity test [message #654588 is a reply to message #654578] Sat, 06 August 2016 07:44 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
Quote:

This has to be done in the shell script or application calling SQL*Plus or the connect command.
Michel Cadot: I need this with SQL itself. So please suggest in case of any possibilities.
Re: SQL connectivity test [message #654589 is a reply to message #654588] Sat, 06 August 2016 07:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
if you do nothing special below will be displayed

ORA-01017: invalid username/password; logon denied
Re: SQL connectivity test [message #654590 is a reply to message #654588] Sat, 06 August 2016 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prathikesh wrote on Sat, 06 August 2016 14:44
Quote:

This has to be done in the shell script or application calling SQL*Plus or the connect command.
Michel Cadot: I need this with SQL itself. So please suggest in case of any possibilities.
You can't.

Re: SQL connectivity test [message #654591 is a reply to message #654588] Sat, 06 August 2016 08:10 Go to previous messageGo to next message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
Can you describe what it is that you want to achieve? From the point of view of the user? Not your idea of the solution, but the users' idea of the problem.
Re: SQL connectivity test [message #654593 is a reply to message #654590] Sat, 06 August 2016 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also don't mix up SQL, PL/SQL and SQL*Plus.
You cannot run SQL or PL/SQL when you are not connected.
SQL*Plus has no programming language, so can't do IF THEN ELSE.
They are workaround but you have to explain IN DETAILS what you want to do.
Adding a simple script example will be better.

Re: SQL connectivity test [message #654613 is a reply to message #654593] Sun, 07 August 2016 23:00 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
Below is the requirement:

1. Connect to the database user scott. Run the script "read_data.sql".
2. The script "read_data.sql" will read the data from dynamic_tbl_connect table. This table is having 2 columns username and passwd. There is only 1 row associated with this table with (username, passwd) as ('C11111', 'c11111') accordingly.
3. We have to connect to the new schema with the above username and passwd. (Ex: connect c11111/c11111).
4. If the connection is successful then execute "mainwrapper.sql" script.
5. If the connection to new schema fails (i.e.: connect c11111/c11111 fails) then we should get the message like "No user c11111 exists in DB".

All this has to be done with the ".sql" script only. Please suggest. Thanks.

[Updated on: Sun, 07 August 2016 23:02]

Report message to a moderator

Re: SQL connectivity test [message #654615 is a reply to message #654613] Mon, 08 August 2016 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 06 August 2016 14:58
Prathikesh wrote on Sat, 06 August 2016 14:44
Quote:

This has to be done in the shell script or application calling SQL*Plus or the connect command.
Michel Cadot: I need this with SQL itself. So please suggest in case of any possibilities.
You can't.
Michel Cadot wrote on Sat, 06 August 2016 16:20

Also don't mix up SQL, PL/SQL and SQL*Plus.
You cannot run SQL or PL/SQL when you are not connected.
SQL*Plus has no programming language, so can't do IF THEN ELSE.
They are workaround but you have to explain IN DETAILS what you want to do.
Adding a simple script example will be better.
I can repeat my answer ever and ever: You can't.

Re: SQL connectivity test [message #654625 is a reply to message #654613] Mon, 08 August 2016 01:57 Go to previous messageGo to next message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
This looks like a college homework assignment. I think you need to do it with a shell script. Where did this
Quote:
All this has to be done with the ".sql" script only.
come from? Is it part of the question, or is it your own words?
Re: SQL connectivity test [message #654626 is a reply to message #654625] Mon, 08 August 2016 02:00 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
@John Watson,
I means to say "My requirement has to be addressed with the sql or PL/SQL script only".
Re: SQL connectivity test [message #654627 is a reply to message #654613] Mon, 08 August 2016 02:11 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Can you describe what it is that you want to achieve? From the point of view of the user? Not your idea of the solution, but the users' idea of the problem.
Please try to answer John's question in the way that he has requested.
What you posted here is your idea of the solution, not a statement of the problem. I'm sure that this is probably really frustrating for you, you have a problem, you have 'designed' what you believe is an appropriate solution, now you're 'just' trying to find a way to implement that solution and all you seem to be getting is "Explain the problem". BUt there's the rub, the solution that you've come up with isn't viable in its current form, so you need to go back a step to the actual problem to be solved, rather that trying to get an unworkable solution to work.

[Updated on: Mon, 08 August 2016 02:14]

Report message to a moderator

Re: SQL connectivity test [message #654628 is a reply to message #654626] Mon, 08 August 2016 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prathikesh wrote on Mon, 08 August 2016 09:00

...with the sql or PL/SQL script only".
Why?

Re: SQL connectivity test [message #654631 is a reply to message #654628] Mon, 08 August 2016 02:44 Go to previous messageGo to next message
Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad
Junior Member
Its part of my requirement. I have the main wrapper script "exec_db_script.sql" which calls the intermediate script "read_data.sql". That is the reason, I am looking the solution to be part of SQL level itself.
Re: SQL connectivity test [message #654633 is a reply to message #654631] Mon, 08 August 2016 02:50 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
And as we all keep telling you, this approach can not work.
So rather than keep telling us you have to do it in a way that can't be done, which will accomplish nothing except to annoy the rest of us, I suggest you actually post the business requirement as asked (and no business requirement ever goes into details about what language to use).
Previous Topic: UTL_DBWS error: ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
Next Topic: Check if value EXISTS in plsql table of record type
Goto Forum:
  


Current Time: Sun Jun 13 16:06:41 CDT 2021