-1

I have following table structure.

CREATE TABLE "DADMIN"."DATATEMPLATES" 
(   
"ID" VARCHAR2(100 BYTE),  
"QUERY" CLOB, 
"ACTIVESTATUS" VARCHAR2(2 BYTE), 
)

I'm storing query data like this SELECT CD.CIF , CD.CREDIT_ACCOUNT FROM CUTOMERDATA CD WHERE ID = :PARA_ID

Currently I'm executing it through the C# raw sql execution

Instead of that, I need to execute the table query through the stored procedure. How can I execute table stored query using SP? and return its data using cursor output?

Update:

This is my sample SP,

PROCEDURE Get_customer_data (p_Query_id IN VARCHAR2,
                             p_cursor OUT OUTPUTCURSOR)
IS
BEGIN
    DECLARE
        l_query CLOB;
    BEGIN
        SELECT query
        INTO   l_query
        FROM   querytemplates
        WHERE  id = p_Query_id ;

        OPEN p_cursor FOR l_query;
    END;
END;

But this makes error

ORA-06512: at line 1
01008. 00000 -  "not all variables bound"

And my other problem is, the table stored query also excepting parameter called PARA_ID how can I pass that.

sample table stored query as follows,

SELECT CD.CIF , CD.CREDIT_ACCOUNT FROM CUTOMERDATA CD  WHERE ID = :PARA_ID
Gamma
  • 317
  • 1
  • 6
  • 22
  • Please [edit] the question to include a [MRE] and clarify the problem. Your table has no relationship with the query; the query is used to read data and not to store data; you have not shared your C# code; you have not told us what the issues/errors are with your current code; you have not told us what your sample data is; or what your expected output is. – MT0 Mar 08 '22 at 13:41

1 Answers1

0

One option is to return refcursor. Here's an example.

Sample data (that stores queries):

SQL> SELECT * FROM datatemplates;

        ID QUERY                                                        A
---------- ------------------------------------------------------------ -
         1 select deptno, dname from dept                               A
         2 select d.dname, e.ename, e.job, e.sal from emp e join dept d A
            on d.deptno = e.deptno


SQL>

Procedure - based on passed ID parameter - selects appropriate query and opens a refcursor based on that select statement:

SQL> CREATE OR REPLACE FUNCTION f_test (par_id IN NUMBER)
  2     RETURN SYS_REFCURSOR
  3  IS
  4     l_query  CLOB;
  5     l_rc     SYS_REFCURSOR;
  6  BEGIN
  7     SELECT query
  8       INTO l_query
  9       FROM datatemplates
 10      WHERE id = par_id;
 11
 12     OPEN l_rc FOR l_query;
 13
 14     RETURN l_rc;
 15  END;
 16  /

Function created.

Testing:

SQL> SELECT f_test (1) FROM DUAL;

F_TEST(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

Some more testing:

SQL> SELECT f_test (2) FROM DUAL;

F_TEST(2)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

DNAME          ENAME      JOB              SAL
-------------- ---------- --------- ----------
ACCOUNTING     CLARK      MANAGER         2450
ACCOUNTING     KING       PRESIDENT       5000
ACCOUNTING     MILLER     CLERK           1300
RESEARCH       JONES      MANAGER         2975
RESEARCH       FORD       ANALYST         3000
RESEARCH       ADAMS      CLERK           1100
RESEARCH       SMITH      CLERK            800
RESEARCH       SCOTT      ANALYST         3000
SALES          WARD       SALESMAN        1250
SALES          TURNER     SALESMAN        1500
SALES          ALLEN      SALESMAN        1600
SALES          JAMES      CLERK            950
SALES          BLAKE      MANAGER         2850
SALES          MARTIN     SALESMAN        1250

14 rows selected.


SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57