OraFAQ Forum: SQL & PL/SQL » SQL connectivity test

Home » SQL & PL/SQL » SQL & PL/SQL » SQL connectivity test (oracle, 11g, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL connectivity test [message #654569]

Sat, 06 August 2016 04:37

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.

Report message to a moderator

Re: SQL connectivity test [message #654570 is a reply to message #654569]

Sat, 06 August 2016 04:53

John Watson
Messages: 8930
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.

Report message to a moderator

Re: SQL connectivity test [message #654571 is a reply to message #654569]

Sat, 06 August 2016 04:55

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

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If step 2 fails you actually have an exception.

Report message to a moderator

Re: SQL connectivity test [message #654577 is a reply to message #654576]

Sat, 06 August 2016 05:53

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.

Report message to a moderator

Re: SQL connectivity test [message #654578 is a reply to message #654577]

Sat, 06 August 2016 05:57

Michel Cadot
Messages: 68643
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.

Report message to a moderator

Re: SQL connectivity test [message #654579 is a reply to message #654578]

Sat, 06 August 2016 05:59

Michel Cadot
Messages: 68643
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?

Report message to a moderator

Re: SQL connectivity test [message #654580 is a reply to message #654576]

Sat, 06 August 2016 06:02

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;
/

Report message to a moderator

Re: SQL connectivity test [message #654582 is a reply to message #654580]

Sat, 06 August 2016 07:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

CONNECT is only sqlplus command; not PL/SQL command (& not SQL command)

Report message to a moderator

Re: SQL connectivity test [message #654583 is a reply to message #654580]

Sat, 06 August 2016 07:29

Michel Cadot
Messages: 68643
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!

Report message to a moderator

Re: SQL connectivity test [message #654585 is a reply to message #654583]

Sat, 06 August 2016 07:35

Prathikesh
Messages: 20
Registered: February 2015
Location: Hyderabad

Junior Member

Done

Smile

Thank you,

Report message to a moderator

Re: SQL connectivity test [message #654586 is a reply to message #654582]

Sat, 06 August 2016 07:42

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.

Report message to a moderator

Re: SQL connectivity test [message #654588 is a reply to message #654578]

Sat, 06 August 2016 07:44

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.

Report message to a moderator

Re: SQL connectivity test [message #654589 is a reply to message #654588]

Sat, 06 August 2016 07:48

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

Report message to a moderator

Re: SQL connectivity test [message #654590 is a reply to message #654588]

Sat, 06 August 2016 07:58

Michel Cadot
Messages: 68643
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.

Report message to a moderator

Re: SQL connectivity test [message #654591 is a reply to message #654588]

Sat, 06 August 2016 08:10

John Watson
Messages: 8930
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.

Report message to a moderator

Re: SQL connectivity test [message #654593 is a reply to message #654590]

Sat, 06 August 2016 09:20

Michel Cadot
Messages: 68643
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.

Report message to a moderator

Re: SQL connectivity test [message #654613 is a reply to message #654593]

Sun, 07 August 2016 23:00

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

Michel Cadot
Messages: 68643
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.

Report message to a moderator

Re: SQL connectivity test [message #654625 is a reply to message #654613]

Mon, 08 August 2016 01:57

John Watson
Messages: 8930
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?

Report message to a moderator

Re: SQL connectivity test [message #654626 is a reply to message #654625]

Mon, 08 August 2016 02:00

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".

Report message to a moderator

Re: SQL connectivity test [message #654627 is a reply to message #654613]

Mon, 08 August 2016 02:11

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

Michel Cadot
Messages: 68643
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?

Report message to a moderator

Re: SQL connectivity test [message #654631 is a reply to message #654628]

Mon, 08 August 2016 02:44

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.

Report message to a moderator

Re: SQL connectivity test [message #654633 is a reply to message #654631]

Mon, 08 August 2016 02:50

cookiemonster
Messages: 13920
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).

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 14:57:02 CDT 2024