Today's Messages (on)  | Unanswered Messages (off)

Forum: Reports & Discoverer
 Topic: with Oracle Report Builder 10. how to print a frame only in last page at the end of the page
Re: with Oracle Report Builder 10. how to print a frame only in last page at the end of the page [message #681609 is a reply to message #681588] Fri, 07 August 2020 15:38
Littlefoot
Messages: 21579
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Stretch the enclosing frame through whole page size, set it to be fixed in size. Then anchor that "third" frame to the bottom of the enclosing frame.
Forum: SQL & PL/SQL
 Topic: Find out how long is the field or column
Re: Find out how long is the field or column [message #681598 is a reply to message #681597] Fri, 07 August 2020 00:09
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

pablolee wrote on Fri, 02 November 2018 07:21
Hi James. This looks a LOT like a homework assignment, which implies that you are studying SQL as part of a course.
Have you had a crack at writing the query? If you have, post what you tried and folk here will happily help to guide you. If you haven't, read your notes and give it a try, then post what you tried and folk here will happily help to guide you.
GL

And if you want to continue to get help you should envisage to feedback in your topics.

[Updated on: Fri, 07 August 2020 00:11]

Report message to a moderator

 Topic: INSERT WHEN NOT EXIST
Re: INSERT WHEN NOT EXIST [message #681599 is a reply to message #681596] Fri, 07 August 2020 03:23
deepakdot
Messages: 51
Registered: July 2015
Member
This works. Thank you.
 Topic: Errors running Proc that compiles fine
Errors running Proc that compiles fine [message #681600] Fri, 07 August 2020 10:36
cfairtp
Messages: 10
Registered: July 2020
Junior Member
Good morning. Below is the code from my stored procedure, a copy\paste of the error message, and a copy \ paste of some of the values in the Review_Count_SQL column from the table I pull in. Any help is greatly appreciated.

create or replace PROCEDURE Tracker_Status_Counts
   (RptPeriod  IN CHAR  ) AS 
   
 InsertCode  VARCHAR2(2000) ; 

 BEGIN 

-- exec Tracker_Status_Counts ('2020-10' ) ;

---------------------------------------------
InsertCode := 

'INSERT INTO                                     ' ||
'       VACLT.XCLT_Tracker_Results               ' ||
'             (Rpt_Period  , Wave   ,            ' || 
'              ValueStream , Task   ,            ' ||
'              CNT                  ,            ' ||
'              Txn_Variance_Count                ' ||
'             )                                  ' ||
' SELECT '''||RptPeriod || ''' AS RptPeriod ,    ' ||
'    Wave   ,                  ' || 
'        ValueStream , Task   ,                  ' || 
'      ('||    '''||Review_Count_SQL  ||'') AS Cnt'      ||
'     ,   NULL AS TXN_VARIANCE_COUNT             ' ||
'  FROM VACLT.XCLT_TRACKER_SQL                   ' ||
' WHERE REVIEW_COUNT_SQL IS NOT NULL             '      
; 
---------------------------------------------
---------------------------------------------

EXECUTE IMMEDIATE InsertCode ; 

---------------------------------------------
---------------------------------------------

COMMIT;

COMMIT;
END;  
HERE IS THE ERROR MESSAGE

Error starting at line : 1 in command -
BEGIN Tracker_Status_Counts ('2020-10' ); END;
Error report -
ORA-01722: invalid number
ORA-06512: at "VACLT.TRACKER_STATUS_COUNTS", line 32
ORA-06512: at line 1
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

HERE ARE THREE VALUES IN THE Review_Count_SQL COLUMN

SELECT COUNT(*) FROM VCLT_FIXED_ASSETS WHERE AO = '40'
SELECT COUNT(*) FROM VCLT_P2P WHERE Wave = 'ABC'
SELECT COUNT(TRANS_NUMBER) FROM VCLT_OBL WHERE WAVE = 'ABC' AND FLAG_ISSUE <> 'Yes'

Re: Errors running Proc that compiles fine [message #681601 is a reply to message #681600] Fri, 07 August 2020 10:44
John Watson
Messages: 8318
Registered: January 2010
Location: Global Village
Senior Member
You are probably trying to insert a string into a date. That doesn't work (or if it does, only by luck). SQL is a strongly typed language: you MUST use type casting functions such as to_date if you want your code to run reliably. Note that this is not just a matter of not getting errors, it also about getting results that are correct.
Re: Errors running Proc that compiles fine [message #681602 is a reply to message #681601] Fri, 07 August 2020 10:54
cfairtp
Messages: 10
Registered: July 2020
Junior Member
Correct results are ALWAYS good. Keeps me employed.

The CNT column that equates to the string with Review_Count_SQL is a NUMBER. I just double checked, in fact

Other ideas?
Re: Errors running Proc that compiles fine [message #681603 is a reply to message #681602] Fri, 07 August 2020 10:57
John Watson
Messages: 8318
Registered: January 2010
Location: Global Village
Senior Member
You need to show your table definitions. It is (obviously!) a problem with data types.
Re: Errors running Proc that compiles fine [message #681604 is a reply to message #681603] Fri, 07 August 2020 11:04
cfairtp
Messages: 10
Registered: July 2020
Junior Member
Here is tracker status counts
RPT_PERIOD	VARCHAR2(7 CHAR)	Yes	
WAVE	VARCHAR2(25 CHAR)	Yes	
VALUESTREAM	VARCHAR2(25 CHAR)	Yes	
TASK	VARCHAR2(25 CHAR)	Yes	
CNT	NUMBER(15,0)	Yes	
TXN_VARIANCE_COUNT	NUMBER(15,0)	Yes	
Thanks
Re: Errors running Proc that compiles fine [message #681605 is a reply to message #681604] Fri, 07 August 2020 11:21
EdStevens
Messages: 1230
Registered: September 2013
Senior Member
what do you get when you execute just the SELECT portion of your built INSERT statement? That should be pretty revealing.
Re: Errors running Proc that compiles fine [message #681606 is a reply to message #681604] Fri, 07 August 2020 11:28
John Watson
Messages: 8318
Registered: January 2010
Location: Global Village
Senior Member
cfairtp wrote on Fri, 07 August 2020 17:04
Here is tracker status counts
RPT_PERIOD	VARCHAR2(7 CHAR)	Yes	
WAVE	VARCHAR2(25 CHAR)	Yes	
VALUESTREAM	VARCHAR2(25 CHAR)	Yes	
TASK	VARCHAR2(25 CHAR)	Yes	
CNT	NUMBER(15,0)	Yes	
TXN_VARIANCE_COUNT	NUMBER(15,0)	Yes	
Thanks
Er... Is the above meant to be useful information? Smile Just describe both tables. Use SQL*Plus, and copy/paste.
Re: Errors running Proc that compiles fine [message #681607 is a reply to message #681605] Fri, 07 August 2020 11:29
cfairtp
Messages: 10
Registered: July 2020
Junior Member
Good for you! And even better for me. I need an open paren and a closed paren to start\end that line. I have a lunch date but will hop on that when I get back. Thank you so, so much. all of you
Re: Errors running Proc that compiles fine [message #681608 is a reply to message #681607] Fri, 07 August 2020 12:46
Michel Cadot
Messages: 67290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create statements and insert statements for all objects so that we will be able work to reproduce what you have.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

[Updated on: Fri, 07 August 2020 12:46]

Report message to a moderator

Re: Errors running Proc that compiles fine [message #681610 is a reply to message #681600] Fri, 07 August 2020 15:44
Solomon Yakobson
Messages: 2977
Registered: January 2010
Location: Connecticut, USA
Senior Member
When you use dynamic SQL it is always a good idea to check generated SQL statements:

SQL> create or replace PROCEDURE Tracker_Status_Counts
  2     (RptPeriod  IN CHAR  ) AS
  3
  4   InsertCode  VARCHAR2(2000) ;
  5
  6   BEGIN
  7
  8  -- exec Tracker_Status_Counts ('2020-10' ) ;
  9
 10  ---------------------------------------------
 11  InsertCode :=
 12
 13  'INSERT INTO                                     ' ||
 14  '       VACLT.XCLT_Tracker_Results               ' ||
 15  '             (Rpt_Period  , Wave   ,            ' ||
 16  '              ValueStream , Task   ,            ' ||
 17  '              CNT                  ,            ' ||
 18  '              Txn_Variance_Count                ' ||
 19  '             )                                  ' ||
 20  ' SELECT '''||RptPeriod || ''' AS RptPeriod ,    ' ||
 21  '    Wave   ,                  ' ||
 22  '        ValueStream , Task   ,                  ' ||
 23  '      ('||    '''||Review_Count_SQL  ||'') AS Cnt'      ||
 24  '     ,   NULL AS TXN_VARIANCE_COUNT             ' ||
 25  '  FROM VACLT.XCLT_TRACKER_SQL                   ' ||
 26  ' WHERE REVIEW_COUNT_SQL IS NOT NULL             '
 27  ;
 28  DBMS_OUTPUT.PUT_LINE(InsertCode);
 29  END;
 30  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC Tracker_Status_Counts ('2020-10' )
INSERT INTO
VACLT.XCLT_Tracker_Results                            (Rpt_Period  , Wave   ,
ValueStream , Task   ,                          CNT                  ,
Txn_Variance_Count                             )
SELECT '2020-10' AS RptPeriod ,        Wave   ,
ValueStream , Task   ,                        ('||Review_Count_SQL  ||') AS Cnt
,   NULL AS TXN_VARIANCE_COUNT               FROM VACLT.XCLT_TRACKER_SQL
WHERE REVIEW_COUNT_SQL IS NOT NULL

PL/SQL procedure successfully completed.

SQL>
So obviously inserting '||Review_Count_SQL ||' into numeric column will fail. And, Review_Count_SQL isn't even a column in VACLT.XCLT_TRACKER_SQL to begin with.

SY.



Current Time: Sat Aug 08 04:23:34 CDT 2020