1

Topic: Stored procedures, difficult data structures on an input

Colleagues good afternoon!
Educate me please on stored procedures and functions.
Whether there is a possibility to transfer in storable PL\SQL procedure a difficult data structure for example List, Map or .?
For example is  to save a question and its possible variants of answers, they will be saved in two tables.
This structure on Java to the side is easily described by standard collections or Json somehow.
It seemed to me what here pertinently to create stored procedure to transfer to it the data and let she already writes as it is necessary.  two calls JDBC of the driver from Java I will have one. However I faced that  how to transfer a difficult data structure.
Generally at me on the project eternal dispute, stored procedures are necessary to us or not.
One of fears it that stored procedures  to us development and application update.
In this plan how much these fears are justified? After all I so understand, what update of stored procedure it simply start SQL of a script with CREATE OR REPLACE PROCEDURE?

2

Re: Stored procedures, difficult data structures on an input

vlad2010 wrote:

However I faced that  how to transfer a difficult data structure.

Probably it made sense to esteem
JDBC Reference Information
SQL and PL/SQL Mappings to Oracle and JDBC Types
Then to familiarize that such serialization. To itself the approach can quite be to pack input and output data in XML (json).

vlad2010 wrote:

Generally at me on the project eternal dispute, stored procedures are necessary to us or not.

Generally if at you on the project realization that Orakl not only a black box for data storage there would be new thoughts would come.
And that  such terry  and other ORM a nonscalable shit and then try to employ  which it removes bottlenecks without thorough refactoring.
At such understanding there is a suspicion that  counterfeit because well it is impossible to pay for it such money without understanding what for. Is myslq, for example.
And, yes, can it is simple  office and rollbacks.
Anything personal, all is simple it very ridiculously.

3

Re: Stored procedures, difficult data structures on an input

Yes there is no all license be assured. Long to explain, it is simple licenses enough. But yes, to do it now it is necessary at all , and with ++ .
Whether I am correct I understand that logic transfer on the side oracle in reasonable limits is justified?
You simply understand a situation, I told it to people that generally there is such thing as stored procedures.
Here SQL requests on java to the code are spread also all normally. I after all feel already arguments of my colleagues and against. We admit "and what we will do if we want to transfer application with oracle on what that another? With pl/sql logic in procedures we hang up".  it already the demagogy went.

4

Re: Stored procedures, difficult data structures on an input

vlad2010;
It would be possible to speak about something if you familiarize with a question and stated that with that agree, and with that not so.
to begin with
business logic database vs application
sql vs orm

vlad2010 wrote:

and what we will do if we want to transfer application with oracle on what that another?

you with surprise can find out that blocking in different DBMS is organized on a miscellaneous and your application generally can cease is correct to work.
Esteem "Thom Kajt Orakl for professionals" there right at the beginning informative stories.
And what you will do if want to transfer application on a C ++ or, say, C#?
It is possible to go on abstraction layer above and to write on DSL which will generate the code in appropriate language.

5

Re: Stored procedures, difficult data structures on an input

dbms_photoshop wrote:

Pogugli to begin with
business logic database vs application
sql vs orm

I
Almost everywhere write that business logic in basis it badly
But here a question and where the business logic begins?
a + b it already the business logic or still is not present? (And also group by, where, etc)
Upon that that saw in practice, logic parts put in different places

6

Re: Stored procedures, difficult data structures on an input

grok wrote:

it is passed...
I
Almost everywhere write that business logic in basis it badly
But here a question and where the business logic begins?
a + b it already the business logic or still is not present? (And also group by, where, etc)
Upon that that saw in practice, logic parts put in different places

On the same rope it is possible how to be hung up, and linen to dry

7

Re: Stored procedures, difficult data structures on an input

grok wrote:

I
Almost everywhere write that business logic in basis badly

I implied it that when the person googles it still thinks.
Forgot that it truly not only only not for all.

grok wrote:

but here a question and where the business logic begins?

Now  "business logic".
Only it is not necessary to google "business" and the logician "separately".
Above probability to come to incorrect outputs.

grok wrote:

upon that that saw in practice, logic parts put in different places

Glanced in your subjects, it would be strongly surprising if you worked, where in practice apply sensible approaches.
Here it was touching the Problem of self-development of the programmer .
Respect for sincerity.

8

Re: Stored procedures, difficult data structures on an input

grok wrote:

I
Almost everywhere write that business logic in basis it badly

You were deceived by the experts in marketing forming sentences on transfer of business logic from a DBMS on AS.

9

Re: Stored procedures, difficult data structures on an input

Well, and such question still
I so understood what to return from PL/SQL functions it is possible a line in length of 4000 byte . it is the maximum length VARCHAR.
I turns out json should be inscribed in this restriction?
As that turns out a little.
In any way it is impossible to bypass this restriction?
The Oracle 11g 11.2.0.4 interests

10

Re: Stored procedures, difficult data structures on an input

vlad2010;
You appeared is too lazy even to descend under links which I for you diligently prepared.

11

Re: Stored procedures, difficult data structures on an input

dbms_photoshop;
I will dare to disagree.
I saw that it is possible to return the user type.
It is possible to use these types at creation of tables for example
CREATE TABLE employee_tab OF employee_typ;
Problem in that that I should return or transfer the big list from structures of type (pseudocode)

class A {
int id;
String name;
List <B> listOfB;
}

Where

class B {
int id_b;
String title;
String description;
}

I  for you am obvious things probably, but check they are obvious not all.
PL/SQL At all user friendly language.

12

Re: Stored procedures, difficult data structures on an input

vlad2010 wrote:

to transfer big

Transfer for some calls of procedures with scalar parameters, developer friendly you ours.

13

Re: Stored procedures, difficult data structures on an input

Actions of trances;
Thanks if to obtain these given by two calls that it is lost all meaning is lost to write stored procedure

14

Re: Stored procedures, difficult data structures on an input

wrote:

the User types it is in this respect even more terrible, at them is not present replace.

Or I do not understand that such the user type, or I do not understand about what replace there is a speech.

15

Re: Stored procedures, difficult data structures on an input

AmKad wrote:

it is passed...
  hints on "CREATE OR REPLACE"

16

Re: Stored procedures, difficult data structures on an input

[quote = - Eugene-] Aftor  hints on "CREATE OR REPLACE"

Well here and I understand, why he says that there is no slander replace.

17

Re: Stored procedures, difficult data structures on an input

AmKad wrote:

Well here and I not understand, why he says that there is no slander replace.

18

Re: Stored procedures, difficult data structures on an input

grok wrote:

I
Almost everywhere write that business logic in basis it badly

You, when read, considered, what 95 % of people on the earth - idiots?
To easier these people approximately about 50 years ago the Internet in hands gave.
Well, suddenly you did not know...

19

Re: Stored procedures, difficult data structures on an input

Whether

vlad2010 wrote:

There is a possibility to transfer in storable PL\SQL procedure a difficult data structure for example List, Map or .?
For example is  to save a question and its possible variants of answers, they will be saved in two tables.

Just in case, if still a problem did not solve...
It is possible to make easily enough and without serious consequences trace still. In the image:
0) to write down your data in the table,  them at lines. And setting it one general identifier (type a handling packet #x)
1) to cause procedure, to transfer it this identifier.
2) all there to process and return that is necessary.
3) given to erase.

20

Re: Stored procedures, difficult data structures on an input

;
Here for these many thanks.
The sheet is the table, I am finite did not think herein!

21

Re: Stored procedures, difficult data structures on an input

MasterZiv;
At first all the same I will try with the user type, . I equally need as to write down and read to these types from basis

22

Re: Stored procedures, difficult data structures on an input

vlad2010;
In Orakle for transmission of difficult structured data there is a special type of columns of tables XMLTYPE. There to you and parcers and , API for XML <=> SQL there and reversely. That else it is necessary  with any user types? As dependences in packets and procedures told there  with support of changes of types. And DML there to launch it is not trivial on tables with  types and collections enough.