Home » SQL & PL/SQL » SQL & PL/SQL » Cursor is not taking the not in class and NOT IN IS Mal Functioning (merged 2 threads)
Cursor is not taking the not in class and NOT IN IS Mal Functioning (merged 2 threads) [message #313691] Mon, 14 April 2008 03:53 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
CREATE OR REPLACE PROCEDURE MW_Columns_SP
(
P_DB_Name IN VARCHAR2 default null ,
P_TBL_Name IN VARCHAR2 default null,
Non_Columns IN VARCHAR2 default null,
Req_Columns IN OUT NVARCHAR2
)
AS
Ret_Col VARCHAR2(4000);
v_Col_Non_Columns VARCHAR2(4000);
V_cnt Number;
BEGIN
v_cnt := 0;

DECLARE
CURSOR CUR is
SELECT column_name FROM all_tab_columns where table_name =P_TBL_Name and owner = P_DB_Name and column_name NOT IN('''' || REPLACE(Non_Columns, ',',''',''') || '''');
v_col_Names CUR%rowtype;
BEGIN
FOR C1 IN CUR
LOOP
--FETCH CUR INTO v_col_Names;
--EXIT WHEN CUR%NOTFOUND;

v_cnt := v_cnt+1;
IF v_cnt = 1 then
v_Col_Non_Columns := 'TRIM('||TRIM(C1.COLUMN_NAME)||')';
ELSE
--v_Col_Non_Columns := v_Col_Non_Columns ||''''||','||''''||'|| C1.COLUMN_NAME||' ;
v_Col_Non_Columns := TRIM(v_Col_Non_Columns) ||','|| 'TRIM('||TRIM(C1.COLUMN_NAME)||')';
END IF;
END LOOP;

--CLOSE CUR;
Ret_Col := REPLACE(v_Col_Non_Columns,'''','');
Ret_Col := REPLACE(v_Col_Non_Columns,',', '||'',''||');
END;

Req_Columns := Ret_Col;
END;



I have created the above procedure . If i will pass the unwanted column name this procedure will remove those columns from all_tab_columns and will return the remaining columns in the table but I am facing a problem in this .

The declared cursor ins not include the not in class why?

When i execute this all columns from the table is coming?


could you help me?
Re: NOT IN IS Mal Functioning [message #313697 is a reply to message #313691] Mon, 14 April 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.


Regards
Michel
Re: Cursor is not taking the not in class [message #313700 is a reply to message #313691] Mon, 14 April 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 14 April 2008 11:12
Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.


Regards
Michel


Re: Cursor is not taking the not in class [message #313702 is a reply to message #313691] Mon, 14 April 2008 04:17 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Did you notice the number of times that your other post was viewed and yet there are still no answers. This is because you STILL refuse to follow the posting guidelines and format your code with code tags. People click on your post, see that it is not formatted, and move on to the next post. You will continue to have this happen until you learn how to post.
Re: Cursor is not taking the not in class [message #313704 is a reply to message #313691] Mon, 14 April 2008 04:35 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
rangan.s wrote on Mon, 14 April 2008 14:43

When i execute this all columns from the table is coming?

i doubt your statement.your code won't compile at the 1st place.don't you get an error something like this :

Encountered the symbol "DECLARE" when expecting one of the following.

regards,
Re: Cursor is not taking the not in class [message #313708 is a reply to message #313704] Mon, 14 April 2008 04:45 Go to previous message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
but it should show all columns except the Not in Columns

For example

Take Emp Table i dont want empno column

If i will execute this


DECLARE
op varchar2(4000);
begin
MW_COLUMNS_SP('Scott','Emp', 'empno',Req_Columns => op);
end;

it should bring the columns except the Empno column which we have passed in the procedure.

when i do this the cursor is not taking the not in clause
Previous Topic: Help in Dates
Next Topic: connect by prior
Goto Forum:
  


Current Time: Fri Dec 09 17:06:45 CST 2016

Total time taken to generate the page: 0.33923 seconds