How To execute procedure

85 pts.
Tags:
Oracle SQL
Oracle stored procedures
I have procedure iven below Output of the procedure below is multiple rows but i am getting only 1st row how to use concept of SYS_REFCURSOR over here CREATE OR REPLACE procedure Vishal_PROC_sp1 (kpiid in number, val1 out varchar2, val2 OUT Number) as cursor c1 is select kpimainid,sysdate,selectstmt,clause,to_char(sysdate,'MON') month,cautionmax,cautionmin from kpimain where kpimainid = kpiid; c1_rec c1%ROWTYPE; BEGIN open c1; Loop Fetch c1 into c1_rec; Begin Exit when c1%NOTFOUND; EXECUTE IMMEDIATE c1_rec.selectstmt || ' '|| c1_rec.clause into val1,val2; Exception When Others then dbms_output.put_line('Error'); End; End LOOP; Close c1; Commit; END; / Tahnks in advance

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think you are getting the last row only, not the first, because you are overwriting val1 and val2 on each loop iteration.

On the other hand, you could return a REF CURSOR if the data you want to return proceeds from a cursor (i.e. a query), but in this case, it comes from many different queries (one different query for each loop iteration).

Not knowing what exactly you want to accomplish, or why you need to do this, I think you could return a PL/SQL table instead.

First, declare the types:

<pre>PACKAGE the_types IS
TYPE record_type is record (val1 varchar2(100),val2 number);
TYPE table_type is table of record_type;
END;</pre>

Then, write your procedure, something like this:

<pre>CREATE OR REPLACE procedure Vishal_PROC_sp1 (kpiid IN NUMBER, the_table out the_types.table_type) AS
cursor c1 is select kpimainid,sysdate,selectstmt,clause,to_char(sysdate,’MON’) month,cautionmax,cautionmin from kpimain where kpimainid = kpiid;
l_table_index number := 0;
BEGIN
the_table := the_types.table_type();
FOR i IN c1 LOOP
the_table.extend();
l_table_index := l_table_index + 1;
EXECUTE IMMEDIATE i.selectstmt ||’ ‘|| i.clause into the_table(l_table_index).val1,the_table(l_table_index).val2;
END LOOP;
END;</pre>

You will have the output in the OUT parameter ‘the_table’.

Discuss This Question: 15  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Hanrahul
    when i exeute EXECUTE IMMEDIATE c1_rec.selectstmt || ' '|| c1_rec.clause into val1,val2; this clause i will get 1 query which is executed and 2 output variable with datatype varchar2 and number will be returned now how can i see the result of this procedure
    85 pointsBadges:
    report
  • Hanrahul
    when i execute select selectstmt,clause from kpimain where kpimainid = 901; Output is SELECTSTMT -------------------------------------------------------------------------------------------------- select worktype a, (sum(estlabcost)-sum(actlabcost))*100/sum(estlabcost) b from workorder CLAUSE ------------------------------------------------------------ group by worktype having sum(estlabcost) > 0 NOW when i execute this EXECUTE IMMEDIATE c1_rec.selectstmt || ' '|| c1_rec.clause Output is A B -------------------- ---------- RCE -8.4045926 REMINDER 35.2218539 USERMOVE 22.6936353 COMPLO -25.377912 CUSSPECIAL -778.18256 38 rows selected. so how i will get these multiple values with above procedure Thanks in advance
    85 pointsBadges:
    report
  • carlosdl
    I said that you data proceeds from many queries because you have a cursor, and inside the loop you have this line: EXECUTE IMMEDIATE c1_rec.selectstmt || ' '|| c1_rec.clause into val1,val2; So, if the cursor 'c1_rec' returns more than one row, you will have more than one different queries. Does the below query return only 1 row ? select kpimainid,sysdate,selectstmt,clause,to_char(sysdate,'MON') month,cautionmax,cautionmin from kpimain where kpimainid = kpiid;
    69,835 pointsBadges:
    report
  • Hanrahul
    Yes select kpimainid,sysdate,selectstmt,clause,to_char(sysdate,’MON’) month,cautionmax,cautionmin from kpimain where kpimainid = kpiid; Query return only 1 row actualy i was trying to get multiple values into val1 and val2 variable so i use loop but it is not working
    85 pointsBadges:
    report
  • carlosdl
    Thanks for clarifying that. So, you could use a simple select statement, and avoid the use of the cursor. You have two options, use the procedure I suggested in the answer, to return a PL/SQL table (eliminating the use of the cursor), or you could return a REF CURSOR. To return a REF CURSOR, you should do something similar to this: Define the type:
    PACKAGE the_types IS
      TYPE cursor_type IS REF CURSOR;
    END;
    And then your procedure would look something like this:
    CREATE OR REPLACE procedure Vishal_PROC_sp1 (kpiid IN NUMBER, p_recordset OUT the_types.cursor_type) AS 
     	l_selectstmt	varchar2(200);
     	l_clause		varchar2(200);
    BEGIN
    	 SELECT selectstmt,clause INTO l_selectstmt,l_clause
    	   	FROM kpimain WHERE kpimainid = kpiid;
    	 OPEN p_recordset for l_selectstmt||' '||l_clause;
    END;
    Then you can fetch the data from the returned cursor in you calling procedure, in a similar way as how you do it with a normal explicit cursor. Let us know if you need further help.
    69,835 pointsBadges:
    report
  • Hanrahul
    Hi, Thanks for replay it works and there is actualy no need to create package I run this procedure as below and it works for me CREATE OR REPLACE procedure Vishal_PROC_sp1 (kpiid IN NUMBER, p_recordset OUT sys_refcursor) AS l_selectstmt varchar2(200); l_clause varchar2(200); BEGIN SELECT selectstmt,clause INTO l_selectstmt,l_clause FROM kpimain WHERE kpimainid = kpiid; OPEN p_recordset for l_selectstmt||' '||l_clause; END; / var v_kpiid Number; BEGIN :v_kpiid := 901; END; / var g_Cursor; EXECUTE Vishal_PROC_sp1(:v_Kpiid,:g_Cursor); PRINT :g_Cursor; Thanks again
    85 pointsBadges:
    report
  • carlosdl
    You are right Hanrahul, there is no need to create the package. I ran a small test, but I did it on an 8i database, in which SYS_REFCURSOR cannot be used as a type. I'm glad it worked.
    69,835 pointsBadges:
    report
  • Hanrahul
    Ok Thanks Again For Your Reply I have 1 More Question This Query Works SELECT distinct replace('''0001,0002''',',',''',''') as assetnum FROM asset O/P := '0001','0002' =========================================================================== This Query Works SELECT distinct(assetnum) FROM asset WHERE assetnum in ('0001','0002') O/P := 0001 0002 =========================================================================== But This Does Not Work SELECT distinct(assetnum) FROM asset WHERE assetnum in (SELECTdistinct replace('''0001,0002''',',',''',''') as assetnum FROM asset); O/P := no rows selected Can You Help Me Thanks Again For Reply
    85 pointsBadges:
    report
  • carlosdl
    You are comparing two different things here, although they look similar. Let's see if I find the way to explain this. This query: SELECT distinct replace(”’0001,0002”’,',’,”’,”’) as assetnum FROM asset Returns this: ‘0001′,’0002′ This is just one value: "‘0001′,’0002′". I mean, this is just one string, containing this text: '0001′,’0002′ (the quotes are part of the string). When you run this query: SELECT distinct(assetnum) FROM asset WHERE assetnum in (SELECTdistinct replace(”’0001,0002”’,',’,”’,”’) as assetnum FROM asset); you are looking for records with assetnum = "‘0001′,’0002′ " For this query: SELECT distinct(assetnum) FROM asset WHERE assetnum in (<sub_query>); to produce the same results as this one: SELECT distinct(assetnum) FROM asset WHERE assetnum in (’0001′,’0002′) The <sub_query> should be a query that returns two rows, one being 0001, and the other 0002 (as varchars, but without the quotes). Why do you need that REPLACE ? Feel free to ask if I did not make myself clear. Regards,
    69,835 pointsBadges:
    report
  • Hanrahul
    Actually I want to write a procedure which takes input parameter and and gives result, But for that input parameter i want to supply multiple values so how can i do it
    85 pointsBadges:
    report
  • carlosdl
    You will probably need to use Dynamic SQL to achieve that. For example: EXECUTE IMMEDIATE 'SELECT distinct(assetnum) FROM asset WHERE assetnum in ('||your_parameter||');'; where your parameter should contain something like this: ’0001′,’0002′
    69,835 pointsBadges:
    report
  • Hanrahul
    hi I want to write a procedure which takes input parameter and and gives result, But for that input parameter i want to supply multiple values so how can i do it Procedure Is As Follows
    CREATE OR REPLACE PROCEDURE sp_Select_LaborStatus
    (v_status IN varchar2,
    v_labor_Cursor OUT SYS_REFCURSOR)
    AS
    BEGIN
    OPEN v_labor_Cursor FOR
      SELECT labor.status, labor.laborcode, person.displayname, labor.worksite, labor.worklocation,
           laborqual.qualificationid, qualification.description, laborqual.status as status2, laborqual.enddate
      FROM labor left outer join laborqual on labor.laborcode = laborqual.laborcode
      AND  labor.orgid = laborqual.orgid, person, qualification
    WHERE
    labor.status  in v_status
       AND labor.personid = person.personid
       AND laborqual.qualificationid = qualification.qualificationid
       AND laborqual.orgid = qualification.orgid
    ORDER BY labor.laborcode, laborqual.qualificationid ;
    END;
    /
    and v_status has values ('ACTIVE','INACTVE') so how can i supply these values to the above procedure
    85 pointsBadges:
    report
  • carlosdl
    Something like this: CREATE OR REPLACE PROCEDURE sp_Select_LaborStatus (v_status IN varchar2, v_labor_Cursor OUT SYS_REFCURSOR) AS l_selectstatement varchar2(500); BEGIN l_selectstatement := 'SELECT ... FROM ... WHERE ... AND labor.status in (' || v_status || ')' || AND ... ORDER BY labor.laborcode, laborqual.qualificationid'; OPEN v_labor_Cursor FOR l_selectstatement; END;
    69,835 pointsBadges:
    report
  • Hanrahul
    Hi thanks for reply but i have problem Procedure is created but when i execute it ,gives error like this BEGIN Rahul_LaborStatus1(:assetnum,:g_Cursor); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "MAXIMO_BAE.RAHUL_LABORSTATUS1", line 7 ORA-06512: at line 1 ERROR: ORA-24338: statement handle not executed SP2-0625: Error printing variable "g_cursor" Procedure i created as below CREATE OR REPLACE PROCEDURE Rahul_LaborStatus1 (v_status IN varchar2, v_labor_Cursor OUT SYS_REFCURSOR) AS l_selectstatement varchar2(500); BEGIN l_selectstatement := 'SELECT labor.status, labor.laborcode, person.displayname, labor.worksite, labor.worklocation, laborqual.qualificationid, qualification.description, laborqual.status as status2, laborqual.enddate FROM labor left outer join laborqual on labor.laborcode = laborqual.laborcode AND labor.orgid = laborqual.orgid, person, qualification WHERE labor.status in (' || v_status || ') AND labor.personid = person.personid AND laborqual.qualificationid = qualification.qualificationid AND laborqual.orgid = qualification.orgid ORDER BY labor.laborcode, laborqual.qualificationid '; OPEN v_labor_Cursor FOR l_selectstatement; END; / var v_status varchar2(500); execute :v_status :='ACTIVE,INACTIVE'; var g_Cursor refcursor; EXECUTE Rahul_LaborStatus1(:v_status,:g_Cursor); print :g_Cursor;
    85 pointsBadges:
    report
  • carlosdl
    Try increasing the size of the l_selectstatement variable. For example: l_selectstatement varchar2(2000); Also, for the procedure to work without more changes, the v_status must be sent to the procedure this way: execute :v_status :=''’ACTIVE'',''INACTIVE''’;
    69,835 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following