Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Developer log has no source/message

User_K2AC3May 22 2014 — edited May 23 2014

I'm not sure if I'm missing something obvious here. I have a stored procedure for my Oracle SQL server (11g).

I'm using flow control to keep my INSERT, UPDATE, DELETE functionality within 1 stored procedure. The weird issue I'm running into is that all of the functionality of this works, but when running an UPDATE or DELETE I receive a log from SQL Developer that has no source or message. The proper changes are committed to the database, but I receive an annoying blank log and I am just confused at this point.

Any info would be great! Thanks

create or replace PROCEDURE          DRAWINGOPERATION

(

  P_CURSOR OUT SYS_REFCURSOR

, P_OPERATION IN VARCHAR2

, P_DRAWINGID IN NUMBER

, P_DRAWINGDATE IN DATE

, P_PRIZEAMOUNT IN VARCHAR2

, P_GAMEID IN NUMBER

) AS

BEGIN

  DECLARE

    return_drawingid NUMBER;

  BEGIN

 

  CASE p_operation

  WHEN 'INSERT' THEN GOTO c_Insert;

  WHEN 'UPDATE' THEN GOTO c_Update;

  WHEN 'DELETE' THEN GOTO c_Delete;

  ELSE GOTO end_sec;

  END CASE;

  <<c_Insert>>

      INSERT INTO DRAWING(GAMEID,DRAWINGDATE,PRIZEAMOUNT) VALUES (p_gameid,p_drawingdate,p_prizeamount)

      RETURNING DRAWINGID

      INTO return_drawingid;

      COMMIT;

      OPEN P_CURSOR FOR SELECT * FROM DRAWING WHERE DRAWINGID = return_drawingid;

      GOTO end_sec;

 

  <<c_Update>>

      IF p_drawingdate IS NOT NULL THEN UPDATE DRAWING SET DRAWINGDATE = P_DRAWINGDATE WHERE DRAWINGID = P_DRAWINGID; END IF;

      IF p_prizeamount IS NOT NULL THEN UPDATE DRAWING SET PRIZEAMOUNT = P_PRIZEAMOUNT WHERE DRAWINGID = P_DRAWINGID; END IF;

      COMMIT;

      GOTO end_sec;

   

  <<c_Delete>>

      --PRIMARY KEY DELETE

      DELETE

      FROM DRAWING

      WHERE drawingid = P_DRAWINGID;

      GOTO end_sec;

     

  <<end_sec>>

  NULL;

 

  END;

END DRAWINGOPERATION;

Comments

User_K2PC5
Answer

Looks like I was able to figure it out using the following connection string after digging deeper into one of the threads here.
User Id=proxy;Data Source=dbhostname:1521/DBSERVICENAME;Proxy User Id=userid; Proxy Password=userpassword;

Source of the answer was the following, as provided by Kristian Vinther on dba.stackexchange (I can't post links or I would just provide the hyperlink)

finally found out how to represent the proxy user login via my connection string.
Proxy User ID = MyUser
Proxy Password = MyUser password
User Id = SchemaName
User Password not set.

SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual
Marked as Answer by User_K2PC5 · Nov 19 2020
Christian.Shay -Oracle

Hi,
Thanks for posting. It is no longer needed to use the workaround of using an ODP.NET Connection String to connect using a proxy user name and password like you show above..
As of version 19.3.3, we added a Show more options checkbox that will reveal the proxy username and password fields.
So, to connect using a proxy, in the connection dialog, check the Show more options checkbox and provide the proxy username and password in the fields that are revealed.

emanuele mattiolo

Hi and sorry, I couldn't log in with my proxy user. See screenshot of the sql developer configuration and that of VS. where am I wrong? Thank you

Documento1.pdf

Christian.Shay -Oracle

Looks like you have a new issue. Next time, please start a new thread instead of adding to an old one.

In your screenshot you are using brackets. With this VS Code extension you do not use brackets.

From the documentation:

In other tools, you may have connected using this format: proxyusername[username]/ [proxypassword]

With Oracle Developer Tools for VS Code, fill the connection dialog like so:

Username: Enter database username. ("/" (forward slash) is not allowed with proxy connections).

Password: Enter the database password. Leave blank for single-session proxy. Password is required for two-session proxy.

Show more options: Check this box

Proxy Username: The proxy username. (Other tools use the format proxyusername[username]/ [proxypassword]). Use "/" (forward slash) if using external authentication (i.e. OS/Kerberos/Certificate) or if using SEPS wallet for database username and password.

Proxy Password: The proxy password. (Other tools use the format proxyusername[username]/ [proxypassword]). Leave blank if Proxy Username field is "/" (forward slash) .

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2014
Added on May 22 2014
3 comments
1,735 views