OraFAQ Forum: SQL & PL/SQL » Meaning of the piece of code

Home » SQL & PL/SQL » SQL & PL/SQL » Meaning of the piece of code (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production,TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production)

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

Meaning of the piece of code [message #600002]

Wed, 30 October 2013 15:40

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

Member

Hi,

I encountered below piece of statement in one of procedure. I am not able to understand the meaning of it. Please help.

BEGIN
	    isql_Cursor := DBMS_SQL.OPEN_CURSOR;
if cenclosure is NULL then
		gv_sqlstmt_in  := 'select decode(column_id,1,''select '',''||'')||'||
		                  chr(39)||rtrim1||chr(39)||'||'||'column_name'||'||'||chr(39)||
						  rtrim2||-- chr(39)||
                          ''||''||chr(39)||'||'||
						  chr(39)||'||'||chr(39)||'||'||
-- 						  'chr(39)||'||'decode(column_id,max_colid,null,''^'')'||'||chr(39)'||
						  'chr(39)||'||'decode(column_id,max_colid,null,'||chr(39)||
						  pDelimeter||chr(39)||')'||'||chr(39)'||
                          -- chr(39)||'
						  ' into  :gv_sqlstmt_tmp '||'
                          from   all_tab_columns a,
                                (select max(column_id) max_colid
                                 from   all_tab_columns b
                                 where  owner = upper('||chr(39)||
                                                      cTable_Owner||chr(39)||')
                                 and    table_name = upper('||chr(39)||
                                                      cTable_Name||chr(39)||')) b
                          where  owner = upper('||chr(39)||cTable_Owner||chr(39)||')
                          and    table_name = upper('||chr(39)||cTable_Name||chr(39)||')
                          order by  column_id';
	else
	      gv_sqlstmt_in  := 'select decode(column_id,1,''select '',''||'')||'||
		                'chr(39)'||'||'||chr(39)||cenclosure||chr(39)||'||'||'chr(39)'||'||'||chr(39)||'||'||chr(39)||'||'||
		                chr(39)||rtrim1||chr(39)||'||'||'column_name'||'||'||chr(39)||
				rtrim2||-- chr(39)||
                                ''||''||chr(39)||'||'|| CHR(39) ||'||'||chr(39)||'||'||'chr(39)'||'||'||chr(39)||cenclosure||chr(39)||'||'||'chr(39)'||'||'||
                                chr(39)||'||'||chr(39)||'||'||
-- 				'chr(39)||'||'decode(column_id,max_colid,null,''^'')'||'||chr(39)'||
				'chr(39)||'||'decode(column_id,max_colid,null,'||chr(39)||
				pDelimeter||chr(39)||')'||'||chr(39)'||
                                -- chr(39)||'
				' into  :gv_sqlstmt_tmp '||'
                                from   all_tab_columns a,
                                (select max(column_id) max_colid
                                 from   all_tab_columns b
                                 where  owner = upper('||chr(39)||
                                                cTable_Owner||chr(39)||')
                                 and    table_name = upper('||chr(39)||
                                                   cTable_Name||chr(39)||')) b
                          where  owner = upper('||chr(39)||cTable_Owner||chr(39)||')
                          and    table_name = upper('||chr(39)||cTable_Name||chr(39)||')
                          order by  column_id';
	  end if;

Cenclosure is a variable , which I am using to enclose the columns.
What I actually want to know is the meaning of chr(39) and the purpose of whole step before using the DBMS_SQL.PARSE.

Report message to a moderator

Re: Meaning of the piece of code [message #600003 is a reply to message #600002]

Wed, 30 October 2013 15:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> select chr(39) from dual
  2  ;

C
-
'

CHR(39) is a single quote mark as shown above

Report message to a moderator

Re: Meaning of the piece of code [message #600004 is a reply to message #600002]

Wed, 30 October 2013 15:47

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

gupta27 wrote on Thu, 31 October 2013 02:10

What I actually want to know is the meaning of chr(39) and the purpose of whole step before using the DBMS_SQL.PARSE.

chr(39) is a single quote.

chr(39)||string||chr(39)

is same as

'''string'''

Report message to a moderator

Re: Meaning of the piece of code [message #600005 is a reply to message #600003]

Wed, 30 October 2013 15:53

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

Member

Why chr(39) is being used for ' (quotation mark)? When quotation mark is being used at other places in the statement, why not everywhere.

Can you please explain me the purpose of the whole statement as in what the whole statement is doing ?

Report message to a moderator

Re: Meaning of the piece of code [message #600006 is a reply to message #600005]

Wed, 30 October 2013 15:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

gupta27 wrote on Thu, 31 October 2013 02:23

Why chr(39) is being used for ' (quotation mark)? When quotation mark is being used at other places in the statement, why not everywhere.

If you ask us about WHY? Sorry, only the developer could answer to your why. I would never code in that way.

Report message to a moderator

Re: Meaning of the piece of code [message #600007 is a reply to message #600006]

Wed, 30 October 2013 15:58

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

Member

what could be better way to code in this particular scenario?

Report message to a moderator

Re: Meaning of the piece of code [message #600009 is a reply to message #600006]

Wed, 30 October 2013 16:01

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Better to use Quoting Mechanism for String Literals from 10g and up.

Have a look at docs, http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

Some examples by Tim Hall, Quoting Mechanism for String Literals

Regards,
Lalit

Report message to a moderator

Previous Topic:	Can we have 2 insert statements for 1 for loop
Next Topic:	convert local time to UTC

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 05:44:44 CDT 2024