OraFAQ Forum: SQL & PL/SQL » need sql query

Home » SQL & PL/SQL » SQL & PL/SQL » need sql query (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

need sql query [message #602657]

Fri, 06 December 2013 07:47

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

could you pls provide sql for the below logic

1.In a proceudre i need to inlcude the select query bases on the input variable

create procedure names (fistname varchar2,seconname varchar2,thirdname varchar2)
is

begin

select * from table where (case when firstname is null then 1 else 0 end)=1 or (case when second is null then 1 else 0 end)=1

select firstname,secondname,thirdname from dual

end;

But how can i put the input variables in sql query..

and also i need to include in from clause also
Could you pls provide the same

Report message to a moderator

Re: need sql query [message #602659 is a reply to message #602657]

Fri, 06 December 2013 07:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

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

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

question is unclear.
please post Test Case & expected/desired results (& why)

Report message to a moderator

Re: need sql query [message #602678 is a reply to message #602659]

Fri, 06 December 2013 10:16

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

I am not a mindreader, but here is a wild guess what you want.

DECLARE
   firstname           VARCHAR2 (4000) := NULL;
   secondname          VARCHAR2 (4000) := NULL;
   thirdname           VARCHAR2 (4000) := NULL;
   result              VARCHAR2 (20);
   result_firstname    VARCHAR2 (4000) := NULL;
   result_secondname   VARCHAR2 (4000) := NULL;
   result_thirdname    VARCHAR2 (4000) := NULL;
BEGIN
   SELECT 'No First Second Name'
     INTO result
     FROM DUAL
    WHERE (CASE WHEN firstname IS NULL THEN 1 ELSE 0 END) = 1
          OR (CASE WHEN secondname IS NULL THEN 1 ELSE 0 END) = 1;

   DBMS_OUTPUT.put_line (result);

   SELECT firstname, secondname, thirdname
     INTO result_firstname, result_secondname, result_thirdname
     FROM DUAL;

   DBMS_OUTPUT.
    put_line (
         'Name = '
      || result_firstname
      || ' '
      || result_secondname
      || ' '
      || result_thirdname);
END;

DECLARE
   firstname           VARCHAR2 (4000) := 'MANU';
   secondname          VARCHAR2 (4000) := 'BATHAM';
   thirdname           VARCHAR2 (4000) := NULL;
   result              VARCHAR2 (20);
   result_firstname    VARCHAR2 (4000) := NULL;
   result_secondname   VARCHAR2 (4000) := NULL;
   result_thirdname    VARCHAR2 (4000) := NULL;
BEGIN
   SELECT 'No First Second Name'
     INTO result
     FROM DUAL
    WHERE (CASE WHEN firstname IS NULL THEN 1 ELSE 0 END) = 1
          OR (CASE WHEN secondname IS NULL THEN 1 ELSE 0 END) = 1;

   DBMS_OUTPUT.put_line (result);

   SELECT firstname, secondname, thirdname
     INTO result_firstname, result_secondname, result_thirdname
     FROM DUAL;

   DBMS_OUTPUT.
    put_line (
         'Name = '
      || result_firstname
      || ' '
      || result_secondname
      || ' '
      || result_thirdname);
END;

Don't tell me that you are unaware of exception handling.

Manu

Report message to a moderator

Re: need sql query [message #602679 is a reply to message #602678]

Fri, 06 December 2013 10:26

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Thanks for your reply...
i need small help...

i need out put of the name in a system refcursor
create or replace procedure name is (firstname varchar2,secondname varchar2,thirdname varchar2,rct1 out system_ref_cursor)
is
v_firstname VARCHAR2 (4000) := 'MANU';
v_secondname VARCHAR2 (4000) := 'BATHAM';
v_thirdname VARCHAR2 (4000) := NULL;
result VARCHAR2 (20);
result_firstname VARCHAR2 (4000) := NULL;
result_secondname VARCHAR2 (4000) := NULL;
result_thirdname VARCHAR2 (4000) := NULL;
BEGIN
open rct1 for SELECT 'No First Second Name'

FROM DUAL
WHERE (CASE WHEN firstname IS NULL THEN 1 ELSE 0 END) = 1
OR (CASE WHEN secondname IS NULL THEN 1 ELSE 0 END) = 1;

DBMS_OUTPUT.put_line (result);

SELECT firstname, secondname, thirdname
INTO result_firstname, result_secondname, result_thirdname
FROM DUAL;

DBMS_OUTPUT.
put_line (
'Name = '
|| result_firstname
|| ' '
|| result_secondname
|| ' '
|| result_thirdname);
END;

Report message to a moderator

Re: need sql query [message #602681 is a reply to message #602679]

Fri, 06 December 2013 10:40

Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> create or replace procedure p (rc out sys_refcursor)
  2  is
  3  begin
  4    open  rc for select * from dual;
  5  end;
  6  /

Procedure created.

SQL> var rc refcursor
SQL> exec p(:rc)

PL/SQL procedure successfully completed.

SQL> print rc
D
-
X

1 row selected.

Report message to a moderator

Re: need sql query [message #602682 is a reply to message #602681]

Fri, 06 December 2013 10:49

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

2 things mvrkr44.

1. Format your code using Online SQL Formatter
2. Learn how to use [code] tags in forum.

Manu

[Updated on: Fri, 06 December 2013 10:49]

Report message to a moderator

Previous Topic:	anything better than substr comparison?
Next Topic:	Retrieve data from string column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 12:53:08 CDT 2024