2

I'm trying to use the CONNECT Command from Oracle inside one of my PL/SQL-Scripts. What I want to achieve is to be able to switch to a different context inside the script itself (e.g. login as user and then switch at some point to sys dba).

My current approach is the following:

sqlplus user/password@//database:1521/orcl @ complete.sql

Content of complete.sql

DECLARE
     -- declare
     v_scriptInserted varchar2(1);
     v_booleanFalse varchar2(1) := '0';
BEGIN
    SELECT CASE WHEN COUNT(*) >= 1 THEN 1 ELSE 0 END INTO v_scriptInserted FROM SCHEMA_HISTORY WHERE SCRIPT = '&v_SCRIPTNAME';
       IF v_scriptInserted = booleanFalse THEN
       -- do Stuff with user privilidges and after this is done switch to sysdba
       CONNECT sys/oracle@database:1521/orcl AS SYSDBA
       -- do magic here with sysdba
       -- SWITCH CONTEXT BACK TO User
       DISCONNECT;
    END IF;
END;
/

However, no matter how I try to escape the CONNECT sequence the part :1521 will be misinterpreted as bind varibale (SP2-0552).

I already digged around stack overflow and found the related articles: - How to escape ":" in Oracle dynamic SQL and also have bind variables? - Oracle PL/SQL - How to escape colon (:), being misinterpreted for bind variable However no matter what I try nothing seems to work.

My current approaches had been:

  • CONNECT sys/oracle@database:1521/orcl AS SYSDBA
  • CONNECT sys/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1521))(CONNECT_DATA=(SID=orcl)) AS SYSDBA
  • CONNECT sys/oracle@database\:1521/orcl AS SYSDBA
  • SELECT 'sys/oracle@database' || ':' || '1521/orcl AS SYSDBA' INTO v_dest_connstring FROM DUAL; CONNECT 'v_dest_connstring'
  • CONNECT 'sys/oracle@database:1521/orcl AS SYSDBA'
  • CONNECT ''sys/oracle@database:1521/orcl AS SYSDBA''
  • CONNECT '''sys/oracle@database:1521/orcl AS SYSDBA'''
  • CONNECT "sys/oracle@database:1521/orcl AS SYSDBA"
  • CONNECT ""sys/oracle@database:1521/orcl AS SYSDBA""
  • CONNECT """sys/oracle@database:1521/orcl AS SYSDBA"""

Any help is highly appreciated.

Thanks,

==============================================

EDIT:

1) Idea with CONNECT sys/oracle@"database:1521/orcl"

1.1) with "

`CONNECT sys/oracle@"database:1521/orcl" AS SYSDBA
    *
FEHLER in Zeile 16:
ORA-06550: Zeile 16, Spalte 5:
PLS-00103: Encountered the symbol "CONNECT"`

1.2) same with '

CONNECT sys/oracle@'database:1521/orcl' AS SYSDBA
    *
FEHLER in Zeile 17:
ORA-06550: Zeile 17, Spalte 5:
PLS-00103: Encountered the symbol "CONNECT" when expecting one of the
following

2) Leaving the port as optional since 1521 is default:

CONNECT sys/oracle@database/orcl AS SYSDBA
    *
FEHLER in Zeile 17:
ORA-06550: Zeile 17, Spalte 5:
PLS-00103: Encountered the symbol "CONNECT" when expecting one of the
following

3) Missed the last bracket with the @(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1521))(CONNE‌​CT_DATA=(SID=orcl))) AS SYSDBA

CONNECT sys/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1521))(CONNE‌​CT_DATA=(SID=orcl))) AS SYSDBA
    *
FEHLER in Zeile 17:
ORA-06550: Zeile 17, Spalte 5:
PLS-00103: Encountered the symbol "CONNECT" when expecting one of the
following
Community
  • 1
  • 1
snooozer snooozer
  • 51
  • 7
  • What version of sql*plus are you using? Also, have you tried: `connect sys/oracle@"database:1521/orcl" as sysdba`? – Boneist Jan 22 '16 at 10:05
  • Why do you use a port token at all? The 1521 port is default. What will happen if you miss it? – Ilia Maskov Jan 22 '16 at 10:06
  • it should be work `CONNECT sys/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database)(PORT=1521))(CONNECT_DATA=(SID=orcl))) AS SYSDBA` you missed last bracket – Arkadiusz Łukasiewicz Jan 22 '16 at 10:34
  • Thanks for the suggestions, however none of them worked :-( – snooozer snooozer Jan 22 '16 at 12:20
  • 1
    `CONNECT sys/password@localhost:1521/orcl AS SYSDBA` works for me in a SQL file. But looking at the error message "Encountered the symbol CONNECT*" I wonder if you may have an error _before_ that line. e.g. an unterminated PL/SQL block or something similar. –  Jan 22 '16 at 12:33
  • mhh this is really strange....I did the following: `CONNECT sys/oracle@database:1521/orcl AS SYSDBA` works fine....`DECLARE BEGIN CONNECT sys/oracle@database:1521/orcl AS SYSDBA DISCONNECT; END; /` does not work....WTF – snooozer snooozer Jan 22 '16 at 12:38
  • 1
    @snooozersnooozer The `CONNECT` is a SQLPLUS command, it is impossible to use it in pl\sql block – Ilia Maskov Jan 22 '16 at 12:43
  • ok that sucks xD but thx – snooozer snooozer Jan 22 '16 at 12:45
  • You have to take it out of the block and put it separately in sqlplus script file – Ilia Maskov Jan 22 '16 at 12:47
  • yeah ik but I don't want to have the connect when it's not required....in most cases it's not required cause the content inside the pl sql block may have been already executed....if you put your comment as answer I'll approve as correct answer... – snooozer snooozer Jan 22 '16 at 12:52

1 Answers1

3

The CONNECT is a SQLPLUS command, it is impossible to use it in pl\sql block. You have to take it out of the block and put it separately in sqlplus script file (if you want).

Ilia Maskov
  • 1,858
  • 16
  • 26