Skip to Main Content

SQL Developer Data Modeler

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!

Problem with Data Modeler in the latest release of SQL Developer (19.1)

LindsaySApr 27 2019 — edited May 29 2019

There appears to be a problem with the Data Modeler embedded in the latest release of SQL Developer (19.1) - Oracle SQL Developer Data Modeler version 19.1.0.081.0911 - primary keys are lost on re-opening.

Create a basic model:

pastedImage_1.png

save, close and re-open - yields:

pastedImage_2.png

The PK has disappeared?

Lindsay.

This post has been answered by Philip Stoyanov-Oracle on May 21 2019
Jump to Answer

Comments

Tubby

990187 wrote:

Iam facing problem in interviews if anyone ask me about NOCOPY,how to call procedure a from procedure b?Any one plz let me know the following

1)what is  the use of nocopy parameter.anyone please do explain with an example?

2)how to call procedure a from procedure b.Plz explain with an example.

Plz help me out in getting them . . waiting for ur reply . . . .

Make the documentation your friend. For questions such as this, there's really no need to go out and solicit help from others. The documentation is quite clear. It's also greatly more efficient for you to be able to find your own answers than to weed through a bunch of replies you're going to get in a forum ... many of which will simply be "RTFM".

PL/SQL Optimization and Tuning

Cheers,

34MCA2K2

If you have understanding of a fundamental programming concept called "pass by reference" you will have no problems in understanding nocopy. To call one P1 procedure from P2 procedure, you can simply write P1(In parameters list, out parameter list); in P2 definition.


However If you really want to learn Oracle PL/SQL I suggest you follow advice of Tubby


Regards,

HuaMin Chen

990187 wrote:

Iam facing problem in interviews if anyone ask me about NOCOPY,how to call procedure a from procedure b?Any one plz let me know the following

1)what is  the use of nocopy parameter.anyone please do explain with an example?

2)how to call procedure a from procedure b.Plz explain with an example.

Plz help me out in getting them . . waiting for ur reply . . . .

Check this

Ask Tom "Anomaly with NOCOPY"

inside which you would also see the way to call one other SP, within a SP.

Billy Verreynne

990187 wrote:

Iam facing problem in interviews if anyone ask me about NOCOPY,

There are 2 ways to pass a parameter from a caller procedureA to called procedureB.

Pass by value.

This means procedureA's variable value for the parameter is copied from procedureA's memory (stack) to procedureB. ProcedureB thus receives a copy of the value. This method is usually the default in programming languages - and works well. Except for dealing with very large values - as that means a slow copy of data from procedureA's stack to procedureB's stack. And lots of memory required for duplicating the value in both stacks.

Pass by reference.

This means procedureA's variable itself is passed as a pointer to procedureB. Thus when procedureB reads the parameter value, it reads it directly from procedureA's memory. When procedureB writes to the parameter (when parameter is defined as an output parameter), procedureB writes directly into procedureA's variable in procedureA's memory. The con with this method is that as procedureB is allowed to write into procedureA's stack, it can potentially corrupt procedureA's memory.

These 2 methods are common throughout most programming languages. In PL/SQL, there is no explicit pointer type (like in C or Pascal for example). So in order to pass by reference, allowing procedureB to write directly into procedureA's variables, procedureA needs to pass its variables to procedureB using the IN OUT NOCOPY clause. This clause tells the PL/SQL engine to pass by the variable by reference (as a pointer).

sgudipudi

BillyVerreynne wrote:

Pass by reference.

This means procedureA's variable itself is passed as a pointer to procedureB. Thus when procedureB reads the parameter value, it reads it directly from procedureA's memory. When procedureB writes to the parameter (when parameter is defined as an output parameter), procedureB writes directly into procedureA's variable in procedureA's memory. The con with this method is that as procedureB is allowed to write into procedureA's stack, it can potentially corrupt procedureA's memory.

These 2 methods are common throughout most programming languages. In PL/SQL, there is no explicit pointer type (like in C or Pascal for example). So in order to pass by reference, allowing procedureB to write directly into procedureA's variables, procedureA needs to pass its variables to procedureB using the IN OUT NOCOPY clause. This clause tells the PL/SQL engine to pass by the variable by reference (as a pointer).

Thanks Billy for the explanation. I have a question. Let's say procedureB writing the ProcedureA's memory multiple times and evrything goes smoothly and finally ProcedureB gives the output to procedureA, what if ProcedureB had an exception after writing couple of times to ProcedureA's memory? would that rollback to previous(original) value?

Thanks again!

Billy Verreynne

Write sample code to explore such questions and determine the answers.

E.g.

SQL> declare
  2          procedure procB( varB in out nocopy integer ) is
  3          begin
  4                  varB := 1;
  5                  raise NO_DATA_FOUND;
  6          end;
  7          procedure procA is
  8                  varA    integer;
  9          begin
10                  varA := 0;
11                  procB( varA );
12          exception when no_data_found then
13                  dbms_output.put_line( 'after exception: varA='||varA );
14          end;
15  begin
16          ProcA;
17  end;
18  /
after exception: varA=1

PL/SQL procedure successfully completed.

SQL>

And (passing by value):

SQL> declare
  2          procedure procB( varB in out integer ) is
  3          begin
  4                  varB := 1;
  5                  raise NO_DATA_FOUND;
  6          end;
  7          procedure procA is
  8                  varA    integer;
  9          begin
10                  varA := 0;
11                  procB( varA );
12          exception when no_data_found then
13                  dbms_output.put_line( 'after exception: varA='||varA );
14          end;
15  begin
16          ProcA;
17  end;
18  /
after exception: varA=0

PL/SQL procedure successfully completed.

SQL>

1 - 6

Post Details

Added on Apr 27 2019
6 comments
1,667 views