APEX

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!

How can i get the return variables of GET method in PL/SQL procedure

Soukaina IDRISSIFeb 15 2018 — edited Feb 24 2018

Hi everyone,

I created a PL/SQL procedure named getInfoUser(id IN NUMBER), that call a GET method( which return 10 variables) of one particular RESTful Web Service, and i want to know how can i do, to return this 10 variables in my own procedure.

Thank you in advance for help.

This post has been answered by Pavel_p on Feb 21 2018

Jump to Answer

Frank Kulash

Answer

Hi,

When you say "CONNECT BY LEVEL <= x" , make sure at least one of the following is true:
(1) the table in the query has only one row, or
(2) you know what you're doing.

In other situations, create a Counter Table that has all the numbers you'll ever need, and join to the Counter Table where you otherwise would do a CONNECT BY.
In the definition for the Counter Table, the CONNECT BY clause will resemble your original CONNECT BY clause, but instead of "CONNECT BY LEVEL <= x" it will be "CONNECT LEVEL <= *(SELECT MAX (x) ...)* ".
When you use the counter table, the join condition will be similar to your original CONNECT BY condition.

WITH	cntr	AS
(
	SELECT	LEVEL	AS n
	FROM	dual
	CONNECT BY	LEVEL <= ( SELECT MAX (LENGTH (a_id))
			      	   FROM	  a
				 )
)
,	split_a_id	AS
(
	SELECT	  a.a_text,
--		  a.a_id,	-- For debugging only
--		  c.n,	-- For debugging only
		  SUM (TO_NUMBER ( SUBSTR ( a.a_id || '0'
	    	       	 	 	  , -c.n
					  , 1
					  )
				  )
	    	      ) OVER ( PARTITION BY  a.a_text
		      	       ORDER BY      c.n
			     )	AS cut_length
	FROM      a     a
	JOIN	  cntr	c	ON	c.n	<= LENGTH (a.a_id)
)
SELECT    SUBSTR ( a_text
	         , 1
	         , LENGTH (a_text) - cut_length
	         ) 	AS cut_text
FROM      split_a_id
ORDER BY  a_text
,     	  cut_text	DESC
;

Now that you're dealing with multiple rows, you need a PARTITION BY clause in the analytic SUM function.

Thanks for including the CREATE TABLE and INSERT statements.
Please format your code, and post it between

 tags so this site won't remove any spaces.
The link was a good idea, but it seems to be a link to the wrong thread.

Edited by: Frank Kulash on Nov 17, 2009 2:46 PM
Commented out 2 line I forgot to delete after testing.

Marked as Answer by Ludy · Sep 27 2020

Ludy

Thanks Frank Kulash,
now got better understanding in using the LEVEL in CONNECT BY.
Now things are getting clearer to me.

sure will post code between

 tags, and sorry about the link.

Aketi Jyuuzou

I like recursive with clause B-)
I do Not have Oracle11gR2
Therefore I made below solution using PostgreSQL8.4 :8}

CREATE TABLE TTT(ID text,Val text);
INSERT INTO TTT(ID,Val) VALUES('13233424','ABCDEFGHIJKLMNOPQRSTUV');
INSERT INTO TTT(ID,Val) VALUES('13233424','WXYZABCDEFGHIJKLMNOPQR');

with recursive rec(ID,Val,LV,cuts) as(
select ID,Val,1   ,substr(ID,1,1)    from TTT
union all
select ID,Val,LV+1,substr(ID,LV+1,1) from rec
 where LV < length(ID))
select substr(Val,1,(sum(cuts::integer) over(partition by Val order by LV))::integer) as CUT_TEXT
  from rec
order by Val,LV desc;

       cut_text
-----------------------
ABCDEFGHIJKLMNOPQRSTUV
ABCDEFGHIJKLMNOPQR
ABCDEFGHIJKLMNOP
ABCDEFGHIJKL
ABCDEFGHI
ABCDEF
ABCD
A
WXYZABCDEFGHIJKLMNOPQR
WXYZABCDEFGHIJKLMN
WXYZABCDEFGHIJKL
WXYZABCDEFGH
WXYZABCDE
WXYZAB
WXYZ
W

I like model clause solution too :D

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 22 2018

Added on Feb 15 2018

#apex-discussions, #plsql, #procedure

23 comments

2,754 views

APEX

How can i get the return variables of GET method in PL/SQL procedure

Comments

Post Details