Home » SQL & PL/SQL » SQL & PL/SQL » Passing input parameter to a like variable in procedure (Oracle 11g)
Passing input parameter to a like variable in procedure [message #573394] Thu, 27 December 2012 16:26 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Hi

I have a procedure named 'GetShipperinfo' which takes i_name as input and needs to build a cursor taking i_name as input

i.e

The following sql when executed at sqlplus prompt gives correct results.

select dept, supplier, shipper_id
from shippers
where upper(shipper_name) like upper('Frank Robert%');

How can I tranform this inside a cursor within a procedure passing 'Frak Robert' value as i_name input.

i.e I should be able to call the procedure as follows

sql> variable v1 varchar2;
sql> exec pkg_shipment.GetShipperinfo('Frank Robert',:v1);
sql> print :v1;

Should the cursor inside the procedure be built as follows

cursor c1 is
select dept, supplier, shipper_id
from shippers
where shipper_name like ''||upper(i_name'%''||)'';

Iam unable to build the sql for the cursor.

Any help in building the cursor sql for the procedure is greatly appreciated.

Thanks

Re: Passing input parameter to a like variable in procedure [message #573395 is a reply to message #573394] Thu, 27 December 2012 17:35 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>Any help in building the cursor sql for the procedure is greatly appreciated.
Procedure? What procedure? I don't see any procedure.
Have you ever constructed a working procedure on your own"
http://asktom.oracle.com contains many fine coding examples.
Re: Passing input parameter to a like variable in procedure [message #573402 is a reply to message #573394] Fri, 28 December 2012 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

Michel Cadot wrote on Wed, 28 November 2012 08:18
..Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


BlackSwan wrote on Wed, 07 November 2012 19:36
...
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.


Michel Cadot wrote on Thu, 08 November 2012 08:14
...Use SQL*Plus and copy and paste your session, the WHOLE session.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


BlackSwan wrote on Thu, 01 November 2012 23:42
It appears that you REFUSE to comply Posting Guidelines; so as far as I am concerned You're On Your Own (YOYO)!

BlackSwan wrote on Fri, 04 May 2012 12:22
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

BlackSwan wrote on Thu, 19 July 2012 13:56
>

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

BlackSwan wrote on Fri, 20 April 2012 15:04
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/



And so on.



[Updated on: Fri, 28 December 2012 01:27]

Report message to a moderator

Re: Passing input parameter to a like variable in procedure [message #573411 is a reply to message #573402] Fri, 28 December 2012 04:09 Go to previous message
Littlefoot
Messages: 19349
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is how I understood the question.
SQL> create or replace procedure p_job (par_ename in varchar2, par_job out varchar2)
  2  is
  3    cursor c1 is
  4      select job
  5       from emp
  6       where ename like par_ename || '%';
  7  begin
  8    open c1;
  9    fetch c1 into par_job;
 10    close c1;
 11  end;
 12  /

Procedure created.

SQL> var v1 varchar2(20);
SQL> exec p_job('SCOTT', :v1);

PL/SQL procedure successfully completed.

SQL> print v1;

V1
--------------------------------
ANALYST

SQL>
Previous Topic: Refcursor question
Next Topic: Exception query
Goto Forum:
  


Current Time: Fri Aug 01 11:04:25 CDT 2014

Total time taken to generate the page: 0.05465 seconds