Home » Developer & Programmer » Forms » diffcult issue in accessing Lob object and functions? (Forms 6i)
diffcult issue in accessing Lob object and functions? [message #420544] Mon, 31 August 2009 04:38 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi,

I had written a code in when list changed

Declare
	  Sqltxt clob;
	  Lnth  Number;
Begin
	  Select Sql_text into sqltxt
	    From Sql_Collection where Rtrim(Short_Topic) = RTrim(:Replst1);
	    	   
	   Fetch_param(sqltxt);	   	   
	   Show_Window('ParamWin');
End;


Fetch_program procedure

PROCEDURE Fetch_Param(sqltxt Clob) IS
Pos1 Number(5) := 1;
pos2 Number(5);
partstr Varchar(32767);
Val  Varchar2(50);
i Number := 1;
Type P_arry is table of Varchar2(50) index by Binary_Integer;
Vp_arry P_arry;
Found Boolean;
BEGIN
	   Go_Block('Param');
	   Next_item;	   
	   Loop	  
	   	  Found := False; 	  
        Pos1 := dbms_lob.Instr(sqltxt,':',1,i);
        Exit When Pos1 = 0;            
        Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;
        Vp_arry(i) := dbms_lob.substr(sqltxt,pos1,pos2);
        If Vp_arry.count is not null and i > 1 Then
        	For j in 1..Vp_arry.count-1 Loop
        		If Vp_arry(j) = dbms_lob.substr(sqltxt,pos1,pos2) Then
        			 Found := True;
        			 exit;
        		End If;
          End Loop;
        End If;
        If not Found Then
           set_item_property(:System.Cursor_item,prompt_text,dbms_lob.substr(sqltxt,pos1,pos2));
           set_item_property(:System.Cursor_item,Prompt_font_style,Font_Underline);          
           next_item;
        End If;                        
        i := i + 1;        
    End Loop;
END;


Problem

The running form automatically closed when it access the line of the above procedure.


 Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;


please help me, What is the problem.

kanish
Re: diffcult issue in accessing Lob object and functions? [message #420547 is a reply to message #420544] Mon, 31 August 2009 04:50 Go to previous messageGo to next message
c.schoeberl
Messages: 22
Registered: August 2009
Junior Member
mm_kanish05 wrote on Mon, 31 August 2009 04:38
Hi,

I had written a code in when list changed

        If Vp_arry.count is not null and i > 1 Then
        	For j in 1..Vp_arry.count-1 Loop
        		If Vp_arry(j) = dbms_lob.substr(sqltxt,pos1,pos2) Then
        			 Found := True;
        			 exit;
        		End If;
          End Loop;
        End If;
       
END;




Because you write
 If Vp_arry.count is not null and i > 1 Then 
so, i is 1 and you say if i > 1 but i = 1 you have to edit the if clause to

 If Vp_arry.count is not null and i >= 1 Then 


Regards

Re: diffcult issue in accessing Lob object and functions? [message #420551 is a reply to message #420547] Mon, 31 August 2009 05:03 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Sorry, When i debug the program, not even read the below line then how possible to reach as you said.

Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;


kanish
Re: diffcult issue in accessing Lob object and functions? [message #420553 is a reply to message #420544] Mon, 31 August 2009 05:13 Go to previous messageGo to next message
c.schoeberl
Messages: 22
Registered: August 2009
Junior Member
Did you solve your Problem ?

I see you dont have any exception block in your code

EXCEPTION
  WHEN Others THEN
  Message(sqlerrm);


Maybe Pos1 is more then 100

[Updated on: Mon, 31 August 2009 05:15]

Report message to a moderator

Re: diffcult issue in accessing Lob object and functions? [message #420555 is a reply to message #420553] Mon, 31 August 2009 05:21 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Not yet solved

Even i add the Exception, Again the same problem when i change the list, automatically the form closed.

kanish
Re: diffcult issue in accessing Lob object and functions? [message #420557 is a reply to message #420544] Mon, 31 August 2009 05:30 Go to previous messageGo to next message
c.schoeberl
Messages: 22
Registered: August 2009
Junior Member
Hmm

I'm searching for the Problem after i look on your code i found this

Pos1 := dbms_lob.Instr(sqltxt,'[B]:[/B]',1,i);


i don't know what you have in your table Sql_Collection but maybe you mean ; and not :

i will search Smile
Re: diffcult issue in accessing Lob object and functions? [message #420558 is a reply to message #420544] Mon, 31 August 2009 05:40 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

when i

   select sql_text into sqltxt from sql_collection where sql_id = 1


then

SELECT a.custcode,
       a.custname,
       a.invoiceno,
       a.invoicedt,
       basicrate,
       a.chassisno,
       a.engineno,
       a.documentno,
       b.documentdate
  FROM (SELECT a.custcode,
               c.custname,
               a.invoiceno,
               a.invoicedt,
               b.chassisno,
               b.engineno,
               d.unitid,
               d.periodid,
               d.documentno,
               basicrate
          FROM invoice a, invoiceitem b, custmast c, chassisitem d
         WHERE a.unitid = :Unit_id
           AND a.invoicedt BETWEEN :Invoice_From_dt AND :Invoice_To_Dt
           AND a.unitid = b.unitid
           AND a.periodid = b.periodid
           AND a.invoiceno = b.invoiceno
           AND a.custcode = c.custcode
           AND c.private_customer = ''Y''
           AND b.chassisno = d.chassis_no
           AND b.engineno = d.engine_no
           AND a.unitid = d.unitid
           AND b.unitid = d.unitid) a,
       chassisarrival b
 WHERE a.unitid = b.unitid AND a.periodid = b.periodid AND a.documentno = b.documentno


the above query is my value of sqltxt. And search for parameter are( where with ':').

kanish
[EDITED by DJM: fixed mega-crappy formatting]

[Updated on: Mon, 21 September 2009 19:54] by Moderator

Report message to a moderator

Re: diffcult issue in accessing Lob object and functions? [message #420559 is a reply to message #420544] Mon, 31 August 2009 06:04 Go to previous messageGo to next message
c.schoeberl
Messages: 22
Registered: August 2009
Junior Member
Type P_arry is table of Varchar2(50) index by Binary_Integer;
Vp_arry P_arry;


Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;


Maybe the Problem is that your table of Varchar could only fetch 50 Character. increase to 500 that went well for me. So the Table is too small i think
Re: diffcult issue in accessing Lob object and functions? [message #420561 is a reply to message #420559] Mon, 31 August 2009 06:17 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

That is not problem it only pick the Parameter name only 50 is more enough

ie in between : and space like

:Invoice_From_dt 


i thought the problem is in when list changed

  Select Sql_text into sqltxt
	    From Sql_Collection where Rtrim(Short_Topic) = RTrim(:Replst1);


when i execute the above query in isqlplus

sql_text only show matter 50 char, the same when i execute like below

  Select dbms_lob.substr(Sql_text) into sqltxt
	    From Sql_Collection where Rtrim(Short_Topic) = RTrim(:Replst1);



it show the whole value. if i use the above query the sqltxt should be varchar2 (because the function return varchar)


kanish
Re: diffcult issue in accessing Lob object and functions? [message #423026 is a reply to message #420561] Mon, 21 September 2009 19:56 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Previous Topic: when validate-item
Next Topic: Senmail didn't work through shell script when trigger from forms
Goto Forum:
  


Current Time: Wed Dec 07 23:58:17 CST 2016

Total time taken to generate the page: 0.11617 seconds