Execute a SQL query and assign output

Hi All,


Please find the SQL query skeleton used 



I have defined the logical database.

Now after executuing this i want to return the spacedetails value assigned in it. How can I do that?


Hi Ajithkumar Radhakrishnan,

You are almost done . Just add a select statement at the end of you query:

Select spacedetails ;

and in the output add a variable of type text. 

Give it a try it will do the work.

Pranav Pandey wrote:

Hi Ajithkumar Radhakrishnan,

You are almost done . Just add a select statement at the end of you query:

Select spacedetails ;

and in the output add a variable of type text. 

Give it a try it will do the work.


Im getting syntax error in the query. Can u show me sample

Ajithkumar Radhakrishnan, Please find the sample screen shot in the attachment .

Create a structure with 1 attribute of type text and use the same structure in the AdvSql output 

Pranav Pandey wrote:

Ajithkumar Radhakrishnan, Please find the sample screen shot in the attachment .

Create a structure with 1 attribute of type text and use the same structure in the AdvSql output 



I have tried the same getting above error at select line.


ok try creating an input parameter and use that .Please see the sample screen shot.

Pranav Pandey wrote:

ok try creating an input parameter and use that .Please see the sample screen shot.


No it doesnt help

May be your query is having some error. Have you tested the query ??

What actully you want the get from the query ??

Pranav Pandey wrote:

May be your query is having some error. Have you tested the query ??

What actully you want the get from the query ??


Query is running fine. I want to pass the output generated in that query


Can you send me a sample oml file. 

Hi Ajithkumar Radhakrishnan,

Looks like some issue with query, if you can paste that query rather than taking screenshot. It helps us to provide you the solution.

Hi Ajithkumar.

What "exactly" are you trying to achieve?

Are you using the OutSystems database? Or is it an external source, imported through Integration Studio?

Cheers.

Eduardo Jauch wrote:

Hi Ajithkumar.

What "exactly" are you trying to achieve?

Are you using the OutSystems database? Or is it an external source, imported through Integration Studio?

Cheers.

SyntaxEditor Code Snippet

/* %LogicalDatabase%=GetLogicalDatabase({OS_DUAL}) */
declare spacedetails VARCHAR(300);
cursor l_spaces 
    is
         select df.tablespace_name nam,
         totalusedspace Used,
        (df.totalspace - tu.totalusedspace) free,
         df.totalspace Total,
         round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) persfree
        from
            (select tablespace_name,
                round(sum(bytes) / 1048576) TotalSpace
                from dba_data_files 
                group by tablespace_name) df,
            (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
                from dba_segments 
                group by tablespace_name) tu
            where df.tablespace_name = tu.tablespace_name ;
            
    r_spaces l_spaces%rowtype;
  BEGIN
    -- TODO: Implementation required for procedure MONITOR.checkspaces
               
    for r_spaces in l_spaces
    loop
            --dbms_output.put_line('Inside loop');
            if spacedetails is null
            then
                spacedetails := r_spaces.nam||'-'||r_spaces.free;
                --dbms_output.put_line('Data  ' || spacedetails);
            else
                spacedetails := spacedetails||','||r_spaces.nam||'-'||r_spaces.free;
                --dbms_output.put_line('Data  ' || spacedetails);
            end if;
    end loop;
    --dbms_output.put_line('END  ' || spacedetails);
    
end;

This is what Im doing now I want to assign the spacedetails to an output param.

Sravan Vanteru wrote:

Hi Ajithkumar Radhakrishnan,

Looks like some issue with query, if you can paste that query rather than taking screenshot. It helps us to provide you the solution.

Attached the query below I have executed the query it fine and working


Hi Ajithkumar,

Are you trying to run this code from within the SQL tool? You may find this post an interesting read.

Jorge Martins wrote:

Hi Ajithkumar,

Are you trying to run this code from within the SQL tool? You may find this post an interesting read.


Im running this through SQL query in service studio. And the query is running fine. My only concern is i want to get back the return of that

Hello Ajithkumar,

You can execute multiple statements in SQL tool.
So, you can execute a SELECT after your statement in order to return whatever you are creating.

Cheers.

Create an Entity, e.g. SpaceDetails 

In your advanced SQL use an 'insert into { SpaceDetails} (att1, att2) value (...,...)

The next block in your flow could be an aggregate that will select the records from the Entity SpaceDetails

you might use dynamic sql when nessary

e.g. 

varchar2 myInsertStatement = 'Insert into {SpaceDetails} (a,b,c) values ';

myInsertStatement += '('||spacedetails||','||r_spaces.nam||'-'||r_spaces.free||');

execute immediate myInsertStatement;

Jorge Martins wrote:

Hi Ajithkumar,

Are you trying to run this code from within the SQL tool? You may find this post an interesting read.


I'm not calling any stored procedure. I have written a sql snippet it has an output and has to be returned.

Hi Ajithkumar,

And between the suggestions given to you to you to return the result of your SQL snippet, like do a SELECT or INSERT into an entity, there is any other problem?

Cheers.