I have been given PL SQL stored Function with the following signature and body description.  Create or Replace function Get_Employee(user_id in Number, res_out out sys_refcursor) return  sys_refcursor is v_result sys_refcursor; begin //here goes function body return v_result end;   Basically, I need to call this function from Java. I have tried all possible ways to register out parameter as well as return value which both are refcursor type. Here is the way which I believe is the most correct way.      String employeeSQL = "{ ? = call Get_Employee(?,?)}";             CallableStatement statement = null;             Employee employee = null;             ResultSet result = null;     try{     statement = conn.prepareCall(employeeSQL);     statement.registerOutParameter(1, OracleTypes.Cursor); //registering return value     statement.registerOutParamter(2, OracleTypes.Cursor); // registering out parameter     statement .setLong(3, userId); // setting input parameter     statement.execute();     resultSet = (ResultSet) statement.getObject(1); // getting return value     }finally{     statement.close();     trsultSet.close();     }   The problem is whenever the statement is executed, it throws NullPointer Exception. So I suspect I do not register parameters in a correct way. Any help appreciated!
129
What are Null Pointer Exceptions (java.lang.NullPointerException) and what causes them?  What methods/tools can be used to determine the cause so that you stop the exception from causing the program to terminate prematurely?
23661
Context Around this Code: I am making a schedulable class run each Sunday to track a couple of metrics important to the team.   Question: I am looking to consolidate DML operations and a big part of this is understanding pass by reference vs pass by value.  I want to pass the salesOps user object to the method SDREfficiencyLastWeek. I made the return type void, because I figured that I will be passing this by reference and I can make all the updates in the method without having to anything.   Why I am confused:    I read the above article and am thrown off by the following statement:  "Non-primitive data type arguments, such as sObjects, are also passed into methods by value. "   If the object is indeed passed by reference, then the query for the salesOps user in my code should be sufficient enough to edit all fields on that object without actually having to state each field in the query.  global class SDREfficientMetricsSchedulable implements Schedulable  {     global void execute(SchedulableContext sc)      {         User salesOps = [SELECT ID FROM USER WHERE id = '0056A000002Z3p9QAC'];         List&lt;String&gt; queries = new List&lt;String&gt;();          String contactsWorked_lastWeek  = 'select count(id) results from contact where LastActivityDate = last_week';         queries.add(contactsWorked_lastWeek);          String demosSet_lastWeek            = 'select count(id) results from form__c where CreatedDate = last_week';         queries.add(demosSet_lastWeek);          String demosCompleted_lastWeek  = 'select count(id) results from opportunity where (stagename = \'Unqualified Demo Completed\'  OR stagename = \'Qualified Demo Completed\' )  and RecordTypeId = \'0126A000000yudDQAQ\' and CloseDate = last_week';         queries.add(demosCompleted_lastWeek);          String qualifiedDemos_lastWeek  = 'select count(id) results  from opportunity where RecordTypeId = \'0126A0000004Ao6QAE\' and isclosed = false and  SDR_Owner__c != null and CreatedDate = last_week';         queries.add(qualifiedDemos_lastWeek);          String closedWon_lastWeek       = 'select count(id) results  from opportunity where RecordTypeId = \'0126A0000004Ao6QAE\' and iswon = true and  SDR_Owner__c != null and CloseDate = last_week';         queries.add(qualifiedDemos_lastWeek);            SDREfficiencyLastWeek(queries, salesOps);          update salesOps;     }       public void SDREfficiencyLastWeek(List&lt;String&gt; queries, User salesOps )     {        }  }
117581
I want to write a funcion or procedure that can be used in the IN clause of another procedure. The function or procedure would return ID numbers.   The main procedure would say something like   SELECT * FROM EMPLOYEES WHERE OFFICE_ID IN (GET_OFFICE_IDS);  -- GET_OFFICE_IDS requires no parameters   GET_OFFICE_IDS returns a VARCHAR2 with the ID separated by commas. When I run the main procedure, I get a "ORA-01722: invalid number" error which makes sense but I don't know where I need to go from here.   Do I need GET_OFFICE_IDS to create a temp table that the main procedure uses? If so, will there be a performance penalty?
6359
What's the best way to get the last inserted id using sqlite from Java? Google is giving me different answers--some say select the last-insert-rowid; others say call statement.getGeneratedKeys(). What's the best route to take? (I just want to return the id, not use it for other inserts or anything.)
119920
I'm working on an application for work that is going to query our employee database. The end users want the ability to search based on the standard name/department criteria, but they also want the flexibility to query for all people with the first name of "James" that works in the Health Department. The one thing I want to avoid is to simply have the stored procedure take a list of parameters and generate a SQL statement to execute, since that would open doors to SQL injection at an internal level.  Can this be done?
107598
String queryFields = null;              for(Schema.FieldSetMember field : SObjectType.Task.FieldSets.Activity_Detail_Page.getFields()){                  queryFields = (queryFields == null ? field.getFieldPath() : queryFields+', '+field.getFieldPath());               }                String query = 'Select '+queryFields+' from Task where Id=\''+activityId+'\'';               task = Database.query(query);   i'll creating a field-set,now how do i add link on any field?  above code is  for dynamically query creation . below code of page   &lt;apex:pageBlock title="Activity" id="Block1"&gt;       &lt;apex:pageBlockSection title="Activity Detail"&gt;         &lt;apex:repeat value="{!$ObjectType.Task.FieldSets.Activity_Detail_Page}" var="field"&gt;             &lt;apex:outputField value="{!Task[field.fieldPath]}" /&gt;         &lt;/apex:repeat&gt;     &lt;/apex:pageBlockSection&gt; &lt;/apex:pageBlock&gt;    As per the image attached, I want to add a link in Rounded field that liks to account. Can any one help me?
69850
I have the following code:  Using cmd As SqlCommand = Connection.CreateCommand     cmd.CommandText = "UPDATE someTable SET Value = @Value"     cmd.CommandText &amp;= " WHERE Id = @Id"     cmd.Parameters.AddWithValue("@Id", 1234)     cmd.Parameters.AddWithValue("@Value", "myValue")     cmd.ExecuteNonQuery End Using   I wonder if there is any way to get the final SQL statment as a String, which should look like this:  UPDATE someTable SET Value = "myValue" WHERE Id = 1234   If anyone wonders why I would do this:   for logging (failed) statements for having the possibility to copy &amp; paste it to the Enterprise Manager for testing purposes
102930
I will preface this question by saying, I do not think it is solvable. I also have a workaround, I can create a stored procedure with an OUTPUT to accomplish this, it is just easier to code the sections where I need this checksum using a function.  This code will not work because of the Exec SP_ExecuteSQL @SQL calls. Anyone know how to execute dynamic SQL in a function? (and once again, I do not think it is possible. If it is though, I'd love to know how to get around it!)  Create Function Get_Checksum (     @DatabaseName      varchar(100),     @TableName         varchar(100) ) RETURNS FLOAT AS BEGIN   Declare @SQL        nvarchar(4000)  Declare @ColumnName varchar(100)  Declare @i          int  Declare @Checksum   float  Declare @intColumns table (idRecord int identity(1,1), ColumnName varchar(255))  Declare @CS         table (MyCheckSum bigint)   Set @SQL =          'Insert Into @IntColumns(ColumnName)' + Char(13) +          'Select Column_Name' + Char(13) +         'From   ' + @DatabaseName + '.Information_Schema.Columns (NOLOCK)' + Char(13) +         'Where  Table_Name = ''' + @TableName + '''' + Char(13) +         '       and Data_Type = ''int'''    -- print @SQL   exec sp_executeSql @SQL   Set @SQL =          'Insert Into @CS(MyChecksum)' + Char(13) +          'Select '   Set @i = 1   While Exists(        Select 1        From   @IntColumns        Where  IdRecord = @i)  begin        Select @ColumnName = ColumnName        From   @IntColumns        Where  IdRecord = @i         Set @SQL = @SQL + Char(13) +              CASE WHEN @i = 1 THEN                   '    Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))'                  ELSE                  '    + Sum(Cast(IsNull(' + @ColumnName + ',0) as bigint))'             END         Set @i = @i + 1  end   Set @SQL = @SQL + Char(13) +        'From ' + @DatabaseName + '..' + @TableName + ' (NOLOCK)'   -- print @SQL   exec sp_executeSql @SQL   Set @Checksum = (Select Top 1 MyChecksum From @CS)   Return isnull(@Checksum,0)  END GO
11352
Duplicate:     Maybe this is a very silly question but I don't understand why I can't declare two methods that have the same signature when they have different return types.  public class MyClass {     private double d = 0;      public double MyMethod()     {         return d;     }      public string MyMethod()     {         return d.ToString();     } }   I get a compile error that states that the class already defines a member with the same parameter types.  (Obviously the way I'm using this in my code isn't as simple as my example code...but I think it gets the idea across.)  Am I missing something concerning OO design that makes what I'm trying to do an OOP anti-pattern? Surely the compiler should be able to determine which method I'm trying to use as long as I specifically tell it which one I want.  Given MyClass myClass = new MyClass(); I would expect the following code to work:  double d = myClass.MyMethod(); string s = myClass.MyMethod();   I would expect the following code to have problems:  var v = myClass.MyMethod();   But even in the case of var it should result in a compile error.  Can anyone see what I'm doing wrong here? I'm more than happy to be corrected. :-)
48923
When using JDBC, I often come across constructs like    ResultSet rs = ps.executeQuery(); while (rs.next()) {     int id = rs.getInt(1);     // Some other actions }   I asked myself (and authors of code too) why not to use labels for retrieving column values:    int id = rs.getInt("CUSTOMER_ID");   The best explanation I've heard is something concerning performance. But actually, does it make processing extremely fast? I don't believe so, though I have never performed measurements. Even if retrieving by label would be a bit slower, nevertheless, it provide better readability and flexibility, in my opinion. So could someone give me good explanation of avoiding to retrieve column values by column index instead of column label? What are pros and cons of both approaches (maybe, concerning certain DBMS)?
34548
I'm trying to make a class that will execute any one of a number of stored procedures with any amount of variables  Im using php and mysqli   My class enumerates an array and constructs a string based on the number of elements if any giving something like this CALL spTestLogin(?,?) for example I now need to bind the input from my array using somethin like this:  $stmt->bind_param($this->paramTypes,$this->paramValues);//paramValues is my array   Then if that works I can work on getting my results  Any ideas
79610
an example code what I try to do will surely do better than my english:  public bool IsNumericValueInBounds (string value, Type numericType) {   double d = double.NaN;         bool inBounds = (bool)numericType.GetMethod ("TryParse").Invoke (null, new object[] { value, d });    return inBounds; }   Unfortunately the TryParse method needs an out parameter so this doesn't work. any ideas how to solve this?  (ps.: would'nt this be a nice example for duck typing? - because i know every numericType has an "TryParse" or I am mistaken?)
66977
How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT?  For example how to do a SELECT like:  SELECT foo, bar FROM foobar;   Hint :  BEGIN SELECT foo, bar FROM foobar; END;   doesn't work.
110185
I have recently inherited a codebase with a large amount of Stored Procedures.  The system they are supporting is encountering numerous performance problems which I am looking in to.  A number of the Stored Procedures have a pattern like this:   Create Temp table Build up dynamic SQL query to insert a bunch of records, e.g.  DECLARE @sql VARCHAR(MAX) SET @sql = 'INSERT INTO @tempTable SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'  IF @someParam = [SomeValue]     SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';  IF @someOtherParam = [SomeOtherValue]     SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';  Execute this dynamic sql  EXEC(@sql);  Select from the temp table and bring in a bunch of additional information to return to the client.  SELECT     ...  FROM     @tempTable    INNER JOIN ...    My immediate thoughts are:   There is dynamic SQL, so no cache plans, meaning plans generated every time. There is an INSERT SELECT pattern, so table locking is more likely to be an issue.   I have re-written some of the Stored Procedures in this way instead:      SELECT          ...      FROM          MyTable          INNER JOIN ...      WHERE      (         @someParam != SomeValue         OR         SomeColumn = SomeValue     )     AND     (         @someOtherParam != SomeOtherValue         OR         SomeOtherColumn = SomeOtherValue     )           From comparing execution plans and client statistics in SQL Management Studio, I have not sped the Stored Procedures up so I am apprehensive about suggesting wholesale re-writes of all Stored Procedures.  I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.  Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?  The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used.  My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.
115908
Suppose the following function:  CREATE FUNCTION [dbo].[ufnTest]() RETURNS TABLE AS RETURN SELECT 1 AS Nr   My actual function will select actual data from many different tables. I (mostly) understand the risks of using nolock hints, and decided that in this case I actually want them. So I try to call above function like this:  SELECT * FROM [dbo].[ufnTest]() WITH(NOLOCK)   However,  with the following message:     Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.   The tips for fixing this error are not relevant. Perhaps this table hint isn't available for table valued functions then?  There are several alternatives I'd consider. One is to use the hint on all tables in my select query, but I'd prefer not to as (a) I'll have to remember this whenever I change the function around and (b) it's repetitive. Another alternative would be to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, but a downside is that I don't quite know how to set it back to wat it was before (and statements after the select from my TVF should have the original isolation level).  So, I'd prefer to make WITH (NOLOCK) for a select from a table valued function work. Is this possible?
97172
Hi i am trying to write script for display list of records from wrapper class.   @remoteaction     public static wrappercls getaccounts(){         string account = 'select id,name,accountnumber from account';         listacc = database.query(account);         count = listacc.size();         system.debug('#####'+count);         wrappercls wra = new wrappercls(count,listacc);         return wra;     }      public class wrappercls{         public integer count11;         public list&lt;account&gt; account;          public wrappercls(integer count11,list&lt;account&gt; account){             this.count11 = count11;             this.account = account;         }     }    This class returns list of account records and record count parameter.Need to display these records in page.Is it possible if possible Please help me on this..  Script  &lt;script&gt;   function getallaccounts(){     exremote.getaccounts(         function(result,event){             if(event.status){              }             else {              }        },{escape:true}     ); } &lt;/script&gt;   Thanks in Advance...
68495
I am getting this error:     SObject row was retrieved via SOQL without querying the requested   field: Contact.Account   Controller:  public class WrapperAccountCtrl {      public List&lt;Account&gt; acclist{get;set;}     public List&lt;Contact&gt; conlist{get;set;}      public List&lt;MyWrapper&gt; wap{get;set;}      public WrapperAccountCtrl()      {         acclist=[Select id,name from Account];          conlist=[Select id,name,AccountID  from contact where AccountID IN:acclist];          system.debug('Size----'+conlist.size());           wap=new List&lt;MyWrapper&gt;();           for(integer i=0;i&lt;acclist.size();i++)          {              wap.add(new MyWrapper(conlist,acclist[i]));          }     }      public class MyWrapper     {          public Account accn{get;set;}          public List&lt;Contact&gt;conrec{get;set;}          public MyWrapper(List&lt;Contact&gt; con,Account acc)         {             accn=acc;             conrec=con;             }     } }   VF Page:  &lt;apex:page controller="WrapperAccountCtrl" sidebar="false"&gt;       &lt;apex:form &gt;          &lt;apex:pageBlock &gt;              &lt;apex:pageBlockTable value="{!conlist}" var="c"&gt;                  &lt;apex:column value="{!c.account.Name}"/&gt;              &lt;/apex:pageBlockTable&gt;          &lt;/apex:pageBlock&gt;      &lt;/apex:form&gt;  &lt;/apex:page&gt;