Hello everyone,
I have a table
ReportingTable having following rows
select * from REPORTINGTABLE;
q_id q_value
-----------------
q1 select * from customer
q2 select * from pilot
q3 select * from staff
What I have to do is create a query which queries this table and execute the query inside the table in order to get result on basis of q_id, then I need this data to be used in tableau further on what I have tried is creating procedure and function but unluckily haven't got the desired results
Function:
create or replace FUNCTION exec_dynamic_query (
p_q_id varchar
)
return sys_refcursor
is
stmt REPORTINGTABLE.Q_VAL%TYPE;
return_val sys_refcursor;
v_list customer%ROWTYPE;
begin
select q_val into stmt from reportingtable where q_id = p_q_id;
dbms_output.put_line(stmt);
open return_val for stmt;
return return_val;
end;
this function is returning the correct sys_refcursor but I am unable to use it as the result is something like
select exec_dynamic_query('q1') from dual;Output:
{<CUSTOMERID=C0001,NAME=Jonie Jones,ADDRESS=50 Lesson Lane, Erebor, New Endor,COUNTRY=New Endor,EMAIL=joniejones@hotmail.com,PHONE=+4958364765,BIRTHDATE=12-MAY-56,PASSPORTNUM=92736401>,<CUSTOMERID=C0002,NAME=Sarah Delaney,ADDRESS=22 Time Drive, Arcadia, Gallifrey,COUNTRY=Gallifrey,EMAIL=sarahdelaney@memail.com,PHONE=+9283746198,BIRTHDATE=14-FEB-92,PASSPORTNUM=83748917>,<CUSTOMERID=C0003,NAME=Mark Foster,ADDRESS=64 Bond Street, London, UK,COUNTRY=UK,EMAIL=markfoster@gmail.com,PHONE=+3748264763,BIRTHDATE=30-DEC-89,PASSPORTNUM=32764920>,<CUSTOMERID=C0004,NAME=Patrick Jolie,ADDRESS=18 Park Road, Melbourne,COUNTRY=AUS,EMAIL=patrickjolie@hotmail.com,PHONE=+6334495820,BIRTHDATE=05-MAY-46,PASSPORTNUM=35270848>,<CUSTOMERID=C0005,NAME=Nik Malema,ADDRESS=124 Tenth Street, Cape Town, South Africa,COUNTRY=South Africa,EMAIL=nikmalema@hotmail.com,PHONE=+5783749100,BIRTHDATE=17-JUN-88,PASSPORTNUM=98562538>}but what I need is
C0001 Jonie Jones 50 Lesson Lane, Erebor, New Endor New Endor joniejones@hotmail.com +4958364765 12-MAY-56 92736401
C0002 Sarah Delaney 22 Time Drive, Arcadia, Gallifrey Gallifrey sarahdelaney@memail.com +9283746198 14-FEB-92 83748917
C0003 Mark Foster 64 Bond Street, London, UK UK markfoster@gmail.com +3748264763 30-DEC-89 32764920
C0004 Patrick Jolie 18 Park Road, Melbourne AUS patrickjolie@hotmail.com +6334495820 05-MAY-46 35270848
C0005 Nik Malema 124 Tenth Street, Cape Town, South Africa South Africa nikmalema@hotmail.com +5783749100 17-JUN-88 98562538
the issue with the procedure is that I cannot be called in tableau, can you please help me out in that
Thanks
First up:
Executing queries stored in tables is a
bad idea. You should
avoid doing this!
Major issues with this are:
- It's a massive security loophole; anyone with write access to the table could abuse this to query data they shouldn't be able to
- It's a nightmare to debug; instead of having the query itself, you first need to run a query to find the query
Really you should connect Tableau directly to the tables you need to access. That's the
point of reporting tools like this!
If for some reason you're forced to go down this route (
strongly push back on this) the issue is you're returning a ref cursor instead of a table. So either you need to:
- Find out if/how to consume cursors in Tableau - read their docs/contact their support team for help with this
- Convert the cursor output to XML/JSON (assuming Tableau is able to consume in these way you want) and return that
- Build a (pipelined) table function to consume the cursor and turn it into rows
For more on table functions, read:
https://blogs.oracle.com/oraclemagazine/post/pipelined-table-functions